How to fetch all in assoc array from a prepared st

问题:

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

回答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>";

回答2:

You can do:

$conn = mysqli_connect('host','username','password','database')
$query = 'select * from tablename';
$result = $conn->query($query);
$data = mysqli_fetch_all($result,MYSQLI_ASSOC);

$data contains all the result in an assoc 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

回答3:

did not tried it but change your while loop condition with the following and test again

$data = $result->fetch()

thanks

回答4:

The method execute() returns TRUE or FALSE not a result set and as such, the fetch_assoc method cannot be used as it does not exist on booleans. What you need is

mysqli_fetch_assoc($result); //Use in association with query();

If you want, I can send you a sample code. Hope this helps.

回答5:

It is just a sample:

class admin{
  public static function admin($con){
    $users = $con -> query("SELECT * FROM felhasznalok");
    $show = $users-> fetch_all(MYSQLI_ASSOC);
    return $show; 
  }
}

$con = new mysqli("localhost", "root", "", "teszt");
$s = admin::admin($con);
echo "<table > ";
if (is_array($s)) {
    foreach ($s as $key => $value) {
        echo "<tr>";
        foreach ($value as $key2 => $value2) {
            echo "<td><strong>$key2: </strong> $value2</td>";
        }
        echo "</tr>";
    }
    echo "</table>";
}
?>