why does only first mysqli_fetch() result gets stored in array

Question

I'm new to mysqli prepared statements. I'm trying to store the results in an associative array so that I can use it further. The results get printed properly before appending to the array but when appended only the first entry gets added. what is the mistake in the approach here?

// order_details_table
$order_details = array();
$invoice = 1234;
$stmt = $con->prepare("SELECT `description`,`amount` FROM order_details_table WHERE invoice_no = ?");
$stmt->bind_param("s", $invoice);
$stmt->execute();
$stmt->bind_result($description,$amount);
while($stmt->fetch())
{
    print_r($description." ".$amount); //This prints all the entries for the given query
    $row['desc'] = $description;
    $row['amount'] = $amount;
    $order_details += [$row]; //This appends just the first entry
}
print_r($order_details);

@mitkosoft he is not overwriting. he is just misusing the array addition operator — Your Common Sense, Feb 21 '20 at 14:04
At your comment wouldnt be good to use `$order_details[array_shift($row)] = $row;` in while loop @mitkosoft — , Feb 21 '20 at 14:12

score 1 · Accepted Answer · answered Feb 21 '20 at 14:34

You are using array union operator. From PHP docs:

The + operator returns the right-hand array appended to the left-hand array; for keys that exist in both arrays, the elements from the left-hand array will be used, and the matching elements from the right-hand array will be ignored.

Your temporary array has the same key as the array you are collecting into. Both have elements with key 0, so the new row is not added.

To fix it you should use array push operator i.e. [].

while($stmt->fetch())
{
    print_r($description." ".$amount); //This prints all the entries for the given query
    $row['desc'] = $description;
    $row['amount'] = $amount;
    $order_details[] = $row; // Append the new row into the array
}

However, I do not recommend such manual approach. mysqli has methods for fetching all the rows at once. You should use fetch_all() instead.

// order_details_table
$invoice = 1234;
$stmt = $con->prepare("SELECT `description`,`amount` FROM order_details_table WHERE invoice_no = ?");
$stmt->bind_param("s", $invoice);
$stmt->execute();
$result = $stmt->get_result(); // Get the result instead of binding
$order_details = $result->fetch_all(MYSQLI_ASSOC);

If you really want to loop on the results one by one and build an array manually then use foreach loop on the mysqli_result object.

// order_details_table
$invoice = 1234;
$stmt = $con->prepare("SELECT `description`,`amount` FROM order_details_table WHERE invoice_no = ?");
$stmt->bind_param("s", $invoice);
$stmt->execute();
$result = $stmt->get_result(); // Get the result instead of binding
$order_details = []; // Instanciate empty array
foreach($result as $row)
{
    $newrow['desc'] = $row['description'];
    $newrow['amnt'] = $row['amount'];
    $order_details[] = $newrow; //This appends just the first entry
}
print_r($order_details);

You sould add a simple pdo example with multidimentional array in answer for me :) Joke! Why foreach instead of while ? — , Feb 21 '20 at 14:53
@Dlk Let me you ask in reverse: Why `while` instead of `foreach`? Isn't foreach approach simpler? — Dharman, Feb 21 '20 at 15:01
Its simple ! I wanted to know if there is something differend between using while and foreach in this case, not in generaly :) — , Feb 21 '20 at 15:40
@Dlk There is. I was going to write another post today. If I do I will link it here. If not you can search my older answers, I explained it somewhere already. — Dharman, Feb 21 '20 at 15:41
@Dlk Maybe this will help: https://stackoverflow.com/a/60343398/1839439 — Dharman, Feb 21 '20 at 17:14

why does only first mysqli_fetch() result gets stored in array

1 Answers1