mysqli_fetch_array functions to get array of records using MYSQLI API

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]

MySQLI database connection file

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>";
}
?>

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

Subscribe to our YouTube Channel here

mysqli_fetch_array()

Looping through the records

Adding elements to multidimensional array.

Displaying single element ( record )

Subscribe