問題描述
如何將 MySQLi 準備好的語句的結果放入關聯數組中？ (How can I put the results of a MySQLi prepared statement into an associative array?)
I have a sql query and a mysqli prepared statement:
$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();
}
How can I store the results in an associative array so I can loop it later and get to all the data returned by the sql string?
參考解法
方法 1:
Try the following:
$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();
First you get the query metadata and from that obtain all the fields you've fetched (you could do this manually, but this code works for all queries rather than building by hand). The call_user_func_array() function calls the mysqli_stmt::bind_result() function for you on each of those parameters.
After that it is just a matter of running through each row and creating an associative array for each row and adding that to an array resulting in all the results.
方法 2:
Update: Since PHP 5.3.0 you can get a mysqli_result object that provides a fetch_array method.
$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:
Oddly enough, you can't. There's simply no way to get a mysqli_result object from a mysqli_stmt instance. I've always considered this a major flaw, and would guess that this is one of the major reasons that mysqli never reached any real popularity. These days it's been pretty much superseded by PDO, which does what you want with out effort.
Edit: My answer only means that you can't do it by default. Of course you can implement it yourself, like Chris suggested. Still, I think you should use PDO instead, if it's at all possible.
方法 4:
I came across this discussion in order to find a solution for getting data from MySQLi prepared statements without the mysqlnd. I have been developing a class for handling prepared statements with MySQLi in a handy way. Please, take a look to the code, or simply use it (see an example of usage at the end of the piece of code) to fastly write prepared statements and get its results.
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!
方法 5:
A simple one that actually surprisingly works. I know it's procedural, but still:
$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);
(by zeckdude、Chris、maikel、Emil H、Carl Gentleman、Hazzdood)