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

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

Solution 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>";

Solution 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

Solution 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);