php - 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?

标签： phpmysqli

Answer 1

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