How can I put the results of a MySQLi prepared sta

Question

I have a sql query and a mysqli prepared statement:

$sql = 'SELECT photographers.photographer_id, photographers.photographer_name
    FROM photographers';

$stmt = $conn->stmt_init(); 
if ($stmt->prepare($sql)) { 
    $stmt->bind_result($photographer_id, $photographer_name);  
    $OK = $stmt->execute(); 
    $stmt->fetch();
}

How can I store the results in an associative array so I can loop it later and get to all the data returned by the sql string?

Answer 1

Oddly enough, you can't. There's simply no way to get a mysqli_result object from a mysqli_stmt instance. I've always considered this a major flaw, and would guess that this is one of the major reasons that mysqli never reached any real popularity. These days it's been pretty much superseded by PDO, which does what you want with out effort.

Edit: My answer only means that you can't do it by default. Of course you can implement it yourself, like Chris suggested. Still, I think you should use PDO instead, if it's at all possible.

Answer 2

A simple one that actually surprisingly works. I know it's procedural, but still:

$query = "SELECT * FROM foo WHERE bar = ?;";

$stmt = mysqli_prepare($dbc, $query);

mysqli_stmt_bind_param($stmt, "s", $bar);

mysqli_stmt_execute($stmt);

$result = mysqli_stmt_get_result($stmt);

return mysqli_fetch_assoc($result);

Answer 3

If you cannot use the PDO extension. Or you are having trouble building your database class with prepared statements. How to use for insert update, delete and insert:

    $db = new database();
    $db->query = "INSERT INTO blabla (name,date,number) VALUES(?,?,?)";
    $db->params = array($name,$date,$number);
    $db->type = 'ssi'; //s=string,i=integer
    if($db->insert())
        echo 'success';

Fetch works a bit different

    $array = array();
    $db = new database();
    $db->query = "SELECT * FROM blabla WHERE id=? and someother=?";
    $db->params = array($id,$other);
    $db->type = 'is';
    $r = $db->fetch(); 
    //$r[0]['id'] for row 1
    //$r[0]['name'] for row 1
    //$r[1] .... For row 2
    //$r[2] .... For row 3
    //etc...

Now for the database class

    class database {

        private $stmt;
        private $mysqli;
        private $query;
        private $params = array();
        private $type;

        public function __set($name, $value) {
            switch ($name) {
                case 'params':
                    $this->params = $value;
                    break;
                case 'query':
                    $this->query = $value;
                    break;
                case 'type':
                    $this->type = $value;
                    break;
                default:
                    break;
            }
        }

        public function __get($name) {
            if ($name !== "mysqli" && $name !== "stmt")
                return $this->$name;
        }

        public function __construct() {
            $this->mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);
            $this->stmt = $this->mysqli->stmt_init();
        }

        private function close_con($bool) {
            if ($bool) {
                $this->stmt->free_result();
            }
            $this->stmt->close();
            $this->mysqli->close();
        }

        private function nofetch() {
            $this->stmt->prepare($this->query);
            $bind_names[] = $this->type;
            for ($i = 0; $i < count($this->params); $i++) {
                $bind_name = 'bind' . $i;
                $$bind_name = $this->params[$i];
                $bind_names[] = &$$bind_name;
            }
            call_user_func_array(array($this->stmt, "bind_param"), $bind_names);

            if ($this->stmt->execute()) {

                $this->close_con(false);
                return true;
            }
            $this->close_con(false);
            return false;
        }

        public function insert() {
            if ($this->nofetch()) {
                return true;
            }
            return false;
        }

        public function update() {
            if ($this->nofetch()) {
                return true;
            }
            return false;
        }

        public function delete() {
            if ($this->nofetch()) {
                return true;
            }
            return false;
        }

        public function fetch() {
            $result_out = array();
            $this->stmt->prepare($this->query);
            $bind_names[] = $this->type;
            if (count($this->params) > 0) {
                for ($i = 0; $i < count($this->params); $i++) {
                    $bind_name = 'bind' . $i;
                    $$bind_name = $this->params[$i];
                    $bind_names[] = &$$bind_name;
                }
                call_user_func_array(array($this->stmt, "bind_param"), $bind_names);
            }
            if ($this->stmt->execute()) {
                $result = $this->stmt->result_metadata();
                $cols = $result->fetch_fields();
                foreach ($cols as $col) {

                    $name = str_replace("-", "_", $col->name);

                    $$name = null;
                    if ($name == null)
                        $name = 'name';
                    $bindarray[$name] = &$$name;
                }

                call_user_func_array(array($this->stmt, 'bind_result'), $bindarray);
                $this->stmt->store_result();
                $copy = create_function('$a', 'return $a;');
                while ($this->stmt->fetch()) {
                    $result_out[] = array_map($copy, $bindarray);
                }
            }
            $this->close_con(true);
            return $result_out;
        }

    }

I hope this is helpful

Answer 4

Try the following:

$meta = $statement->result_metadata(); 

while ($field = $meta->fetch_field()) { 
    $params[] = &$row[$field->name]; 
} 

call_user_func_array(array($statement, 'bind_result'), $params);            
while ($statement->fetch()) { 
    foreach($row as $key => $val) { 
        $c[$key] = $val; 
    } 
    $hits[] = $c; 
} 
$statement->close(); 

First you get the query metadata and from that obtain all the fields you've fetched (you could do this manually, but this code works for all queries rather than building by hand). The call_user_func_array() function calls the mysqli_stmt::bind_result() function for you on each of those parameters.

After that it is just a matter of running through each row and creating an associative array for each row and adding that to an array resulting in all the results.

Answer 5

I came across this discussion in order to find a solution for getting data from MySQLi prepared statements without the mysqlnd. I have been developing a class for handling prepared statements with MySQLi in a handy way. Please, take a look to the code, or simply use it (see an example of usage at the end of the piece of code) to fastly write prepared statements and get its results.

class DbUtils {

    private $host;
    private $user;
    private $pass;
    private $database;
    private $connection;

    public function __construct($host, $user, $pass, $database) {

        $this->host = $host;
        $this->user = $user;
        $this->pass = $pass;
        $this->database = $database;
        $this->connection = new mysqli($host, $user, $pass, $database);

    }

    public function query(Array $params) {

        $args = array();

        // 0. Correct the input function parameters
        if (array_key_exists("query", $params)) {
            $args["query"] = $params["query"];
        } else {
            throw new Exception("Parameter not found: 'query'.");
        }
        if (array_key_exists("types", $params)) {
            $args["types"] = $params["types"];
        } else {
            $args["types"] = '';
        }
        if (array_key_exists("input", $params)) {
            $args["input"] = $params["input"];
        } else {
            $args["input"] = array();
        }

        // 1. Check the connection:
        if ($this->connection->connect_errno) {
            echo "Connection to MySQL failed: [" . $this->connection->connect_errno . "]: " . $this->connection->connect_error . "<br/>";
        }

        // 2. Prepare the sentence:
        if (!($stmt = $this->connection->prepare($args["query"]))) {
            echo "Prepared statement failed: [" . $stmt->errno  . "]: " . $stmt->error . "<br/>";
        }

        // 3. Bind the input parameters:
        if ( ( 0 != sizeof( $args["input"] ) ) && !(call_user_method_array("bind_param", $stmt, array_merge(array($args["types"]), $args["input"])))) {
            echo "Binding parameters failed: [" . $stmt->errno . "]: " . $stmt->error . "<br/>";
        }

        // 4. Execute the sentence
        if (!($stmt->execute())) {
            echo "Sentence execution failed: [" . $stmt->errno . "]: " . $stmt->error . "<br/>";
        }

        // 5. Bind the results:
        $data = array();
        $meta = $stmt->result_metadata();
        $row = array();
        while( $field = $meta->fetch_field() ) {
            $argos[] = &$row[$field->name];
        }
        call_user_method_array('bind_result', $stmt, $argos);

        // 6. Collect the results:
        while ($stmt->fetch()) {
            foreach($argos as $key => $val) { 
                $dataItem[$key] = $val; 
            } 
            $data[] = $dataItem;
        }

        // 7. Close the sentence:
        $stmt->close();

        // 8. Return interesting data properly ordered:
        return $data;
    }

}

// 1. Instantiate it:
$dbUtils = new DbUtils(
    "127.0.0.1", 
    "user", 
    "password", 
    "database"
);

// 2. Query prepared statements like this:
$users = $dbUtils->query(array(
    "query" => "SELECT * FROM user WHERE name LIKE ? AND pass LIKE ?;",
    "input" => array('%', '%'),
    "types" => 'ss'
));

// 3. Enjoy securely CRUD Ops!

Answer 6

Update: Since PHP 5.3.0 you can get a mysqli_result object that provides a fetch_array method.

$sql = 'SELECT photographers.photographer_id, photographers.photographer_name
    FROM photographers';
$data = null;

$stmt = $conn->stmt_init(); 
if ($stmt->prepare($sql)) { 
    $stmt->bind_result($photographer_id, $photographer_name);  
    $OK = $stmt->execute();
    $result = $stmt->get_result();
    $data = $result->fetch_array();
}

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