CODEWITHSUNDEEP
phpmysqlarraysmultidimensional-array
Ali Ben Messaoud
Ali Ben Messaoud

Reputation: 11920

Return a multidimensional array from MySQL result php

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

Upvotes: 3

Views: 10823

Answers (4)

Sandeep Sherpur
Sandeep Sherpur

Reputation: 2802

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
}

Upvotes: 1

Galen
Galen

Reputation: 30170

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

Upvotes: 2

Lawrence Cherone
Lawrence Cherone

Reputation: 46602

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.

Upvotes: 3

Andreas Wong
Andreas Wong

Reputation: 60516

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)

Upvotes: 9

Related Questions