将php PDO代码转换为mysqli代码 [英] Convert php PDO code to mysqli code
本文介绍了将php PDO代码转换为mysqli代码的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想帮助转换使用php PDO函数的此代码，但我想使用mysqli，请帮助我做到这一点.
I want help converting this code which is using php PDO function but i want to use mysqli, please help me do this.
<?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)，因为我想提供所有步骤的紧凑视图.
Down under are the two methods from which you can choose. Notice that I didn't use any OOP or functions for the code structure (but MySQLi methods are OOP), because I wanted to provide a compact view of all steps.
仅当安装/激活了驱动程序mysqlnd( MySQL Native Driver )时，此方法(推荐)才有效.我认为该驱动程序默认是在PHP> = 5.3中激活的.实施代码并使其运行.它应该工作.如果有效，那就太完美了.如果没有，请尝试激活mysqlnd驱动程序，例如在php.ini中取消注释extension=php_mysqli_mysqlnd.dll.否则，您必须使用第二种方法(2).
This method (recommended) works only if the driver mysqlnd (MySQL Native Driver) is installed/activated. I think the driver is by default activated in PHP >= 5.3. Implement the code and let it run. It should work. If it works, then it's perfect. If not, try to activate mysqlnd driver, e.g. uncomment extension=php_mysqli_mysqlnd.dll in php.ini. Otherwise you must use the second method (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;
}
}
//...
也在显示数据"代码中进行相应的更改:
Make the corresponding changes in the "Display data" code too:
$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);
}
//...
也在显示数据"代码中进行相应的更改:
Make the corresponding changes in the "Display data" code too:
$autoCompleteProductName = $item['auto_complete_product_name'];
选项2:使用store_result()+ bind_result()+ fetch():
在没有驱动程序mysqlnd( MySQL本机驱动程序)的情况下工作.
OPTION 2: Use store_result() + bind_result() + fetch():
Works without the driver mysqlnd (MySQL Native Driver).
<?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类来查询数据库并接收结果.您也可以通过实现相应的接口来扩展它们的使用，但我试图使我的解释保持简单.
In the end I'd suggest you to use an object-oriented approach, like implementing a MySQLiConnection class (for handling the db connection) and a MySQLiAdapter class (for handling the query functionality). Both classes should be instantiated only once. The MySQLiConnection should be passed as constructor argument to the MySQLiAdapter class. MySQLiAdapter class needs an MySQLiConnection class for querying the db and for receiving the results. You could extend their use by implementing corresponding interfaces too, but I tried to keep my explanation simple.
我还建议您使用PDO代替MySQLi.实现此代码时发现的原因之一:MySQLi中具有挑战性的异常处理系统.
I'd also suggest you to use PDO instead of MySQLi. One of the reasons I've discovered when I implemented this code: the somewhat challenging exception handling system in MySQLi.
祝你好运！
这篇关于将php PDO代码转换为mysqli代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
