Php mysql all results to array

PHP: Save MySQL Result in Array

When we want to process the result of a MySQL query in PHP, it is often practical, to store the data directly into an array. Impcityant functions in this context are mysqli_fetch_array(), mysqli_fetch_row() and mysqli_fetch_assoc(). In this info, I would like to go over this and explain the difference.

Numerically indexed array with mysqli_fetch_row

The function mysqli_fetch_row() returns a numerically indexed array. This means, that we can access the data, for example, with $arr[1] or $arr[3]:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE = mysqli_fetch_row($res); echo $arr[0]; // name echo $arr[1]; // city echo $arr[2]; // country

As the example shows, the order of the elements in the array corresponds to the order of the fields in the query. So we can use the query to determine how our array should be filled.

Associative array with mysqli_fetch_assoc

The function mysqli_fetch_array() returns an associative array. This means, that the fields can not be addressed with the index number but with the name of the field. And this field name is the name of our column from our MySQL database:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE = mysqli_fetch_assoc($res); echo $arr['name']; // name echo $arr['city']; // city echo $arr['country']; // country

Interesting in this context is the function extract(). This function can automatically create individual variables with the respective name from the array, which are then available to us in the code, as the following example shows:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE = mysqli_fetch_assoc($res); extract($arr); echo $name; // name echo $city; // city echo $country; // country

Also the function list() can be used to make individual variables out of the array, as the following example shows:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE $city, $country) = mysqli_fetch_row($res); echo $name; // name echo $city; // city echo $country; // country

In contrast to extract(), however, with list() we have the option of freely defining what our variables should be called. We can, but don’t have to, stick to the names of the columns in our table.

The all-rounder mysqli_fetch_array

The function mysqli_fetch_array() masters to output the data set both numerically indexed as well as an associative array. This can be controled with the parameters MYSQLI_NUM or MYSQLI_ASSOC.

If you omit the parameter completely or you are using MYSQLI_BOTH, you can use both types of indexes:

$arr = mysqli_fetch_array($res); // equivalent to mysqli_fetch_array($res, MYSQL_BOTH); echo $arr[0]; // name echo $arr['city']; // city echo $arr[2]; // country

Calling mysqli_fetch_array($res, MYSQLI_NUM) is equivalent to the function mysqli_fetch_row($res).

Calling mysqli_fetch_array($res, MYSQLI_ASSOC) is equivalent to the function mysqli_fetch_assoc($res).

About the Author

You can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? — sttmedia.com/contact
Show Profile

Related Topics

Send HTML5 Canvas as Image to Server

HTML5 Canvas: Beginner Tutorial Chapter 3 — Rectangles and Circles

jQuery: Send HTML5 Canvas to Server via Ajax

HTML5 Canvas: Beginner Tutorial Chapter 2 — Drawing Lines

Units: SI Prefixes for Powers of Ten

XLS and XLSX: Maximum Number of Columns and Rows

jQuery: Read and Change Data Attribute Value

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.

Источник

How to fetch database data as arrays or objects in php

Hello everyone. In this article we are gonna see how we can turn the data fetched from the database, into a php array or an object. When we use a SELECT query to fetch data, that what we get back is called a result set.
A result set (mysqli_result class) is an object with properties and methods.
In the following paragraphs we are going to use the fetch methods that the mysqli_result object provides us, to create arrays and objects from the database data.
To learn everything about the mysqli_result class visit php.net

The database table

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

id	image	name	price	quantity
1	bike-1.jpeg	Orient Vita Pro 28	284.50	11
2	bike-2.jpeg	Orient S-400 26	198.00	12
3	bike-3.jpeg	Bullet Bora 20	240.99	4
We have more than three records in the table. I wanted you to see here how the table is structured .

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.

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.

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.

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.

I hope you liked the article, and thanks for reading.

View video on Youtube

If you like to say thanks, you can buy me a coffee.

Buy me a coffee with paypal

Источник