如何从准备好的语句中获取 assoc 数组中的所有内容? [英] How to fetch all in assoc array from a prepared statement?

问题描述

我正在尝试此代码:

 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 "
";var_dump($statistic);echo "</pre>";}
但它抛出以下错误
<块引用>
[Fri Jun 15 12:13:11 2012] [error] [client 127.0.0.1] PHP 致命错误:调用 [myfile.php]
 中未定义的方法 mysqli_stmt::fetch_assoc()
我也试过了:
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 "
";var_dump($statistic);echo "</pre>";}
就是这样:
<块引用>
[Fri Jun 15 12:22:59 2012] [error] [client 127.0.0.1] PHP 致命错误:在非对象上调用成员函数 fetch_assoc()[myfile.php]
我还能做什么来获得结果或我做错了什么?我需要来自 DB 的 assoc 数组，看起来像 $data[0]["id"] = 1
解决方案
事实上，您可以很容易地做到这一点，只是使用 mysqli_stmt 对象，你必须提取底层的mysqli_result，你可以通过简单地调用 mysqli_stmt::get_result().注意:这需要 mysqlnd(MySQL Native Driver)扩展，它可能并不总是可用.
然而，下面关于推荐 PDO 而不是 MySQLi 的观点仍然成立，这是一个很好的例子:MySQLi 用户空间 API 没有意义.我花了几年时间间歇性地使用 MySQLi 才发现上述机制.现在，我承认将语句和结果集概念分开确实有意义，但在那种情况下，为什么语句有一个 fetch() 方法?值得深思(如果您仍然对 MySQLi 和 PDO 犹豫不决的话).
为了完整起见，这里有一个代码示例(大致)基于问题中的原始代码:
//创建语句$查询 = "选择 *从`mytable`哪里`rows1` =?"；$stmt = $this->mysqli->prepare($query);//绑定参数并执行$stmt->bind_param("i", $id);//提取结果集并循环行$result = $stmt->get_result();while ($data = $result->fetch_assoc()){$statistic[] = $data;}//证明它在工作echo "
";var_dump($statistic);echo "</pre>";
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
解决方案
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>";


                        
这篇关于如何从准备好的语句中获取 assoc 数组中的所有内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！