fetch_array gets only one row

Question

Howdee, I'm trying to send two MySQL queries, each for a different table, to connect the results afterwards into a single array for further processing. Here's my code:

function list_invoice($invoice_id){
global $conn;
    $query_getinvoice = "SELECT * FROM invoices WHERE id = ".$invoice_id;
    $query_getlines = "SELECT * FROM invoice_lines WHERE invoice_id = ".$invoice_id;
    $result = $conn->query($query_getinvoice);
    $result2 = $conn->query($query_getlines);

    $invdata = $result->fetch_array(MYSQLI_ASSOC);
    $invdata2 = $result2->fetch_array(MYSQLI_ASSOC);
    
    $the_data = array_merge($invdata,$invdata2);
}

My problem with the code above is that in invoice_lines table there are actually three rows with the same invoice_id, but the print_f($the_data) or even print_f($result2) returns only one row. Why's that?

Step one in understanding behaviour of any function is reading [documentation](https://www.php.net/manual/en/mysqli-result.fetch-array): "mysqli_fetch_array — Fetch a result **row**". It gets the first row of the result set. — El_Vanja, Apr 16 '21 at 10:28
Or use [`fetch_all`](https://www.php.net/manual/en/mysqli-result.fetch-all.php). — El_Vanja, Apr 16 '21 at 10:28
Another piece of advice: research `JOIN`s to replace those two separate queries with a single one that gets all the related data you need. — El_Vanja, Apr 16 '21 at 10:50

J0R1AN · Accepted Answer · 2021-04-16T10:39:00.643

This is what fetch_array is supposed to do. It fetches one row from the data.
To go through all rows you would have to do something like this:

while ($invdata = $result->fetch_array(MYSQLI_ASSOC) and $invdata2 = $result2->fetch_array(MYSQLI_ASSOC)) {
   $the_data = array_merge($invdata,$invdata2);
   // Do something with this row of data
}

It's also important to note that the way you create the queries right now could lead to SQL Injection if $invoice_id is user input. An attacker could insert a quote ' to close the string and execute malicious SQL commands. To prevent this you need to use prepared statements to ensure that user input doesn't get interpreted as code. Here's a post explaining how to do that with mysqli: How can I prevent SQL injection in PHP?

As @El_Vajna pointed out this will stop looping when any of the two results end. To make it go further even if only one result has data you can change the and inside the if statement to an or. Then only one needs to contain more rows

One flaw in the code you've presented: `$result` and `$result2` might not have the same number of rows and this loop would stop reading as soon as the one with less rows ran out of rows to fetch. — El_Vanja, Apr 16 '21 at 10:30

fetch_array gets only one row

1 Answers1