我需要帮助转换使用php PDO函数的代码，但我想使用mysqli，请帮我做到这一点。
<?php
// PDO connect *********
function connect()
{
return new PDO('mysql:host=localhost;dbname=smartstorey', 'root', 'Sph!nx2g0!!', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}
$pdo = connect();
$keyword = '%'.$_POST['keyword'].'%';
$sql = "SELECT * FROM product WHERE auto_complete_product_name LIKE (:keyword)";
$query = $pdo->prepare($sql);
$query->bindParam(':keyword', $keyword, PDO::PARAM_STR);
$query->execute();
$list = $query->fetchAll();
foreach ($list as $rs) {
// put in bold the written text
$country_name = str_replace($_POST['keyword'], '<b>'.$_POST['keyword'].'</b>', $rs['auto_complete_product_name']);
// add new option
echo '<li onclick="set_item(''.str_replace("'", "'", $rs['auto_complete_product_name']).'')">'.$country_name.'</li>';
}
?>
下面是您可以选择的两种方法。请注意，我没有使用任何OOP或函数作为代码结构(但MySQLi方法是OOP(，因为我想提供所有步骤的紧凑视图。
如何使用 MySQLi 预准备语句和异常处理
选项 1：使用 get_result(( + fetch_object(( 或 fetch_array(( 或 fetch_all((：
此方法(推荐(仅在安装/激活驱动程序 mysqlnd(MySQL 本机驱动程序(时才有效。我认为默认情况下，该驱动程序在 PHP>= 5.3 中激活。实现代码并让它运行。它应该有效。如果它有效，那么它就是完美的。如果没有，请尝试激活 mysqlnd 驱动程序，例如在 php.ini 中取消注释extension=php_mysqli_mysqlnd.dll。否则，必须使用第二种方法 (2(。
<?php
/*
* Define constants for db connection.
*/
define('MYSQL_HOST', '...');
define('MYSQL_PORT', '...');
define('MYSQL_DATABASE', '...');
define('MYSQL_CHARSET', 'utf8');
define('MYSQL_USERNAME', '...');
define('MYSQL_PASSWORD', '...');
/*
* Activate PHP error reporting.
* Use ONLY on development code, NEVER on production code!!!
* ALWAYS resolve WARNINGS and ERRORS.
* I recommend to always resolve NOTICES too.
*/
error_reporting(E_ALL);
ini_set('display_errors', 1);
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception). They are catched in the try-catch block.
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* See:
* http://php.net/manual/en/class.mysqli-driver.php
* http://php.net/manual/en/mysqli-driver.report-mode.php
* http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
if (isset($_POST['keyword'])) {
$keyword = $_POST['keyword'];
$keywordPlaceholder = '%' . $keyword . '%';
$fetchedData = array();
/*
* ------------------------------------
* FETCH DATA.
* ------------------------------------
*/
try {
/*
* Create the db connection.
*
* Throws mysqli_sql_exception.
* See: http://php.net/manual/en/mysqli.construct.php
*/
$connection = new mysqli(
MYSQL_HOST
, MYSQL_USERNAME
, MYSQL_PASSWORD
, MYSQL_DATABASE
, MYSQL_PORT
);
if ($connection->connect_error) {
throw new Exception('Connect error: ' . $connection->connect_errno . ' - ' . $connection->connect_error);
}
/*
* 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.
*
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT * FROM product WHERE auto_complete_product_name LIKE ?';
/*
* Prepare the SQL statement for execution.
*
* Throws mysqli_sql_exception.
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
if (!$statement) {
throw new Exception('Prepare error: ' . $connection->errno . ' - ' . $connection->error);
}
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to mysqli::prepare. The first
* argument of mysqli_stmt::bind_param is a string that contains one
* or more characters which specify the types for the corresponding bind variables.
*
* See: http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$bound = $statement->bind_param('s', $keywordPlaceholder);
if (!$bound) {
throw new Exception('Bind error: The variables could not be bound to the prepared statement');
}
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* See: http://php.net/manual/en/mysqli-stmt.execute.php
*/
$executed = $statement->execute();
if (!$executed) {
throw new Exception('Execute error: The prepared statement could not be executed!');
}
/*
* Get the result set from the prepared statement. In case of
* failure use errno, error and/or error_list to see the error.
*
* 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.
*
* See:
* http://php.net/manual/en/mysqli-stmt.get-result.php
* https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
if (!$result) {
throw new Exception('Get result error: ' . $connection->errno . ' - ' . $connection->error);
}
/*
* Get the number of rows in the result.
*
* See: http://php.net/manual/en/mysqli-result.num-rows.php
*/
$numberOfRows = $result->num_rows;
/*
* Fetch data and save it into $fetchedData array.
*
* See: http://php.net/manual/en/mysqli-result.fetch-array.php
*/
if ($numberOfRows > 0) {
/*
* Use mysqli_result::fetch_object to fetch a row - as object -
* at a time. E.g. use it in a loop construct like 'while'.
*/
while ($row = $result->fetch_object()) {
$fetchedData[] = $row;
}
}
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* See: 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.
*
* See: http://php.net/manual/en/mysqli-stmt.close.php
*/
$statementClosed = $statement->close();
if (!$statementClosed) {
throw new Exception('The prepared statement could not be closed!');
}
// Close db connection.
$connectionClosed = $connection->close();
if (!$connectionClosed) {
throw new Exception('The db connection could not be closed!');
}
} catch (mysqli_sql_exception $e) {
echo 'Error: ' . $e->getCode() . ' - ' . $e->getMessage();
exit();
} catch (Exception $e) {
echo $e->getMessage();
exit();
}
/*
* Disable internal report functions.
*
* MYSQLI_REPORT_OFF: Turns reporting off.
*
* See:
* http://php.net/manual/en/class.mysqli-driver.php
* http://php.net/manual/en/mysqli-driver.report-mode.php
* http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;
/*
* ------------------------------------
* DISPLAY DATA.
* ------------------------------------
*/
// Check if data fetched.
$countOfFetchedData = count($fetchedData);
if ($countOfFetchedData > 0) {
foreach ($fetchedData as $key => $item) {
$autoCompleteProductName = $item->auto_complete_product_name;
// Put in bold the written text.
$country_name = str_replace($keyword, '<b>' . $keyword . '</b>', $autoCompleteProductName);
// Add new option.
echo '<li onclick="set_item('' . str_replace("'", "'", $autoCompleteProductName) . '')">' . $country_name . '</li>';
}
} else {
echo 'No records found';
}
}
注意：如何使用fetch_array((代替fetch_object((：
//...
if ($numberOfRows > 0) {
/*
* Use mysqli_result::fetch_array to fetch a row at a time.
* e.g. use it in a loop construct like 'while'.
*/
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$fetchedData[] = $row;
}
}
//...
在"显示数据"代码中也进行相应的更改：
$autoCompleteProductName = $item['auto_complete_product_name'];
注意：如何使用fetch_all((代替fetch_object((：
//...
if ($numberOfRows > 0) {
/*
* Use mysqli_result::fetch_all to fetch all rows at once.
*/
$fetchedData = $result->fetch_all(MYSQLI_ASSOC);
}
//...
在"显示数据"代码中也进行相应的更改：
$autoCompleteProductName = $item['auto_complete_product_name'];
选项 2：使用 store_result(( + bind_result(( + fetch((：
无需驱动程序mysqlnd(MySQL本机驱动程序(即可工作。
<?php
/*
* Define constants for db connection.
*/
define('MYSQL_HOST', '...');
define('MYSQL_PORT', '...');
define('MYSQL_DATABASE', '...');
define('MYSQL_CHARSET', 'utf8');
define('MYSQL_USERNAME', '...');
define('MYSQL_PASSWORD', '...');
/*
* Activate PHP error reporting.
* Use ONLY on development code, NEVER on production code!!!
* ALWAYS resolve WARNINGS and ERRORS.
* I recommend to always resolve NOTICES too.
*/
error_reporting(E_ALL);
ini_set('display_errors', 1);
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception). They are catched in the try-catch block.
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* See:
* http://php.net/manual/en/class.mysqli-driver.php
* http://php.net/manual/en/mysqli-driver.report-mode.php
* http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
if (isset($_POST['keyword'])) {
$keyword = $_POST['keyword'];
$keywordPlaceholder = '%' . $keyword . '%';
$fetchedData = array();
/*
* ------------------------------------
* FETCH DATA.
* ------------------------------------
*/
try {
/*
* Create the db connection.
*
* Throws mysqli_sql_exception.
* See: http://php.net/manual/en/mysqli.construct.php
*/
$connection = new mysqli(
MYSQL_HOST
, MYSQL_USERNAME
, MYSQL_PASSWORD
, MYSQL_DATABASE
, MYSQL_PORT
);
if ($connection->connect_error) {
throw new Exception('Connect error: ' . $connection->connect_errno . ' - ' . $connection->connect_error);
}
/*
* 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.
*
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT * FROM product WHERE auto_complete_product_name LIKE ?';
/*
* Prepare the SQL statement for execution.
*
* Throws mysqli_sql_exception.
* See: http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
if (!$statement) {
throw new Exception('Prepare error: ' . $connection->errno . ' - ' . $connection->error);
}
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to mysqli::prepare. The first
* argument of mysqli_stmt::bind_param is a string that contains one
* or more characters which specify the types for the corresponding bind variables.
*
* See: http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$bound = $statement->bind_param('s', $keywordPlaceholder);
if (!$bound) {
throw new Exception('Bind error: The variables could not be bound to the prepared statement');
}
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* See: http://php.net/manual/en/mysqli-stmt.execute.php
*/
$executed = $statement->execute();
if (!$executed) {
throw new Exception('Execute error: The prepared statement could not be executed!');
}
/*
* Transfer the result set resulted from executing the prepared statement.
* E.g. store, e.g. buffer the result set into the (same) prepared statement.
*
* See:
* http://php.net/manual/en/mysqli-stmt.store-result.php
* https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$resultStored = $statement->store_result();
if (!$resultStored) {
throw new Exception('Store result error: The result set could not be transfered');
}
/*
* Get the number of rows from the prepared statement.
*
* See: http://php.net/manual/en/mysqli-stmt.num-rows.php
*/
$numberOfRows = $statement->num_rows;
/*
* Fetch data and save it into $fetchedData array.
*
* See: http://php.net/manual/en/mysqli-result.fetch-array.php
*/
if ($numberOfRows > 0) {
/*
* Bind the result set columns to corresponding variables.
* E.g. these variables will hold the column values after fetching.
*
* See: http://php.net/manual/en/mysqli-stmt.bind-result.php
*/
$varsBound = $statement->bind_result(
$resAutoCompleteProductName
);
if (!$varsBound) {
throw new Exception('Bind result error: The result set columns could not be bound to variables');
}
/*
* Fetch results from the result set (of the prepared statement) into the bound variables.
*
* See: http://php.net/manual/en/mysqli-stmt.fetch.php
*/
while ($row = $statement->fetch()) {
$fetchedObject = new stdClass();
$fetchedObject->auto_complete_product_name = $resAutoCompleteProductName;
$fetchedData[] = $fetchedObject;
}
}
/*
* Frees the result memory associated with the statement,
* which was allocated by mysqli_stmt::store_result.
*
* See: http://php.net/manual/en/mysqli-stmt.store-result.php
*/
$statement->free_result();
/*
* 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.
*
* See: http://php.net/manual/en/mysqli-stmt.close.php
*/
$statementClosed = $statement->close();
if (!$statementClosed) {
throw new Exception('The prepared statement could not be closed!');
}
// Close db connection.
$connectionClosed = $connection->close();
if (!$connectionClosed) {
throw new Exception('The db connection could not be closed!');
}
} catch (mysqli_sql_exception $e) {
echo 'Error: ' . $e->getCode() . ' - ' . $e->getMessage();
exit();
} catch (Exception $e) {
echo $e->getMessage();
exit();
}
/*
* Disable internal report functions.
*
* MYSQLI_REPORT_OFF: Turns reporting off.
*
* See:
* http://php.net/manual/en/class.mysqli-driver.php
* http://php.net/manual/en/mysqli-driver.report-mode.php
* http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;
/*
* ------------------------------------
* DISPLAY DATA.
* ------------------------------------
*/
$countOfFetchedData = count($fetchedData);
if ($countOfFetchedData > 0) {
foreach ($fetchedData as $key => $item) {
$autoCompleteProductName = $item->auto_complete_product_name;
// Put in bold the written text.
$country_name = str_replace($keyword, '<b>' . $keyword . '</b>', $autoCompleteProductName);
// Add new option.
echo '<li onclick="set_item('' . str_replace("'", "'", $autoCompleteProductName) . '')">' . $country_name . '</li>';
}
} else {
echo 'No records found';
}
}
最后，我建议您使用面向对象的方法，例如实现MySQLiConnection类(用于处理数据库连接(和MySQLiAdapter类(用于处理查询功能(。这两个类只应实例化一次。MySQLiConnection 应作为构造函数参数传递给 MySQLiAdapter 类。MySQLiAdapter 类需要一个 MySQLiConnection 类来查询数据库和接收结果。你也可以通过实现相应的接口来扩展它们的使用，但我试图保持我的解释简单。
我还建议你使用PDO而不是MySQLi。我在实现此代码时发现的原因之一是：MySQLi 中有点挑战性的异常处理系统。
祝你好运！