MYSQLI and bind result

[Drongo_III]

Hi Guys!

 

Quick question on mysqli and bind_result (been using pdo a lot and mysqli seems a wee bit different which is throwing me).

 

When you bind the results of a query, what is the best way to get a multidimensional array from the result?

 

I have played about and got the below to work - but i just know there has to be a better way than that!

 

I woudl appreciate some enlightenment if you please

 

<?php

$db = new mysqli("localhost", "root", "", "testingcsv");

// check errors
if(mysqli_connect_errno()){

	echo mysqli_connect_error();
	exit();

}


$var = 34;

$query = $db->prepare("SELECT name, address, town FROM datastuff WHERE id=?");
$query->bind_param('i', $var);

$query->execute();

$query->bind_result($name, $address, $town);

$myarray = array(); // to store results
$i = 0; 

while($query->fetch()){

	$myarray[$i][] = $name;
	$myarray[$i][]= $address;
	$myarray[$i][] = $town;
	$i++;
}
echo "<pre>";
print_r($myarray);

[Christian F.]

As the PHP manual states:

mysqli_stmt::fetch -- mysqli_stmt_fetch ? Fetch results from a prepared statement into the bound variables

In other words, you're not fetching an array, but using a function very similar to extract (). If you want an array, don't use mysqli::bind_result (), but mysqli::fetch_array ().

[PFMaBiSmAd]

Since you are using a prepared statement, in general, you would need to do it the way you came up with. I would form an array with keys/values and do this all in one simple statement - $myarray[] = array('name'=>$name,'address'=>$address,'town'=>$town);.

 

If you have php5.3+ and are using the mysqlnd driver, you can convert the result from a prepared statement into a mysqli result set using - http://php.net/manual/en/mysqli-stmt.get-result.php This will allow you to use traditional mysqli fetch statements to iterate over the result set.

 

 

[Drongo_III]

Hi Guys

 

Thanks for your replies to this post. Just have a couple of questions (humour me as i am still getting to grips with prepared mysqli statements).

 

1) Generally speaking in what scenario would you want to use bind_result? I assumed from the examples that it was simply what was done - from following examples.

 

2) Christian suggested above that instead of using bind result I should use mysqli_stmt::fetch. But in the context of a prepared statement, like the example below, how would I use that method?

 

Example:

        $var = 34;	
$query = $db->prepare("SELECT name, address, town FROM datastuff WHERE id=?");
$query->bind_param('i', $var);
        $query->execute();

 

Where would I go from here to use mysqli_stmt_fetch? My confusion is really around the fact that with a prepared statement you're not using somehting like:

 

$result = $mysqli->query($query);

 

 

Sorry if these questions seem a little vacuous...just trying to get to grips with it.

 

Drongo

 

 

[PFMaBiSmAd]

bind_result and  mysqli_stmt::fetch are used with prepared statements.

 

He suggested using mysqli::fetch_array (no stmt in it), which isn't used with prepared statements. It's used with non-prepared statements that are executed via $mysqli->query().

[Christian F.]

Actually, you can use mysql_result::fetch_array () and Prepared Statements:

http://www.php.net/manual/en/mysqli-stmt.get-result.php

[PFMaBiSmAd]

Someone already stated that, along with the restrictions under which it could be used.

[Drongo_III]

Ok getting to grips with it a bit more now.

 

Ordinarily, when would you suggest using bind_result? Or is it how prepared select statements should be done? Sort of as a matter of course.

 

 

[Christian F.]

Ah, would you look at that. What a smart individual too!

 

OK, time to re-read threads before answering them, so I don't make any more mistakes like this. Forgot all about that post while searching for an answer I knew I had read somewhere.

[Drongo_III]

Any suggestions on the below question?

 

Ok getting to grips with it a bit more now.

 

Ordinarily, when would you suggest using bind_result? Or is it how prepared select statements should be done? Sort of as a matter of course.

[PFMaBiSmAd]

Ordinarily, when would you suggest using bind_result?

 

When you have a prepared query statement that returns a result set.

[Drongo_III]

Thank you!