How to fetch database data as arrays or objects in php

The database table

Here we have the "products" database table from which we will pull out the data.

The php file

The first thing we have to do in the php file is to write the code to connect to the database.
I am going to use here a mysqliobject and pass-in the database connection details.

$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');
	if($mysqli->connect_errno != 0){
		echo $mysqli->connect_error;
		exit();
	}

• In line 1 we create a new mysqli() object and we pass-in the database log-in details ("server name", "our username", "our password", "and the database we want to use").
• In line 2-5 we perform a check to see if the connection is successful. If NOT, we echo out an error and exit the script.

Next we write our query to select the data from the "products" table.

$res = $mysqli->query(
	"SELECT id, image, name, price, quantity FROM products"
);

• In line 7 the $res variable is holding the mysqli_result object which the $mysqli->query method returned.
  Now we can use the $res variable to access the methods of the mysqli_result object, to fetch the columns and rows from the "products" table.
• In line 8 we have our query statement, where we bsically selecting everything from the products table.

Fetch methods

The mysqli_result object has several fetch methods which we can use to grab the data from a result-set.
Those are, fetch_array, fetch_assoc, fetch_object, and fetch_all.

The fetch_array method

The fetch_array() will give us the fetched row as an associative array, a numeric array, or a combination of both. That means that the outcome depends on the constant we pass in the method.
Those constants are MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.

• The MYSQLI_ASSOC returns the fetched row as an associative array.
• The MYSQLI_NUM returns the fetched row as a numeric array.
• The MYSQLI_BOTH returns the fetched row as a combination of both.

In order to fetch all the data from the table we have to use the fetch_array() method with a while() loop.

while($row = $res->fetch_array(MYSQLI_BOTH)){ // using the MYSQLI_BOTH constant.
	$data[] = $row;
}
var_dump($data);

• In line 11 as the loop goes through each table row, that table row is assigned to the $row variable.
• And in line 12 we adding the table row to the $data[] array.
• In line 14 we var_dumb() the $data array so we can see what it holds.

This is what we get when we use the fetch_array(MYSQLI_BOTH) method. We have here is a combination of a numeric, and an associative array.

array (
  0 => array (
    0 => '1', 'id' => '1',
    1 => 'bike-1.jpeg', 'image' => 'bike-1.jpeg',
    2 => 'Orient Vita Pro 28', 'name' => 'Orient Vita Pro 28',
    3 => '284.5', 'price' => '284.5',
    4 => '11', 'quantity' => '11',
  ),
  more rows fetched as arrays...
	

And if we want to display all the product names, we use an foreach() loop to do the task.

foreach ($data as $value) {
	echo $value['name']; // to access the names from the associative array.
	// or
	echo $value[2]; // to access the names from the numeric array.
}

The fetch_assoc method

The fetch_assoc() method fetches the row of a result set as an associative array.

Each key in the array represents the name of one of the table columns.
And if we want to fetch all the data we have to use a while loop to add the data in an array, as we did with the previous method (fetch_array).

while($row = $res->fetch_assoc()){
	$data[] = $row;
}
var_dump($data);

This is what we get with the fetch_assoc method.

array (
  0 =>
    array (
     'id' => '1',
     'image' => 'bike-1.jpeg',
     'name' => 'Orient Vita Pro 28',
     'price' => '284.5',
     'quantity' => '11',
   ),
   more rows fetched as arrays...
	

If we want to access the values of each row, we use a foreach loop, like we did in the previous example.

foreach ($data as $value) {
	echo $value['name']; // to access the names from the associated array.
}

The fetch_object method

The fetch_object method fetches the row of a result set as an object.
The properties in the object are representing the names of the table columns.
And again we use a while loop to fetch all the database tables data.

while($row = $res->fetch_object()){
	$data[] = $row;
}
var_dump($data);

This is what we get with the fetch_object method.

array (
  0 =>
    (object) array(
       'id' => '1',
       'image' => 'bike-1.jpeg',
       'name' => 'Orient Vita Pro 28',
       'price' => '284.5',
       'quantity' => '11',
    ),
    more rows fetched as objects...
	

If we want to access the values of each row, we use here also a foreach loop, but inside the loop we have to use the OOP arrow -> to access the values. Ex. $value->name, the name property will give us the product's name.

foreach ($data as $value) {
	echo $value->name; // to access the names from the object.
}

The fetch_all method

The fetch_all method fetches all result rows as an associative array, a numeric array, or both.

That means that the outcome depends on the constant we pass in the method. If we pass-in the MYSQLI_ASSOC constant we get an associative array.

The MYSQLI_NUM, returns a numeric array, and the MYSQLI_BOTH constant returns a combination of both. We don't need here a while loop to fetch all rows.

$data = $res->fetch_all(MYSQLI_ASSOC); // returns all the rows as an associative array.
$data = $res->fetch_all(MYSQLI_NUM);   // returns all the rows as a numeric array.
$data = $res->fetch_all(MYSQLI_BOTH);  // returns all the rows as a combination of both.

Summary

We saw how to loop through a result set and fetch the data as an array or as an object.

• The fetch_array() method fetches each row as an associative array, a numeric array, or a combination of both.
• The fetch_assoc() method fetches the row of a result set as an associative array.
• The fetch_object() method fetches the row of a result set as an object.
• The fetch_all() method fetches all rows as an associative array, a numeric array, or a combination of both.

Buy me a coffee