How to fetch all in assoc array from a prepared statement?

Question

I'm trying this code:

    if ($result = $this->mysqli->prepare("SELECT * FROM `mytable` WHERE `rows1`=?"))
    {

        $result->bind_param("i",$id);
        $result->execute();
        while ($data = $result->fetch_assoc())
        {

            $statistic[] = $data;

        }

        echo "<pre>";
        var_dump($statistic);
        echo "</pre>";
    }

but it's throwing the following error

[Fri Jun 15 12:13:11 2012] [error] [client 127.0.0.1] PHP Fatal error: Call to undefined method mysqli_stmt::fetch_assoc() in [myfile.php]

And also I've tried:

if ($result = $this->mysqli->prepare("SELECT * FROM `mytable` WHERE `rows1`=?"))
    {

        $result->bind_param("i",$id);
        $rows = $result->execute();
        while ($data = $rows->fetch_assoc())
        {

            $statistic[] = $data;

        }

        echo "<pre>";
        var_dump($statistic);
        echo "</pre>";
    }

that makes this:

[Fri Jun 15 12:22:59 2012] [error] [client 127.0.0.1] PHP Fatal error: Call to a member function fetch_assoc() on a non-object in [myfile.php]

What else I can do for getting result or what I doing wrong? I need the assoc array from DB looking like $data[0]["id"] = 1

Answer 1

In fact you can do this quite easily, you just can't do it with the mysqli_stmt object, you have to extract the underlying mysqli_result, you can do this by simply calling mysqli_stmt::get_result(). Note: this requires the mysqlnd (MySQL Native Driver) extension which may not always be available.

However, the point below about recommending PDO over MySQLi still stands, and this is a prime example of why: the MySQLi userland API makes no sense. It has taken me several years of intermittently working with MySQLi for me to discover the mechanism outlined above. Now, I'll admit that separating the statement and result-set concepts does make sense, but in that case why does a statement have a fetch() method? Food for thought (if you're still sitting on the fence between MySQLi and PDO).

For completeness, here's a code sample based (loosely) on the original code in the question:

// Create a statement
$query = "
    SELECT *
    FROM `mytable`
    WHERE `rows1` = ?
";
$stmt = $this->mysqli->prepare($query);

// Bind params and execute
$stmt->bind_param("i", $id);

// Extract result set and loop rows
$result = $stmt->get_result();
while ($data = $result->fetch_assoc())
{
    $statistic[] = $data;
}

// Proof that it's working
echo "<pre>";
var_dump($statistic);
echo "</pre>";

Answer 2

You can do:

$stmt = $this->mysqli->prepare("SELECT * FROM `mytable` WHERE `rows1`=?");
$stmt->bind_param("i",$id);
$stmt->execute();
$result = $stmt->get_result();
$statistic = $result->fetch_all(MYSQLI_ASSOC);

$statistic contains all the result in a 2-dimensional array.

*It should be noted that this mysqli_fetch_all() function only works with the mysqlnd package. http://php.net/manual/en/mysqli-result.fetch-all.php

Answer 3

I don't like Mysqli, but you can do it like this on prepare.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli('hostAddress', 'username', 'password', 'databaseName');
$db->set_charset('utf8mb4');

$userID = 2;

$stmt = $db->prepare("SELECT * FROM users WHERE ID = ?");
$stmt->bind_param("i", $userID);

// because the variable is bound by reference you can assign the value after binding, too
//$userID = 2;

$stmt->execute();

if you want result;

$result = $stmt->get_result();
$user   = $result->fetch_array(MYSQLI_ASSOC); //one row

or multiple row

$users  = $result->fetch_all(MYSQLI_ASSOC);

Answer 4

With a prepared statement you don't need an array but you are free to use it. Use the bind_result method of mysqli_stmt and you won't have any trouble. In bind_result you defined the variables where the values of the columns should be stored in the same order as you request them from the database. You can use native variables or array keys to do so. see php docs mysqli_stmt::bind_result

You ask what's wrong with your code it is the misuse of $result->execute. $result->execute() returns a boolean and not an instance mysqli_result as expected by you. see php docs mysqli_stmt::execute

Let's assume your database table `mytable` has the following columns: id (int), field1 (varchar), field2 (date)

Then the code would look like this:

// Initialize the array with some default values if you really want to use an array
$data = array('id'=>0,'field1'=>'','field2'=>'');
/* I recommend to explicitly define which columns you want to retrieve so you don't have to rely that the columns in the database are set in the desired order and on otherhand it doesn't matter if the database table has more columns that you want to retrieve, the code will still work as long as the columns with this names exist.*/
if ($result = $this->mysqli->prepare("SELECT `id`,`field1`,`field2` FROM `mytable` WHERE `rows1`=?"))
    {

        $result->bind_param("i",$id);
        $result->bind_result("iss", $data['id'], $data['field1'], $data['field2']);
        $result->execute();
        while($result->fetch())
        {

            $statistic[] = $data;

        }

        echo "<pre>";
        var_dump($statistic);
        echo "</pre>";
    }