php - 如何使用bind_param php mysqli选择行？

Question

通常我将此代码用于回显页面行。它的工作很好。

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

Answer 1

问题描述: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
    ...
)

更多详情请阅读 The mysqli_result class .
关于错误和异常处理:
请注意正确的 error and exception handling系统在开发过程中是必不可少的。 This article以优雅而彻底的方式描述了激活它所需的步骤。
广泛的例子:
为清楚起见，我准备了一个广泛的示例，其中包含使用 mysqli 访问数据库所需的所有组件。延期。它呈现了必须从用户列表中获取一条或多条记录的情况 - 保存在名为 users 的数据库表中。 .每个用户由其 id 描述, name和 age .
由您来实现错误/异常处理系统 - 如上述文章中所述。
index.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/>';
}

connection.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;