Problem description:
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
)
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.
Solution:
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'];
//...
with $row
representing the fetched record and being an array like this:
Array
(
[id] => 13
[page] => 21
...
)
For more details read The mysqli_result
class.
About error and exception handling:
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.
Extensive example:
For clarity, I prepared an extensive example with all components needed for accessing a database using the mysqli
extension. It 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
, name
and age
.
It's up to you to implement the error/exception handling system - as described in the above mentioned article.
index.php:
Option 1) Fetching only one record:
<?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/>';
Option 2) Fetching multiple records:
<?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);
// Set the desired connection charset
$connection->set_charset('utf8mb4');
Test data:
id name age
---------------
9 Julie 23
10 Michael 18
11 Harry 59
Create table syntax:
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=utf8mb4;