Mysqli Prepared Statements Return Array - How To?

[mds1256]

Hi

 

Sorry for all the questions but just trying to get my head around using prepared statements instead of just normal connections and queries.

 

I have the below code and what I want to do is put it into a function and return the array of results.

 

To then call the function and store the array in a variable and the iterate over the variable to get the values.

 

$category = $_POST['category'];

$sql = "select id, name from items where category = ?";
$stmt = $Con->prepare($sql);
$stmt->bind_param('s', $category);
$stmt->execute();

$stmt->bind_result($id, $name);
while($row = $stmt->fetch())            ---------- this is the bit I would like to return from the function
{
       echo $id."<br />";
       echo $name."<br />";
}
$stmt->close();

[Jessica]

What have you done so far? IE, where are you stuck?

[Christian F.]

If you want/need to save the result from the query as an array, without setting one up manually with the bind () method, then you have to use the MySQLInd driver. Then you'll get access to the mysqli::fetch_all () method.

You can also use PDO, as it supports fetch the rows as arrays by default.

[mds1256]

What have you done so far? IE, where are you stuck?

 

The above, not sure how to return the array and then call the function and iterate over the results.

[mds1256]

If you want/need to save the result from the query as an array, without setting one up manually with the bind () method, then you have to use the MySQLInd driver. Then you'll get access to the mysqli::fetch_all () method.

You can also use PDO, as it supports fetch the rows as arrays by default.

 

No access to Mysqld driver unfortunately, shared hosting

[Jessica]

function myfunc(){
// do code
return $arr;
}

myfunc();

 

If you want help, post your function and loop. Otherwise you're asking someone to do it for you.

[mds1256]

basically I want to achieve the following in pseudo code:

 

function contains the prepared statement and returns the array.

 

You call the function and store the returned array in a variable.

 

You then use the while loop / foreach loop to iterate over the results allowing the return of each field as a row variable.

 

Just like you can do using normal mysql where you return the mysql_query() and then use this to iterate over using mysql_fetch_assoc().

[mds1256]

function myfunc(){
// do code
return $arr;
}

myfunc();

 

If you want help, post your function and loop. Otherwise you're asking someone to do it for you.

 

The function doesnt work, but here is my code:

 

function test()
{


$res = array();


$Con = new mysqli('localhost', 'usr', 'pass', 'db');

$category = $_POST['category'];
$sql = "select id, name from items where category = ?";
$stmt = $Con->prepare($sql);
$stmt->bind_param('i', $category);
$stmt->execute();

$stmt->bind_result($id, $name);

$res = $stmt->fetch()

return $res;


}


$my = test();


foreach($my as $test)    
{
  echo $my['id'];


}

[Jessica]

Was that so hard??

[Christian F.]

http://no2.php.net/manual/en/mysqli-stmt.fetch.php <- I recommend reading this page, and pay attention to the details. fetch () isn't doing what you think it does.

[Jessica]

You have some errors in there that you should be getting errors reported on. How does it not work? Fix the errors like the missing ; at the end of line, etc.

 

Seriously, read the links in my signature, before replying.

[mds1256]

Ok, I think I may have sorted it.

 

See the below code, is there a more efficient way of doing this? does filling up a new array up and returning it slow things down as well as use memory unnecessarily?

 

<?php


function test()
{


$res = array();
$Con = new mysqli('localhost', 'usr', 'pass', 'db');
$limit = 10000;
$sql = "select id, name, email from tbl_test limit ?";
$stmt = $Con->prepare($sql);
$stmt->bind_param('i', $limit);
$stmt->execute();
$stmt->bind_result($id, $name, $email);
$i = 0;
while($stmt->fetch())
{
 $res[$i]["name"] = $name;
 $res[$i]["email"] = $email;
 $i++;
}

$stmt->close();


return $res;


}


$my = test();


foreach($my as $item)
{
echo $item['name'].$item['email']."<br />";
}



?>

[Christian F.]

Doesn't really affect the performance or memory too much, especially the latter. Performance can take a slight hit, if you do this a lot, or with a lot of data.

 

You can also do away with the $i variable, and just define an array directly:

while ($stmt->fetch ()) {
  $res[] = array ('name' => $name, 'email' => $email);
}

 

Though, generally you should probably avoid these kinds of constructs, if you can. Most of the time it's better to just use the results directly, instead of adding an (essentially) unnecessary loop and array.

[mds1256]

Thanks for the above that helps make it easier

 

I have had a problem returning the result so this is the only way I can think of is to iterate over the result and add it to an array and then return that array unless you can think of a different way.

 

The fetch_results() only works with the mysqld driver which I cannot use unfortunately.

 

Thanks

[Christian F.]

You're welcome.

 

PS: It's MySQLInd, which stands for "MySQL Improved native driver".

Edited December 25, 2012 by Christian F.