Return a multidimensional array from MySQL result php

Question

I have a PHP function that extract dat of an invoice from DB.

The invoice may have more then one line (one product).

function getInvoiceLines($id)
{
 $res = mysql_query("select * from invoice_lines where id = $id ");
 while ($row = mysql_fetch_array($res))
 {
 $res_retun['ref']=$row['ref'];
 $res_retun['label']=$row['label'];
 $res_retun['price']=$row['label'];
 $res_retun['qty']=$row['qty'];
 }
return $res_retun ;
}

I found this link Create a Multidimensional Array with PHP and MySQL and I made this code using that concept.

Now, how can I move something like a cursor to the next line and add more lines if there's more in MySQL result??

If it's possible, how can I do to show data in HTML with for ??

Answer 1

A little modification should get what you want, below the [] operator is a shorthand notation to add elements to an array, the problem with your code is that you are overwriting the same keys on each iteration

 // fetch only what you need;
 $res = mysql_query("select ref, label, price, qty from invoice_lines where id = $id ");
 while ($row = mysql_fetch_array($res))
 {
 $res_return[] = $row;
 }
 return $res_return;

Note, I fixed some of your typos (you were using $rw instead of $row in the loop of your original code)

• dvir: You changed his query - explain what you changed and why, and adding a note about SQL injection would be awesome as well
• Stecman: In the question and here, $rw inside the while loop should be $row; It won't work otherwise.
• Ali Ben Messaoud: It copy/paste error! I fixed it and for the query it's more efficient when we select which columns to use :))

Answer 2

If you used PDO with fetchAll() you would be returned with the array your expecting, also be safe from nastys:

<?php //Cut down
$db = new PDO("mysql:host=localhost;dbname=dbName", 'root', 'pass');
$sql = "SELECT * FROM invoice_lines WHERE id = :id ";
$stmt = $db->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();
$res_return = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

Then you just loop through the array like with any other array:

<?php 
foreach($res_return as $row){
echo $row['ref'];
...
...
}
?>

Also id should not have more then 1 row it should be unique IF its your primary key.

Answer 3

If you were using PDO like you should be it would be super easy and you would solve your sql injection issues

$db = new PDO(...);
function getInvoiceLines( $db, $id )
{
 $stmnt = $db->prepare("select ref, label, price, qty from invoice_lines where id=?");
 $stmnt->execute( array( $id ) );
 return $stmnt->fetchAll( PDO::FETCH_ASSOC );
}

Answer 4

Make variable global first Then access it in function. For exp.

$return_arr = array(); // outside function 
$i = 0;
cal_recursive(); // call function first time
function cal_recursive(){
 global $return_arr;
 global $i;
 $return_arr[$i] = // here push value to array variable
 $i++;
 // do code for recursive function 
return $return_arr // after end
}