Apa itu mysql fetch array?

We can retrieve the rows of the tables of a MySQL database in PHP language by using the mysql_fetch_array() function, mysqli_fetch_array() function or PDO_MYSQL driver in the form of arrays. These arrays can be either numeric arrays or associative arrays. The mysql_fetch_array() function has been deprecated in PHP 5.5.0 version and is completely removed in PHP 7.0.0 version. After PHP 5.5.0 version, mysqli_fetch_array() function should be used for fetching the array of rows in the resultset of the MySQL query. Alternatively, the PDO_MYSQL driver enables access from PHP to the database of Mysql and can be used to implement the PHP Data Objects interface. In this article, we will learn about the syntax of the mysqli_fetch_array() function and how this can be used to retrieve the result in the form of an array in PHP with the help of an example.

Syntax of MySQL Fetch Array

When we want to use this function in Object-oriented way we will have to follow the below syntax.

resultOutput mysqli_result::fetch_array(int resulttype= =MYSQLI_BOTH);

In the case of Procedural way, we will have to use the below syntax for fetching array containing rows of resultset of MySQL query –

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

All in One Data Science Bundle(360+ Courses, 50+ projects)

Price
View Courses

360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,241 ratings)

resultOutput mysqli_fetch_array(mysqli_result result,
int resulttype= =MYSQLI_BOTH);

• resultOutput: Both these methods will return the array that will contain the rows of the table. If none of the rows are present in the returned resultset then the function will return a NULL value. Also, be careful about the field names used in the query as the returned resultset will consider the names of the fields in a case-sensitive manner. Besides the numeric indices assigned to the fetched array, we can also use the field names for indexing purpose which is called associative indices.
• Result: In the case of procedural syntax, the result parameter stands for the result set that is retrieved from the mysqli_store_result, mysqli_query, or mysqli_use_result methods.
• Result type: We can specify the type of indexing that should be used in the returned resultset of this function by specifying it using the resulttype field. This field can either have MYSQLI_BOTH, MYSQLI_NUM, or MYSQLI_ASSOC. The specification of the resulttype in the function is optional. When we use associative indices by specifying MYSQLI_ASSOC resulttype then mysqli_fetch_array() will behave in the same manner as mysqli_fetch_assoc(). While in case if we use numeric indices by specifying MYSQLI_NUM resulttype then mysqli_fetch_array() will behave in the same manner as mysqli_fetch_row(). In fact, mysqli_fetch_array() function is considered as the extended version of mysqli_fetch_row() function.

Examples of MySQL Fetch Array

Let us consider one example, firstly we will log in to MySQL and create one table named educba_writers in the database named educba, and then we will insert some records in the educba_writers table. After retrieving the resultset from the MySQL table, we will write the PHP program in which we will fetch the array of the rows that will be present in the educba_writers table.

Select and use the educba database which we want to use for table creation using the following query statement –
use educba;

Execution of the above query gives the following output

use educba;

Now, we will create the educba_writers table that will contain four columns namely id, firstName, rate, and joining_date using the CREATE TABLE statement in the following query –

CREATE TABLE `educba_writers` (
`id` int(11) NOT NULL,
`firstName` varchar(10) COLLATE latin1_danish_ci NOT NULL,
`rate` decimal(5,2) DEFAULT NULL,
`joining_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

Execution of the above query gives the following output –

Let us insert some records in the educba_writers table using the INSERT INTO statement of Mysql in the following query –

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date`) VALUES
(1, 'Payal', NULL, NULL),
(2, 'Vyankatesh', NULL, NULL),
(5, 'Om Prakash', NULL, NULL),
(6, 'Om Prakash', NULL, NULL);

Execution of the above query gives the following output:

Now, we will select the data from educba_writers table by using the following query statement –

select * from educba_writers;

Execution of the above query gives the following output:

It retrieves 4 rows with names payal, vyankatesh. Om Prakash and Om Prakash with ids 1,2,5 and 6. Note that the same data is to be retrieved in the array format when we use mysqli_fetch_array() function in PHP.

Let us prepare one PHP file in our /var/www/html/ path named demo.php and write the code for establishing the connection with the educba database with the help of the user named username and password as the password of that user. Then we will write our SQL query to select the contents of the educba_writers table and finally retrieve the resultset from the query in an array format using mysqli_fetch_array() function and using the indexes of the array elements loop them into while loop and print the record of each row on a different line. The PHP code for this will be as specified below –

<?php
$con=mysqli_connect("127.0.0.1", "username", "password") or
die("Could not connect: " . mysqli_error());
mysqli_select_db($con,"educba");
$result = mysqli_query($con,"SELECT id, firstName FROM educba_writers");
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
printf("\nID: %s Name: %s", $row[0], $row[1]);
}
printf("\n");
mysqli_free_result($result);
?>

Save this file with the name of demo.php in the folder specified earlier. Now, we will run the PHP on the terminal itself and check the output. For this execute the following command on the terminal

php /var/www/html/demo.php

Execution of the above query gives the following output:

We can observe that the same four rows are retrieved in the array format and we have printed the contents storing each row in the array variable $row and then print the elements at 0th and 1st position which are id and firstName column values respectively until the result is present in the array of resultset returned from mysqli_fetch_array() function using the while loop. Note that the returned value of mysqli_fetch_array() function in our case if an array of arrays. In this way, we can retrieve the contents of the queries in the array format while using PHP.

We can even specify the field names instead of indexes that are associative arrays in the demo.php file as shown below –

<?php
$con=mysqli_connect("127.0.0.1", "username", "password") or
die("Could not connect: " . mysqli_error());
mysqli_select_db($con,"educba");
$result = mysqli_query($con,"SELECT id, firstName FROM educba_writers");
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
printf("\nID: %s Name: %s", $row['id'], $row['firstName']);
}
printf("\n");
mysqli_free_result($result);
?>
It returns the same output after executing the following query -
php /var/www/html/demo.php

Conclusion

We can use mysqli_fetch_array() function in PHP 5.5.0 version and higher and mysql_fetch_array() function in the versions prior to PHP 7.0.0. mysql_fetch_array() function has been deprecated from 5.5 versions of PHP and completely removed from 7 and higher versions of PHP. Both these functions help in retrieving the resultset in the form of an array in PHP language and we can use numeric, associative of both types of indexing for the retrieved arrays.

Recommended Articles

This is a guide to MySQL Fetch Array. Here we also discuss the introduction and syntax of mysql fetch array along with different examples and its code implementation. You may also have a look at the following articles to learn more –

Jelaskan apa yang dimaksud MySQL fetch Row?

Apa itu mysql_query?

Apa itu Mysqli_connect ()?

Apa fungsi mysql_select_db?