通常我将此代码用于回显页面行。这是工作很好。
$query = "SELECT * FROM table WHERE id = '$id' ";
$result = mysqli_query($db_mysqli, $query);
$row = mysqli_fetch_assoc($result);
$page = $row['page'];
echo $page;
.....
现在我bind_param这段代码用于回显页面行。但是不工作，我该怎么办？
$stmt = $db_mysqli->prepare("SELECT * FROM table WHERE id = ?");
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$page = $row['page'];
echo $page;
问题描述：
方法返回的mysqli_result对象get_result如下所示：
mysqli_result Object
(
[current_field] => 0
[field_count] => 3
[lengths] =>
[num_rows] => 1
[type] => 0
)
如您所见，此对象仅公开有关需要从中引用数据的记录集的某些属性(字段数、行数等)。因此，您不能直接从中引用字段值。
溶液：
为了获得所需的数据，您必须调用mysqli_result类中定义的方法之一(fetch_all、fetch_array、fetch_assoc等)：
//...
$result = $stmt->get_result();
$row = $result->fetch_array(MYSQLI_ASSOC);
$page = $row['page'];
//...
$row表示获取的记录，并且是这样的数组：
Array
(
[id] => 13
[page] => 21
...
)
有关更多详细信息，请阅读mysqli_result类。
关于错误和异常处理：
请注意，在开发过程中，适当的错误和异常处理系统至关重要。本文介绍了以优雅而彻底的方式激活它所需的步骤。
广泛的示例：
为了清楚起见，我准备了一个广泛的示例，其中包含使用mysqli扩展访问数据库所需的所有组件。它呈现了必须从用户列表中获取一条或多条记录的情况 - 保存在名为users的数据库表中。每个用户都由其id、name和age描述。
由您来实现错误/异常处理系统 - 如上述文章所述。
索引.php：
选项 1) 仅获取一条记录：
<?php
require 'connection.php';
// Assign the values used to replace the sql statement markers.
$id = 10;
/*
* The SQL statement to be prepared. Notice the so-called markers,
* e.g. the "?" signs. They will be replaced later with the
* corresponding values when using mysqli_stmt::bind_param.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT
id,
name,
age
FROM users
WHERE id = ?';
/*
* Prepare the SQL statement for execution - ONLY ONCE.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to prepare(). The first
* argument of bind_param() is a string that contains one
* or more characters which specify the types for the
* corresponding bind variables.
*
* @link http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$statement->bind_param('i', $id);
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* @link http://php.net/manual/en/mysqli-stmt.execute.php
*/
$statement->execute();
/*
* Get the result set from the prepared statement.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
* PHP config file (php.ini) and restart web server (I assume Apache) and
* mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* @link http://php.net/manual/en/mysqli-stmt.get-result.php
* @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
/*
* Fetch data and save it into an array:
*
* Array
* (
* [id] => 10
* [name] => Michael
* [age] => 18
* )
*
* @link https://secure.php.net/manual/en/mysqli-result.fetch-array.php
*/
$user = $result->fetch_array(MYSQLI_ASSOC);
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* @link http://php.net/manual/en/mysqli-result.free.php
*/
$result->close();
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* @link http://php.net/manual/en/mysqli-stmt.close.php
*/
$statement->close();
/*
* Close the previously opened database connection.
*
* @link http://php.net/manual/en/mysqli.close.php
*/
$connection->close();
// Reference the values of the fetched data.
echo 'User id is ' . $user['id'] . '<br/>';
echo 'User name is ' . $user['name'] . '<br/>';
echo 'User age is ' . $user['age'] . '<br/>';
选项 2) 获取多条记录：
<?php
require 'connection.php';
$id1 = 10;
$id2 = 11;
$sql = 'SELECT
id,
name,
age
FROM users
WHERE
id = ?
OR id = ?';
$statement = $connection->prepare($sql);
$statement->bind_param('ii', $id1, $id2);
$statement->execute();
$result = $statement->get_result();
/*
* Fetch data and save it into an array:
*
* Array
* (
* [0] => Array
* (
* [id] => 10
* [name] => Michael
* [age] => 18
* )
*
* [1] => Array
* (
* [id] => 11
* [name] => Harry
* [age] => 59
* )
* )
*
* @link http://php.net/manual/en/mysqli-result.fetch-all.php
*/
$users = $result->fetch_all(MYSQLI_ASSOC);
$result->close();
$statement->close();
$connection->close();
// Reference the values of the fetched data.
foreach ($users as $key => $user) {
echo 'User id is ' . $user['id'] . '<br/>';
echo 'User name is ' . $user['name'] . '<br/>';
echo 'User age is ' . $user['age'] . '<br/>';
echo '<hr/>';
}
连接.php：
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');
/*
* Error reporting.
*
* Also, define an error handler, an exception handler and, eventually,
* a shutdown handler function to handle the raised errors and exceptions.
*
* @link https://phpdelusions.net/articles/error_reporting Error reporting basics
* @link http://php.net/manual/en/function.error-reporting.php
* @link http://php.net/manual/en/function.set-error-handler.php
* @link http://php.net/manual/en/function.set-exception-handler.php
* @link http://php.net/manual/en/function.register-shutdown-function.php
*/
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER! */
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception).
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* @link http://php.net/manual/en/class.mysqli-driver.php
* @link http://php.net/manual/en/mysqli-driver.report-mode.php
* @link http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// Create a new db connection.
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
// Set the desired connection charset
$connection->set_charset('utf8mb4');
测试数据：
id name age
---------------
9 Julie 23
10 Michael 18
11 Harry 59
创建表语法：
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
您需要添加：
while ($row = $result->fetch_assoc()) {
$page = $row['page'];
}
echo $page;
我尽量避免绑定参数，因为如果管理不当，它会给出奇怪的结果。
我更喜欢绑定值，因为它会复制时间点变量值，而不是维护内存位置连接。
但是，mysqli 似乎只支持简单的非命名参数绑定:(
http://php.net/manual/en/mysqli-stmt.bind-param.php http://php.net/manual/en/mysqli.prepare.php
$stmt = $db_mysqli->prepare("SELECT * FROM table WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$page = $row['page'];
echo $page;
}
我更喜欢PDO而不是mysqli，更容易使用。