如何将 MySQLi 准备好的语句的结果放入关联数组中？

Question

我有一个 sql 查询和一个 mysqli 准备好的语句：

$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();
}

如何将结果存储在关联数组中，以便稍后循环并获取 sql 字符串返回的所有数据？

Answer 1

尝试以下操作：

$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(); 

首先获取查询元数据，然后从中获取已获取的所有字段（您可以手动执行此操作，但此代码适用于所有查询，而不是手动构建）。 call_user_func_array() 函数调用 mysqli_stmt::bind_result() 为您提供关于每个参数的函数。

之后，只需运行每一行并为每一行创建一个关联数组并将其添加到一个数组中即可得到所有结果。

Answer 2

更新：从 PHP 5.3.0 开始，您可以 获取提供 fetch_array 方法的 mysqli_result 对象。

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

$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_array();

Answer 3

奇怪的是，你不能。 根本没有办法从 mysqli_stmt 实例获取 mysqli_result 对象。 我一直认为这是一个主要缺陷，并且猜测这是 mysqli 从未真正流行的主要原因之一。 如今，它几乎已被 PDO 所取代，PDO 可以毫不费力地完成您想要的事情。

编辑：我的回答仅意味着默认情况下您不能这样做。 当然，你可以自己实现它，就像克里斯建议的那样。 不过，我认为如果可能的话，您应该使用 PDO。

Answer 4

我遇到这个讨论是为了找到一种在没有 mysqlnd 的情况下从 MySQLi 准备好的语句中获取数据的解决方案。 我一直在开发一个类，用于以方便的方式使用 MySQLi 处理准备好的语句。 请查看代码，或者简单地使用它（请参阅代码片段末尾的使用示例）来快速编写准备好的语句并获取其结果。

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 5

一个简单的方法实际上令人惊讶地有效。 我知道这是程序性的，但仍然：

$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 6

如果无法使用 PDO 扩展。 或者您在使用准备好的语句构建数据库类时遇到困难。
如何使用插入更新、删除和插入：

    $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';

获取工作方式有点不同

    $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...

现在对于数据库类

    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;
        }

    }

我希望这对您有所帮助

Answer 7

https://stackoverflow.com/users/5849505/carl-gentleman

他的答案是以前版本的一种方法php 作为“call_user_method_array”在 PHP 4.1.0 中已弃用，并在 PHP 7.0.0 中已删除。

因此，我发现至少针对 PHP7 发布更新的答案是相关的，因为我最近发现自己在我转移到的新主机上没有用于 MYSQLI 扩展的 MYSQLND 本机驱动程序。 耶！...

注意：这里有 2 个函数。 最后一项是必需的。 这是我所知道的唯一可行的方法。
（编辑答案不会产生关联数组...已修复）

    public function arr($query, $data, $format) { // Some parts have been used from others. I don't know who.
                                        $d = array();
                                        $row = array();
            // 1. Connect to the database // This is how I do it
                                        $db = $this->con;
            // 2. Prepare the sentence:
            if( !($stmt = $db->prepare($query)) ) { 
                                        echo "Prepared statement failed: [" . $stmt->errno  . "]: " . $stmt->error . "<br>";
                                        $d[0] = false;// I return an object array so [0] I can check later. It is true or false however I define it.
                                        return $d; 
            }

                                        // cast to array
                                        $data = (array) $data; 
                                        $format = (array) $format; 

                                        //Normalize format
                                        $format = implode('', $format); 
                                        $format = str_replace('%', '', $format);

                                        // Prepend $format onto $values
                                        array_unshift($data, $format);

            // 3. Bind the input parameters: (note "call_user_func_array" is not depriciated)
            if ( !(call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($data) )) ) {
                                        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. Prepare to Bind the results:
                                        $meta = $stmt->result_metadata();
            while( $field = $meta->fetch_field() ) {
                                        $argos[] = &$row[$field->name];
                                        $fld_nms[] = $field->name;
            }

            // 6. Bind the results to the argos array:
                                        call_user_func_array( array( $stmt, 'bind_result'), $argos);

                                        /* // I left some debuging tools that are helpful
                                        echo "<br>argos<br>";
                                        print_r($argos);
                                        echo "<br><br>";

                                        $class_methods = get_class_methods($stmt);

            foreach ($class_methods as $method_name) {
                echo "$method_name<br>";
            }
                                        */

            // 7. Collect the results:
            while ($ftch = $stmt->fetch()) {
                                        $dataItem = array();
                                        /*
                                        echo "<br>ftch<br>";
                                        print_r($ftch);
                                        echo "<br><br>";
                                        */
                foreach($argos as $key => $val) { 

                                        echo "Args: k:" . $key . "; v:" . $val . ";<br>";
                                        $nme = $fld_nms[$key];
                                        $dataItem[$nme] = $val; 
                                        //$dataItem[$key] = $val; 
                } 
                                        $d[] = $dataItem; // I am not interested in returning the multi level array yet but I left it
            }

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

            // 9. Return interesting data properly ordered:
                                        return $d;

    }
    private function ref_values($array) { 
                                        $refs = array();
        foreach ($array as $key => $value) {
                                        $refs[$key] = &$array[$key]; 
        }
                                        return $refs; 
    }