Store the database table to array php

Question

Please someone help me. I have problem like this:

I want to get data from database and store it in array. Example:

my product table:

id::name::price::qty

1::Red Shoes::10::2

2::Black Dress::20::3

3::Blue Skirt::30::1

I want get data from that table an store in array like this:

    $items = [
        array(
           'id' => '1',
           'price' => 10,
           'quantity' => 2,
           'name' => 'Red Shoes'
        ),
        array(
           'id'=> '2',
           'price' => 20,
           'quantity' => 3,
           'name' => 'Black Dress'
        ),
        array(
           'id'=> '3',
           'price' => 30,
           'quantity' => 1,
           'name' => 'Blue Skirt'
           )
    ];

I try using this code:

$query = mysql_query("SELECT * FROM product");
$data_item = array();

while ($row = mysql_fetch_array($query)) {

    $data_item['id'] = $row['id'];
    $data_item['price'] = $row['price'];
    $data_item['quantity'] = $row['qty'];
    $data_item['name'] = $row['name'];

}

$items = [$data_item];

print_r($items);

This is the output:

Array ( [0] => Array ( [id] => 3 [price] => 30 [quantity] => 1 [name] => Blue Skirt ) )

With that code I just get the last data and 2 others not store inside array. Why this happen?

How to get output like this:

Array ( [0] => Array ( [id] => 1 [price] => 10 [quantity] => 2 [name] => Red Shoes ) [1] => Array ( [id] => 2 [price] => 20 [quantity] => 3 [name] => Black Dress ) [2] => Array ( [id] => 3 [price] => 30 [quantity] => 1 [name] => Blue Skirt )  )

Can someone help me please?

Kristen Waite · Accepted Answer · 2014-06-25 19:18:33Z

Two problems - you are only grabbing a single array that way. 1) $items needs to be inside the while loop in order to grab each $row and 2) $items needs to be a multidimensional array (i.e. an array of $data_items).

while ($row = mysql_fetch_array($query)) {

    $data_item['id'] = $row['id'];
    $data_item['price'] = $row['price'];
    $data_item['quantity'] = $row['qty'];
    $data_item['name'] = $row['name'];

    $items[] = $data_item;

}

From there, you can iterate through the $items array to see all of your items. If you do a print_r($items), you should see the output you expect.

Also, for the record, you could simplify by using mysql_fetch_assoc() instead of mysql_fetch_array() - using mysql_fetch_assoc() will bring along the key (field) values as well. Then you could simplify the building of $items down to a single line of code (which is commonly done):

while ($row = mysql_fetch_assoc($query)) $items[] = $row;

This should give you the same result as your initial attempt when you print_r($items).

Jason Bell · Accepted Answer · 2014-06-25 19:19:03Z

Your code, as it is written right now, is overwriting portions of that $data_item array for each iteration in the while loop.

while ($row = mysql_fetch_array($query)) {

    $data_item[]['id'] = $row['id'];
    $data_item[]['price'] = $row['price'];
    $data_item[]['quantity'] = $row['qty'];
    $data_item[]['name'] = $row['name'];

}

This should fix your problem and as a side note you may want to look into mysqli or PDO database connection handlers since mysql is on the way out and deprecated with php versions 5.5+ (http://www.php.net//manual/en/migration55.deprecated.php) Additionally, the mysqli result class has a nice fetch_all method to grab all the results from a query to avoid having to iterate over rows.

$sql = "SELECT * FROM product";
$result = mysqli_query($sql);
$data_array = $result->fetch_all(MYSQLI_ASSOC);

Granted with this syntax you are limited to using the database column names as your array keys but it does take significantly less code to accomplish nearly the same thing.

Collectives™ on Stack Overflow

Store the database table to array php

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related