我该如何将MySQLi预处理语句的结果放入关联数组中?

10

我有一个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字符串返回的所有数据?

7个回答

28

尝试以下方法:

$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()函数。

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


这不是我的代码,我想不起来我在哪里找到它的。毫无疑问,谷歌会提供答案。 - Chris
很棒的编码。我不知道metaData函数。 - hadi
5
$row$params[] = &$row[$field->name]; 中是从哪里来的? - martisj

9

更新:自PHP 5.3.0起,您可以获得一个mysqli_result对象,该对象提供了fetch_array方法。

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

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

3
请注意,正如文档所述,这仅在您安装了并且MySQL本地驱动程序可用的情况下才有效,而您的安装可能没有此功能。 - PaulJ

1
我阅读了这篇讨论,旨在找到一种不需要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!

1
因为尽管"call_user_method_array"已被弃用,但我仍然能够将其用于我非常需要的目的,所以我点了赞。我将在一分钟内发布可工作的php7脚本。 - JSG

1
一个出乎意料地有效的简单方法。我知道这是过程式的,但仍然有效。
$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);

1

奇怪的是,你不能这样做。从mysqli_stmt实例中根本无法获取mysqli_result对象。我一直认为这是一个重大缺陷,并猜想这可能是mysqli从未真正流行的主要原因之一。如今,PDO已经基本取代了它,而且不费吹灰之力就能实现你想要的功能。

编辑:我的回答只是意味着你不能默认地这样做。当然,你可以像Chris建议的那样自己实现它。但我认为,如果可能的话,你应该使用PDO。


0

如果您无法使用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';

Fetch 的工作方式有些不同

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

    }

希望这对你有所帮助


0

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

他的答案是针对之前版本的PHP的一种方法,因为"call_user_method_array"在PHP 4.1.0中被弃用,并在PHP 7.0.0中被删除。

所以我认为发布一个更新后的答案至少适用于PHP7是相关的,因为我最近发现自己在新主机上转移时没有MYSQLND本地驱动程序来支持MYSQLI扩展。太棒了!...

注意:这里有两个函数。最后一个是必需的。这是我知道所有工作的唯一方法。 (编辑答案不会生成关联数组...已修复)

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

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接