How To Pass Prepared Statement Result To Function?

[crwork]

I'm working on a REST service and just to get the basic communication working, set up a SQL query and returned the $result query object to a calling function like so:

$result = $this->db->query("
 SELECT
 user_id, user_name, filter_prefs
 FROM
 user_prefs
 WHERE
 user_id = ". $user_id );
//Result is Object.
return $result;

 

The $result variable was using in the calling function like this:

 

 

if($result->num_rows){
while ($row = $result->fetch_assoc()) {
.
.
.

 

This all worked.

 

However, I'm now shoring up the code and making the SQL query a prepared statement. I am having trouble sending a query object back to the calling function. When I return the variable, I can't loop through results.

 

$stmt->prepare("SELECT
user_id, user_name, filter_prefs
FROM
user_prefs
WHERE
user_id = ?");

// Bind variable
$stmt->bind_param('i', $user_id);
/* execute query */
$stmt->execute();
/* bind result variables */
$stmt->bind_result($theuser, $thename, $theprefs);
/* store result - need this to get num_rows */
$stmt->store_result();
return $stmt;

 

If I do a var_dump of $stmt, it doesn't look like it contains any info retrieved from the DB. Does anyone know if there is a straight-forward way to pass the prepared statement object to a calling function similar to the working code above? I may be missing something simple here...

[PFMaBiSmAd]

Prepared queries don't directly return a result resource that you can iterate over using mysqli_fetch_xxxxx statements. You normally use mysqli_stmt_bind_result to bind the selected columns to variables and use mysqli_stmt_fetch in your loop to populate the variables with the data from each row.

 

If you have php 5.3 and are using the mysqlnd driver, you can use mysqli_stmt_get_result to convert the result of a prepared query into a traditional result resource that you can use mysqli_fetch_xxxxx statements with.

 

Edit: Your use of mysqli_stmt_store_result only transfers the result set from the sql server into php memory so that things like the num_rows method will work and you can iterate over the result set local to php rather than sending commands and data back and forth between the sql server and php on each fetch statement.

Edited September 28, 2012 by PFMaBiSmAd

[crwork]

Prepared queries don't directly return a result resource that you can iterate over using mysqli_fetch_xxxxx statements. You normally use mysqli_stmt_bind_result to bind the selected columns to variables and use mysqli_stmt_fetch in your loop to populate the variables with the data from each row.

 

If you have php 5.3 and are using the mysqlnd driver, you can use mysqli_stmt_get_result to convert the result of a prepared query into a traditional result resource that you can use mysqli_fetch_xxxxx statements with.

 

Edit: Your use of mysqli_stmt_store_result only transfers the result set from the sql server into php memory so that things like the num_rows method will work and you can iterate over the result set local to php rather than sending commands and data back and forth between the sql server and php on each fetch statement.

 

Great information, thanks for your help. We're on 5.3, but will have to check into the mysqlnd driver. I'll try that and re-post with results. Thanks very much!

[crwork]

Rats, looks like mysqlnd isn't installed so I guess I'll have to create an array to store the values in within the called function, then pass the array back to the caller function. Thanks again...