如何使用bind_param php mysqli选择行? [英] How to select row using bind_param php mysqli?
问题描述
通常，我将此代码用于回显页面行.很好.
Normally i use this code for echo page rows. It's work good.
$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此代码用于回显页面行.但是不行，我该怎么办?
Now i use bind_param this code for echo page rows. But not work , how can i do ?
$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;
推荐答案
方法get_result返回的mysqli_result对象看起来像这样:
The mysqli_result object returned by the method get_result looks something like this:
mysqli_result Object
(
[current_field] => 0
[field_count] => 3
[lengths] =>
[num_rows] => 1
[type] => 0
)
如您所见，该对象仅公开一些有关记录集的属性(字段数，行数等)，您需要从中引用数据.因此，您不能直接从中引用字段值.为了获取所需的数据，您必须调用mysqli_result类中定义的方法之一(fetch_all，fetch_array，fetch_assoc等):
As you can see, this object exposes only some properties (number of fields, number of rows, etc) about the record set from which you need to reference your data. So, you can not directly reference field values from it. In order to reach to the needed data you'll have to call one of the methods defined in the mysqli_result class (fetch_all, fetch_array, fetch_assoc, etc):
$result = $stmt->get_result();
$row = $result->fetch_array(MYSQLI_ASSOC);
$page = $row['page'];
其中$row代表获取的记录，并且是这样的数组:
with $row representing the fetched record and being an array like this:
Array
(
[id] => 13
[page] => 21
...
)
有关更多详细信息，请阅读 mysqli_result类
For more details read The mysqli_result class.
请注意，正确的错误和异常处理系统是在发展过程中至关重要. 本文描述了以优雅而透彻的方式激活它的步骤.
Please note that a proper error and exception handling system is essential in the developing process. This article describes the steps needed to activate it in an elegant and thoroughly manner.
为清楚起见，我准备了一个广泛的示例，其中包含使用mysqli扩展名访问数据库所需的所有组件.如上文所述，由您决定是否要执行错误/异常处理系统.
For clarity, I prepared an extensive example with all components needed for accessing a database using the mysqli extension. It's up to you to implement the error/exception handling system - as described in the above mentioned article.
该示例展示了必须从用户列表中获取一个或多个记录的情况-保存在名为users的数据库表中.每个用户都用其id，其name和age来描述.
The example presents the situation of having to fetch one or more records from a list of users - saved in a db table named users. Each user is described by its id, its name and age.
选项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);
测试数据
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=utf8;
这篇关于如何使用bind_param php mysqli选择行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
