EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL Fetch Array

MySQL Fetch Array

Updated June 1, 2023

MySQL Fetch Array

 

 

Introduction to 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. In versions 5.5.0 and higher of PHP, the mysql_fetch_array() function is no longer supported and PHP 7.0.0 has completely removed it. Instead, one should use the mysqli_fetch_array() function to retrieve rows from a MySQL query resultset. You can also utilize the PDO_MYSQL driver to access the MySQL database and implement the PHP Data Objects interface. This article will explain the syntax of mysqli_fetch_array() and provide an example of how to use it to retrieve results as an array in PHP.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax of MySQL Fetch Array

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

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

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

resultOutput mysqli_fetch_array(mysqli_result result,
int resulttype= =MYSQLI_BOTH);
  • resultOutput: Both these methods will return the array containing the table’s rows. The function will return a NULL value if none of the rows are present in the returned resultset. 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 purposes, 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 the MYSQLI_ASSOC resulttype, then mysqli_fetch_array() will behave like mysqli_fetch_assoc(). While in case we use numeric indices by selecting MYSQLI_NUM resulttype, then mysqli_fetch_array() will behave the same way as mysqli_fetch_row(). The mysqli_fetch_array() function extends the functionality of the 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; 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;

MySQL Fetch Array-1.1

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 –

MySQL Fetch Array-1.2

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:

MySQL Fetch Array-1.3

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

select * from educba_writers;

Execution of the above query gives the following output:

MySQL Fetch Array-1.4

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

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 that user’s password. 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 the 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 demo.php in the folder specified earlier. We will run the PHP on the terminal 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:

MySQL Fetch Array-1.5

Four identical rows were identified in the array format and were stored in the variable $row array. The values of the id and firstName columns were exhibited at positions 0 and 1, respectively. It is imperative to continuously check until the mysqli_fetch_array() function returns the resultset array. Note that the returned value of the mysqli_fetch_array() function, in our case, is 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 –

MySQL Fetch Array-1.6

Conclusion

We can use the mysqli_fetch_array() function in PHP 5.5.0 version and higher and the mysql_fetch_array() function in the versions before PHP 7.0.0. mysql_fetch_array() function has been deprecated from 5.5 versions of PHP and wholly 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 and associative of both types of indexing for the retrieved arrays.

Recommended Articles

We hope that this EDUCBA information on “MySQL Fetch Array” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Workbench
  2. Partition in MySQL
  3. MySQL Dump
  4. MySQL DISTINCT