MySQL Fetch using PHP

PHP provides a huge list of MySQL functions to access the database from the front end. Among those functions, we are going to discuss some of them that are used to fetch data from the database. Such functions differ with the type of results they are expected to return.

In PHP, MySQL fetches results can be obtained by the following functions. All of these functions will fetch only one row per function call. If required, we should call such functions with a loop for getting subsequent MySQL fetch results row by row.

• mysqli_fetch_row()
• mysqli_fetch_assoc()
• mysqli_fetch_array()
• mysqli_fetch_object()
• mysqli_fetch_lengths()
• mysqli_fetch_field()

mysqli_fetch_row()

This function will fetch data about the single row with which the row pointer currently exists. After fetching the entire row details, it will be returned as an array with number indices corresponding to the MySQL field offset.

The mysqli_fetch_row() function requires resource data that is returned by executing a query appropriate to MySQL fetch operations. If no results are found for the query, then mysqli_fetch_row() will return NULL.

Let us consider the Users table, which we had taken for example while seeing about MySQL Left JOIN and MySQL Right JOIN. And the table data and structure is as follows.

The following PHP program is for fetching MySQL data using mysqli_fetch_row(). Before that, the database connections are made with the first two lines as usual.

<?php
$conn = mysqli_connect("localhost", "root", "test", "blog_samples") or die("Connection Error: " . mysqli_error($conn));
$query = "SELECT * from Users";
$result = mysqli_query($conn, $query) or die(mysqli_error($conn));
$row = mysqli_fetch_row($result);
print "<pre>";
print_r($row);
print "<pre>";
?>

This program will return values of the entire first row since the row pointer is at the beginning. So the output will be as shown below that can be cross-checked with the table data shown already.

Array
(
[0] => 1
[1] => admin
[2] => admin123
[3] => student
)

If we want to get all the row information, we should do the same process recursively by iterating through a loop until end of the record. For example,

<?php
while ($row = mysqli_fetch_row($result)) {
    print "<pre>";
    print_r($row);
    print "<pre>";
}
?>

mysqli_fetch_assoc()

This function is similar to the mysqli_fetch_row(), except that, it will return an array of row information containing column values indexed with the column name. So the result type is an associative array where each column name and values of a single row are associated together as name and value pairs.

Let us replace mysqli_fetch_row() with mysqli_fetch_assoc() in the above program which will return the following array.

Array
(
[user_id] => 1
[user_name] => admin
[password] => admin123
[user_type] => student
)

By using this type of MySQL fetch, we can access the data by its name instead of its offset. Since, remembering the order of fields is too tough for a huge list of columns, accessing by field name is easy and thereby this function could be preferred in such a scenario.

mysqli_fetch_array()

This MySQL fetch method returns a resultant array with both indices, that is, field offset and field name. So, it would be used most probably by having both options of indexing.

Unlike the above two functions, mysqli_fetch_array() accepts an optional argument for specifying the resultant array index type and its possible values are,

• MYSQLI_BOTH – It is the default value that would be taken if no second argument is provided for this function. It will provide a resultant array with both indices.
• MYSQLI_NUM – With this option, mysqli_fetch_array() will return an array with offset indices as same as mysqli_fetch_row().
• MYSQLI_ASSOC – With this option, mysqli_fetch_array() will return an array with name indices as same as mysqli_fetch_assoc().

By replacing mysqli_fetch_row() with mysqli_fetch_array(), the output array will be,

Array
(
    [0] => 1
    [user_id] => 1
    [1] => admin
    [user_name] => admin
    [2] => admin123
    [password] => admin123
    [3] => student
    [user_type] => student
)

mysqli_fetch_object()

mysqli_fetch_object() function will return MySQL data with same structure as returned by mysqli_fetch_assoc(), but its type is different. mysqli_fetch_object() returns object whereas mysqli_fetch_assoc() returns array. So, the way of accessing these data will also differ. For example, if we are required to access user_name, after array fetch, it will be done by,

<?php
echo $row["user_name"];
?>

Or else after object fetch, it would be,

<?php
echo $row->user_name;
?>

mysqli_fetch_lengths()

This PHP function is used to return the string length of each column value of the recently fetched row. So, before calculating the string length, any one of the above MySQL fetch functions need to be invoked.

For example, the following program is to fetch single row data using mysqli_fetch_object() and to print the corresponding length array to the browser.

<?php
$conn = mysqli_connect("localhost", "root", "test", "blog_samples") or die("Connection Error: " . mysqli_error($conn));

$query = "SELECT * from Users";
$result = mysqli_query($conn, $query) or die(mysqli_error($conn));
$row = mysqli_fetch_object($result);
$student_length = mysqli_fetch_lengths($result);
print "<pre>";
print_r($row);
print_r($student_length);
print "<pre>";
?>

And the output is,

stdClass Object
(
[user_id] => 1
[user_name] => admin
[password] => admin123
[user_type] => student
)
Array
(
[0] => 1
[1] => 5
[2] => 8
[3] => 7
)

Obviously, the object properties array and the length array are returned as shown above that could be cross-checked manually to ensure the property value length is correct as expected.

mysqli_fetch_field()

Unlike the above functions, mysqli_fetch_field() is for getting the MySQL Database table’s field information instead of record data. And, this function also fetches one field per call and needs loop implementation for getting more fields.

This information array will be returned as an object which includes properties like table name, field name, field maximum length, primary key flag offset and etc. For example, the user_id field details of the Users table is returned as follows.

stdClass Object
(
 [name] => user_id
 [table] => Users
 [def] => 
 [max_length] => 1
 [not_null] => 1
 [primary_key] => 1
 [multiple_key] => 0
 [unique_key] => 0
 [numeric] => 1
 [blob] => 0
 [type] => int
 [unsigned] => 0
 [zerofill] => 0
)

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
    • PHP Responsive Image Gallery using CSS Media Queries
    • PHP MyAdmin
    • Prevent SQL Injection in PHP
    • How to Import SQL into a Database using PHP
    • How to Backup MySQL Database using PHP
    • Restore MySQL Database using PHP
    • Import Excel File into MySQL Database using PHP
    • PHP Database Backup Client for MySQL
    • MySQL Fetch using PHP
    • Access MySQL from PHP
    • PHP CRUD with MySQL
    • CRUD with MySQLi Prepared Statement using PHP
    • PHP PDO CRUD
    • Object-Oriented CRUD System using PHP PDO and MySQL
    • Amazon like Product Category Tree
    • PHP Input Filtering
    • Filtering MySQL Results by Applying PHP Conditions
    • Multi-Select Dropdown Filter in PHP with Database Search
    • Sorting MySQL Column using PHP
    • DataTables Server-side Processing using PHP with MySQL
    • Column Search in DataTables using Server-side Processing
    • Loading List box Dynamically
    • Update/Delete Multiple Rows using PHP
    • PHP Pagination
    • Ajax Pagination with Tabular Records using PHP and jQuery
    • MySQL BLOB using PHP
    • PHP Search and Pagination using PDO
    • Bootstrap Pagination Example in PHP
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js