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

Question

I'm working on a project for uni and have been using the following code on a testing server to get all devices from a table based on a 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 worked fine on my testing server but returns this error when migrating over to the university project server:

Call to undefined method mysqli_stmt::get_result()

Some googling suggests using bind_result() instead of get_result() but I have no idea how to do this all fields in the table. Most examples only show returning one field

Any help would be much appreciated

Answer 1

Assuming you can't use get_result() and you want an array of devices, you could 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;
}

This creates a temporary array and stores the data from each row in it, and then pushes it to the main array. As far as I'm aware, you can't use SELECT * in bind_result(). Instead, you will annoyingly have to type out all the fields you want after SELECT

Answer 2

By now, you've certainly grasped the idea of binding to multiple variables. However, do not believe the admonitions about not using "SELECT *" with bind_result(). You can keep your "SELECT *" statements... even on your server requiring you to use bind_result(), but it's a little complicated because you have to use PHP's call_user_func_array() as a way to pass an arbitrary (because of "SELECT *") number of parameters to bind_result(). Others before me have posted a handy function for doing this elsewhere in 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... all set to encode to JSON or whatever.

It's kinda crafty what is going on, here. stmt_bind_assoc() constructs an empty associative array at the reference you pass to it ($bound_assoc). It uses result_metadata() and fetch_field() to get a list of the returned fields and (with that single statement in the while loop) creates an element in $bound_assoc with that fieldname and appends a reference to it in the $fields array. The $fields array is then passed to mysqli_stmt_bind_result. The really slick part is that no actual values have been passed into $bound_assoc, yet. All of the fetching of the data from the query happens in fetch_my_data(), as you can see from the fact that stmt_bind_assoc() is called before the while($stmt->fetch()).

There is one catch, however: Because the statement has bound to the references in $bound_assoc, they are going to change with every $stmt->fetch(). So, you need to make a deep copy of $row. If you don't, all of the rows in your $result array are going to contain the same thing: the last row returned in your SELECT. So, I'm using a little array_copy() function that I found with the 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

in order to use bind_result() you can't use queries that SELECT *.

instead, you must select individual column names, then bind the results in the same order. here's 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 suggests that you have MySQL Native driver (MySQLnd) installed on your local server, but MySQLnd is missing on the school project server. Because get_result() requires MySQLnd.

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

Answer 5

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

Notwithstanding, you can still use mysqli to do 'select *' queries, and get the results with the field names - although it is slightly more complicated than using get_result(), and involves using php's call_user_func_array() function. See example below which does a simple 'select *' query, and outputs the results (with the 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>";