PHP MySQL Fetch Functions Tutorial

 Types of mysqli_fetch Functions

 

 

Learn the difference between different types of mysql_fetch functions such as PHP mysqli_fetch_row(), PHP mysqli_fetch_array(), PHP mysqli_fetch_assoc(),   PHP mysqli fetch_object().

While working with MySQL Database in PHP, We get a result from the query if the query is successfully executed. Once a result is obtained, you need to access data from it. For,  this, we have different types of MySQL fetch functions. These functions read a row of  data  and  returns it in different formats. For example, 

• as an object or 
• as a simple array or 
• as an associative array. 

So based on requirement you need to choose the fetch function.

This tutorial shows some frequently used mysqli_fetch functions and how to use them. It will also show the result of each fetch function with an example output.

Table of contents

1.   PHP mysqli_fetch_row() Function
2.   PHP mysqli_fetch_array() Function
3.   PHP mysqli_fetch_assoc() Function
4.   PHP mysqli fetch_object() Function

1. PHP mysqli_fetch_row  Function

It takes the $result which is the result of a successful query that is executed with the mysqli_query() function, and fetches one row from a result. Returns a simple array of strings that corresponds to the fetched row. Each string in an array can be accessed with its index.

Example: 

$sql = mysqli_query($con, "SELECT * FROM books");  
while($row = mysqli_fetch_array($sql))
  {
   
   	echo "<pre>";
	print_r($row);  
 	echo "</pre>";
 
  }

Output:
Array
(
    [0] => 1
    [1] => JavaScript
    [2] => Author1
    [3] => 200
)

Array
(
    [0] => 2
    [1] => jQuery
    [2] => Author2
    [3] => 300
) 

2. PHP mysqli_fetch_array Function

TOC

 

Returns the result in the form of associative and numeric indexes. With this array we can access  each column with the indices(0,1,2,3,4,5), or with a column name.

Example:  

$sql = mysqli_query($con, "SELECT * FROM books ");    
while($row = mysqli_fetch_array($sql))
  {
   
   	echo "<pre>";
	print_r($row); echo "<br />";
 	echo "</pre>";
 
  }
 



Output:

Array
(
    [0] => 1
    [book_id] => 1
    [1] => JavaScript
    [title] => JavaScript
    [2] => Author1
    [author] => Author1
    [3] => 200
    [price] => 200
)



Array
(
    [0] => 2
    [book_id] => 2
    [1] => jQuery
    [title] => jQuery
    [2] => Author2
    [author] => Author2
    [3] => 300
    [price] => 300
)

3. PHP mysqli_fetch_assoc Function

 TOC↑

The row from the Query result is returned in the form of associative array, and each name of the column is a key in the array.  

 

Example:

$sql = mysqli_query($con, "SELECT * FROM books");   
while($row = mysqli_fetch_assoc($sql))
  {
   
   	echo "<pre>";
	print_r($row); echo "<br />";
 	echo "</pre>";
 
  }
 


Output:

 Array
(
    [book_id] => 1
    [title] => JavaScript
    [author] => Author1
    [price] => 200
)

Array
(
    [book_id] => 2
    [title] => jQuery
    [author] => Author2
    [price] => 300
)

4. PHP mysqli fetch_object() Function

Goto TOC↑

 

Returns an object with string properties for the fetched row. NULL if there are no more rows in the result set. The ability to return as a different object was added in PHP 5.0.0.

Example: 

$sql = mysqli_query($con, "SELECT * FROM books");  
while($row = mysqli_fetch_object($sql))
  {
   
   	echo "<pre>";
	print_r($row); echo "<br />";
 	echo "</pre>";
 
  }
 

 

Try out the above examples with this full code example, by replacing the line  mysqli_fetch_row($sql).

mysqli_fetch_example.php

<?php 

$con = mysqli_connect('localhost', 'root', '');
mysqli_select_db($con, "booksdb");

$sql = mysqli_query($con, "SELECT * FROM books");  
 

while($row = mysqli_fetch_row($sql))
  {
   
   	echo "<pre>";
	print_r($row); echo "<br />";
 	echo "</pre>";
 
  }

mysqli_close($con);

?>