Please note that the credits for the sql statement that I used belong to @Barmar, because he had yesterday the idea of the joined queries first.
Now, down under are the two methods to use. Notice that I didn't use any OOP or functions. The reason is that I wanted you to have a compact view of all steps.
How to use mysqli prepared statements and exception handling
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);
try {
// To delete (just for test here).
$get_info_id = 1;
$userId = $get_info_id;
$fetchedData = array();
/*
* 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
cnv.offer_name,
cnv.time,
cnv.payout
FROM conversions AS cnv
LEFT JOIN users AS usr ON usr.affsub = cnv.affsub
WHERE usr.id = ?';
/*
* 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('i', $userId);
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;
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Example code: Mysqli prepared statements & exception handling</title>
</head>
<style>
table {
font-family: "Verdana", Arial, sans-serif;
font-size: 14px;
border-collapse: collapse;
}
table, th, td {
border: 1px solid #ccc;
}
th, td {
padding: 7px;
}
thead {
color: #fff;
font-weight: normal;
background-color: coral;
}
tfoot {
background-color: wheat;
}
tfoot td {
text-align: right;
}
</style>
<body>
<?php
$countOfFetchedData = count($fetchedData);
if ($countOfFetchedData > 0) {
?>
<table>
<thead>
<tr>
<th>Crt. No.</th>
<th>OFFER NAME</th>
<th>TIME</th>
<th>PAYOUT</th>
</tr>
</thead>
<tbody>
<?php
foreach ($fetchedData as $key => $item) {
$offerName = $item->offer_name;
$time = $item->time;
$payout = $item->payout;
?>
<tr>
<td><?php echo $key + 1; ?></td>
<td><?php echo $offerName; ?></td>
<td><?php echo $time; ?></td>
<td><?php echo $payout; ?></td>
</tr>
<?php
}
?>
</tbody>
<tfoot>
<tr>
<td colspan="7">
- <?php echo $countOfFetchedData; ?> records found -
</td>
</tr>
</tfoot>
</table>
<?php
} else {
?>
<span>
No records found.
</span>
<?php
}
?>
</body>
</html>
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 html code too.
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 html code too.
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);
try {
// To delete (just for test here).
$get_info_id = 1;
$userId = $get_info_id;
$fetchedData = array();
/*
* 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
cnv.offer_name,
cnv.time,
cnv.payout
FROM conversions AS cnv
LEFT JOIN users AS usr ON usr.affsub = cnv.affsub
WHERE usr.id = ?';
/*
* 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('i', $userId);
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(
$resOfferName
, $resTime
, $resPayout
);
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->offer_name = $resOfferName;
$fetchedObject->time = $resTime;
$fetchedObject->payout = $resPayout;
$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;
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Example code: Mysqli prepared statements & exception handling</title>
</head>
<style>
table {
font-family: "Verdana", Arial, sans-serif;
font-size: 14px;
border-collapse: collapse;
}
table, th, td {
border: 1px solid #ccc;
}
th, td {
padding: 7px;
}
thead {
color: #fff;
font-weight: normal;
background-color: coral;
}
tfoot {
background-color: wheat;
}
tfoot td {
text-align: right;
}
</style>
<body>
<?php
$countOfFetchedData = count($fetchedData);
if ($countOfFetchedData > 0) {
?>
<table>
<thead>
<tr>
<th>Crt. No.</th>
<th>OFFER NAME</th>
<th>TIME</th>
<th>PAYOUT</th>
</tr>
</thead>
<tbody>
<?php
foreach ($fetchedData as $key => $item) {
$offerName = $item->offer_name;
$time = $item->time;
$payout = $item->payout;
?>
<tr>
<td><?php echo $key + 1; ?></td>
<td><?php echo $offerName; ?></td>
<td><?php echo $time; ?></td>
<td><?php echo $payout; ?></td>
</tr>
<?php
}
?>
</tbody>
<tfoot>
<tr>
<td colspan="7">
- <?php echo $countOfFetchedData; ?> records found -
</td>
</tr>
</tfoot>
</table>
<?php
} else {
?>
<span>
No records found.
</span>
<?php
}
?>
</body>
</html>
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.
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.
Good luck!
