How to use bind_result () instead of get_result () in php

Question

I am working on a project for uni and used the following code on a testing server to get all devices from a table based on user_id :

 public function getAllDevices($user_id) { $stmt = $this->conn->prepare("SELECT * FROM devices WHERE primary_owner_id = ?"); $stmt->bind_param("i", $user_id); $stmt->execute(); $devices = $stmt->get_result(); $stmt->close(); return $devices; } 

This works fine on my test server, but returns this error when switching to a university project server:

 Call to undefined method mysqli_stmt::get_result() 

Some searches suggest using bind_result() instead of get_result() , but I don’t know how to do all fields in the table. Most examples only show one field return.

Any help would be greatly appreciated

Answer 1

Assuming you cannot use get_result() and you need an array of devices, you can do:

 public function getAllDevices($user_id) { $stmt = $this->conn->prepare("SELECT device_id, device_name, device_info FROM devices WHERE primary_owner_id = ?"); $stmt->bind_param("i", $user_id); $stmt->execute(); $stmt->bind_result($id, $name, $info); $devices = array(); while($stmt->fetch()) { $tmp = array(); $tmp["id"] = $id; $tmp["name"] = $name; $tmp["info"] = $info; array_push($devices, $tmp); } $stmt->close(); return $devices; } 

Creates a temporary array and stores data from each row in it, and then pushes it to the main array. As far as I know, you cannot use SELECT * in bind_result() . Instead, you will be annoyed to enter all the fields you want after SELECT

Answer 2

Currently, you probably understood the idea of ​​binding to several variables. However, do not believe the warnings about using "SELECT *" with bind_result (). You can store your "SELECT *" instructions ... even on your server, requiring you to use bind_result (), but it's a bit complicated because you have to use PHP call_user_func_array () as a way to pass arbitrary (due to "SELECT" * ") the number of bind_result () parameters. Others in front of me provided a convenient function for this in other places on these forums. I include it here:

 // Take a statement and bind its fields to an assoc array in PHP with the same fieldnames function stmt_bind_assoc (&$stmt, &$bound_assoc) { $metadata = $stmt->result_metadata(); $fields = array(); $bound_assoc = array(); $fields[] = $stmt; while($field = $metadata->fetch_field()) { $fields[] = &$bound_assoc[$field->name]; } call_user_func_array("mysqli_stmt_bind_result", $fields); } 

Now, to use this, we do something like:

 function fetch_my_data() { $stmt = $conn->prepare("SELECT * FROM my_data_table"); $stmt->execute(); $result = array(); stmt_bind_assoc($stmt, $row); while ($stmt->fetch()) { $result[] = array_copy($row); } return $result; } 

Now fetch_my_data () will return an array of associative arrays ... everything is configured for JSON encoding or something else.

It is insidious what is happening here. stmt_bind_assoc () creates an empty associative array by the link you pass to it ($ bound_assoc). It uses result_metadata () and fetch_field () to get a list of returned fields and (with this only statement in the while loop) creates an element in $bound_assoc with that field name and adds a reference to it in the $ fields array. Then the $ fields array is passed to mysqli_stmt_bind_result. The really smooth part is that no actual values ​​were passed to $ bound_assoc. All fetching from the request occurs in fetch_my_data (), as you can see from the fact that stmt_bind_assoc() is called before while($stmt->fetch()) .

However, there is one catch: since the operator is bound to links in $ bound_assoc, they will change with every $stmt->fetch() . So, you need to make a deep copy of the string $. If you do not, all the rows in the $ result array will contain the same thing: the last row returned in your SELECT. So, I use the small array_copy () function that I found on Google:

 function array_copy( array $array ) { $result = array(); foreach( $array as $key => $val ) { if( is_array( $val ) ) { $result[$key] = arrayCopy( $val ); } elseif ( is_object( $val ) ) { $result[$key] = clone $val; } else { $result[$key] = $val; } } return $result; } 

Answer 3

to use bind_result() you cannot use SELECT * queries.

Instead, you should select individual column names and then chain the results in the same order. here is an example:

 $stmt = $mysqli->prepare("SELECT foo, bar, what, why FROM table_name WHERE id = ?"); $stmt->bind_param("i", $id); if($stmt->execute()) { $stmt->bind_result($foo, $bar, $what, $why); if($stmt->fetch()) { $stmt->close(); }else{ //error binding result(no rows??) } }else{ //error with query } 

Answer 4

Your question assumes that you have MySQL Native driver (MySQLnd) installed on your local server, but MySQLnd is not on the school project server, since get_result() requires MySQLnd.

Therefore, if you still want to use get_result() instead of bind_result() on the school project server, then you must install MySQLnd on the school project server.

Answer 5

get_result() now only available in PHP by installing its own MySQL driver (mysqlnd). In some environments, it may not be possible or desirable to install mysqlnd.

Despite this, you can still use mysqli to execute "select *" queries and get the results with field names, although this is a bit more complicated than using get_result() , and includes using the php function call_user_func_array() . See the example below, which executes a simple "select *" query and outputs the results (with column names) to an HTML table:

 $maxaccountid=100; $sql="select * from accounts where account_id<?"; $stmt = $mysqli->prepare($sql); $stmt->bind_param('i', $maxaccountid); $stmt->execute(); print "<table border=1>"; print "<thead><tr>"; $i=0; $meta = $stmt->result_metadata(); $query_data=array(); while ($field = $meta->fetch_field()) { print "<th>" . $field->name . "</th>"; $var = $i; $$var = null; $query_data[$var] = &$$var; $i++; } print "</tr></thead>"; $r=0; call_user_func_array(array($stmt,'bind_result'), $query_data); while ($stmt->fetch()) { print "<tr>"; for ($i=0; $i<count($query_data); $i++) { print "<td>" . $query_data[$i] . "</td>"; } print "</tr>"; $r++; } print "</table>"; $stmt->close(); print $r . " Records<BR>";