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

Andreas Wong · Accepted Answer · 2012-06-22 01:20:08Z

9

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)

answered Jun 22, 2012 at 1:20

Andreas Wong

60.7k19 gold badges112 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dvir Over a year ago

You changed his query - explain what you changed and why, and adding a note about SQL injection would be awesome as well

Stecman Over a year ago

In the question and here, $rw inside the while loop should be $row; It won't work otherwise.

Ali Ben Messaoud Over a year ago

It copy/paste error! I fixed it and for the query it's more efficient when we select which columns to use :))

Lawrence Cherone · Accepted Answer · 2012-06-22 01:28:49Z

3

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.

answered Jun 22, 2012 at 1:28

Lawrence Cherone

46.7k7 gold badges66 silver badges107 bronze badges

Comments

Galen · Accepted Answer · 2012-06-22 01:31:55Z

2

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 );
}

answered Jun 22, 2012 at 1:31

Galen

30.2k9 gold badges75 silver badges90 bronze badges

Comments

Sandeep Sherpur · Accepted Answer · 2017-07-03 07:57:56Z

1

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
}

edited Jul 3, 2017 at 7:57

answered Jun 26, 2017 at 8:53

Sandeep Sherpur

2,84231 silver badges30 bronze badges

Collectives™ on Stack Overflow

Return a multidimensional array from MySQL result php

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related