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.
How to use MySQLi prepared statements and exception handling
OPTION 1: Use get_result() + fetch_object() or fetch_array() or fetch_all():
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';
}
}
NB: How to use fetch_array() instead of 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'];
NB: How to use fetch_all() instead of 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'];
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->con
