mysqli_fetch_array functions to get array of records using MYSQLI API

Example : Object Oriented Style

<?Php
require "config.php";// Database connection file.
$q="SELECT * FROM student WHERE id=3";
// Generate resultset
$result_set = $connection->query($q);
$row=$result_set->fetch_array(MYSQLI_NUM);
echo $row[0],$row[1],$row[2],$row[3];
$result->free;
?>

MySQL SELECT Query

To get associative array

<?Php
require "config.php";// Database connection file.
$q="SELECT * FROM student WHERE id=3";
// Generate resultset
$result_set = $connection->query($q);
$row=$result_set->fetch_array(MYSQLI_ASSOC);
echo $row['id'],$row['name'],$row['class'],$row['mark'];
$result_set->free;
?>

To get Both ( column name and index )

<?Php
require "config.php";// Database connection file.
$q="SELECT * FROM student WHERE id=3";
// Generate resultset
$result_set = $connection->query($q);
$row=$result_set->fetch_array(MYSQLI_BOTH);
echo $row['id'],$row[1],$row['class'],$row[3];
$result_set->free;
?>

Procedural style

<?Php
require "config.php";// Database connection file.
// Generate resultset
$q="SELECT * FROM student WHERE id=3";
$result_set = mysqli_query($connection,$q);
$row = mysqli_fetch_array($result_set, MYSQLI_NUM);
echo $row[0],$row[1],$row[2],$row[3];
?>

We used index numbers to get the field data, now we will use field name to collect data

<?Php
require "config.php";// Database connection file.
$q="SELECT * FROM student WHERE id=3";
// Generate resultset
$result_set = mysqli_query($connection,$q");
$row = mysqli_fetch_array($result_set, MYSQLI_ASSOC);
echo $row['id'],$row['name'],$row['class'],$row['mark'];
?>

Using Both

<?Php
require "config.php";// Database connection file.
$q="SELECT * FROM student WHERE id=3";
// Generate resultset
$result_set = mysqli_query($connection,$q");
$row = mysqli_fetch_array($result_set, MYSQLI_BOTH);
echo $row[0],$row['name'],$row[1],$row['mark'];
?>

Looping through the records

Here the while loop will return one row of data as array in each loop execution by using fetch_array() as condition. The record pointer will move to next record after returning present row or record. After returning last record fetch_array() will return NULL which is same as False and while loop will end.

<?Php
require "config.php";// Database connection file.
// Generate resultset
$result_set = mysqli_query($connection,"SELECT * FROM student ");
while($row = mysqli_fetch_array($result_set,MYSQLI_NUM)){
echo $row[0],$row[1],$row[2],$row[3]."<br>";
}
?>

Using object oriented style

<?Php
require "config.php";// Database connection file.
// Generate resultset
$result_set = $connection->query("SELECT * FROM student ");
while($row = $result_set->fetch_array(MYSQLI_ASSOC)){
echo $row['id'],$row['name'],$row['class'],$row['mark']."<br>";
}
?>

Syntax

mysqli_fetch_array($result,$result_type);

$result is the resultset returned by mysqli_query() or mysqli_store_result() or mysqli_use_result()

$result_type A constant tells which type of row data should be returend. There are three types , MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.

MYSQLI_ASSOC : We get one associative array where we access data by usign field name. Example $row['name']
MYSQLI_NUM : We get one numeric array where we access data by using field index numbers . Example $row[2]
MYSQLI_BOTH : We get both in the array so field names and index number can be used. Example $row['class'], $row[1]

Adding elements to multidimensional array.

We will create amultidimensional array by taking records from a database table. Here we are looping through all the records to display records and at the same time adding the record to array.

$data=array("id"=>$row['id'],"name"=>$row['name']);

Note that each student record is an array and it is added to our main array my_array. While adding to an array we can add one more element ( as percentage here ) for each record.
Here is the full code

<?Php
require "config.php";// Database connection file.

$result_set = $connection->query("SELECT * FROM student ");// Generate resultset
$my_array=array();

while($row = $result_set->fetch_array(MYSQLI_ASSOC)){
$percentage=($row['mark']/200)*100; // new element to be stored in our array
$my_array[]=array("id"=>$row['id'],"name"=>$row['name'],"class"=>$row['class'],"mark"=>$row['mark'],"percentage"=>$percentage);
}

$max=sizeof($my_array);
for($i=0; $i<$max; $i++) { 
// displaying all the elements
while (list ($key, $val) = each ($my_array[$i])) { 
echo "$key -> $val <br>"; 
} // inner array while loop

} // outer array for loop
?>

Displaying single element ( record )

echo $my_array[5]['name']; // displaying single record

MySQL DUMP of student table

MYSQLI Functions mysql_query() : Apply query to database

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

mysqli_fetch_array()

Looping through the records

Adding elements to multidimensional array.

Displaying single element ( record )

Subhendu Mohapatra