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

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.