- PHP Basics
- Learn PHP
- PHP Comments
- PHP Data Types
- PHP Variables
- PHP Operators
- PHP echo
- PHP print
- PHP echo vs. print
- PHP if else
- PHP switch
- PHP for Loop
- PHP while Loop
- PHP do...while Loop
- PHP foreach Loop
- PHP break and continue
- PHP Arrays
- PHP print_r()
- PHP unset()
- PHP Strings
- PHP Functions
- PHP File Handling
- PHP File Handling
- PHP Open File
- PHP Create a File
- PHP Write to File
- PHP Read File
- PHP feof()
- PHP fgetc()
- PHP fgets()
- PHP Close File
- PHP Delete File
- PHP Append to File
- PHP Copy File
- PHP file_get_contents()
- PHP file_put_contents()
- PHP file_exists()
- PHP filesize()
- PHP Rename File
- PHP fseek()
- PHP ftell()
- PHP rewind()
- PHP disk_free_space()
- PHP disk_total_space()
- PHP Create Directory
- PHP Remove Directory
- PHP Get Files/Directories
- PHP Get filename
- PHP Get Path
- PHP filemtime()
- PHP file()
- PHP include()
- PHP require()
- PHP include() vs. require()
- PHP and MySQLi
- PHP and MySQLi
- PHP MySQLi Setup
- PHP MySQLi Create DB
- PHP MySQLi Create Table
- PHP MySQLi Connect to DB
- PHP MySQLi Insert Record
- PHP MySQLi Update Record
- PHP MySQLi Fetch Record
- PHP MySQLi Delete Record
- PHP MySQLi SignUp Page
- PHP MySQLi LogIn Page
- PHP MySQLi Store User Data
- PHP MySQLi Close Connection
- PHP Misc Topics
- PHP Object Oriented
- PHP new Keyword
- PHP Cookies
- PHP Sessions
- PHP Date and Time
- PHP GET vs. POST
- PHP File Upload
- PHP Image Processing
PHP MySQLi Fetch Data from Database
This article is created to describe the ways to fetch and display the data from the MySQL database.
Here are the list of functions that can be used to fetch data from MySQL database, using PHP MySQLi script:
- fetch_row() is used to to fetch and return the result as an enumerated array, in object-oriented style
- mysqli_fetch_row() is used to to fetch and return the result as an enumerated array, in procedural style
- fetch_assoc() is used to fetch and return the result as an associative array, in object-oriented style
- mysqli_fetch_assoc() is used to fetch and return the result as an associative array, in procedural style
- fetch_array() is used, when we need to fetch and get the result as an enumerated array or as an associative array, or as both, in object-oriented style
- mysqli_fetch_array() is used, when we need to fetch and get the result as an enumerated array or as an associative array, or as both, in procedural style
Note - The fetch_row(), fetch_assoc(), and fetch_array() are used in PHP MySQLi object-oriented script.
Note - The mysqli_fetch_row(), mysqli_fetch_assoc(), and mysqli_fetch_array() are used in PHP MySQLi procedural script.
PHP MySQLi Script to Fetch Data using fetch_row()
<?php $server = "localhost"; $user = "root"; $pass = ""; $db = "fresherearth"; $conn = new mysqli($server, $user, $pass, $db); if($conn -> connect_errno) { echo "Connection to the database failed!<BR>"; echo "Reason: ", $conn -> connect_error; exit(); } $sql = "SELECT * FROM customer"; $result = $conn -> query($sql); if($result) { while($row = $result -> fetch_row()) { echo "Name: ", $row[1]; echo "<BR>"; echo "Age: ", $row[2]; echo "<HR>"; } $result -> free_result(); } else { echo "Something went wrong!<BR>"; echo "Error Description: ", $conn -> error; } $conn -> close(); ?>
The output produced by above PHP example, on fetching data from database is shown in the snapshot given below:
In above example, since the name field is available at second column, therefore I have used 1 as index to get that column. In array, indexing starts with 0, therefore 1 refers to the second column.
Note - The mysqli() is used to open a connection to the MySQL database server, in object-oriented style.
Note - The new keyword is used to create a new object.
Note - The connect_errno is used to get/return the error code (if any) from last connect call, in object-oriented style.
Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.
Note - The exit() is used to terminate the execution of the current PHP script.
Note - The query() is used to perform query on the MySQL database, in object-oriented style.
Note - The free_result() is used to free the stored result, in object-oriented style.
Note - The error is used to return the description of error (if any), by the most recent function call, in object-oriented style.
Note - The close() is used to close an opened connection, in object-oriented style.
The above example, can also be written as:
<?php $conn = new mysqli("localhost", "root", "", "fresherearth"); if(!$conn -> connect_errno) { $sql = "SELECT * FROM customer"; if($result = $conn -> query($sql)) { while($row = $result -> fetch_row()) { echo "Name: ", $row[1]; echo "<BR>"; echo "Age: ", $row[2]; echo "<HR>"; } $result -> free_result(); } } $conn -> close(); ?>
PHP MySQLi Script to Fetch Data using mysqli_fetch_row()
<?php $conn = mysqli_connect("localhost", "root", "", "fresherearth"); if(!mysqli_connect_errno()) { $sql = "SELECT * FROM customer"; if($result = mysqli_query($conn, $sql)) { while($row = mysqli_fetch_row($result)) { echo "Name: ", $row[1]; echo "<BR>"; echo "Age: ", $row[2]; echo "<HR>"; } mysqli_free_result($result); } } mysqli_close($conn); ?>
Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.
Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.
Note - The mysqli_query() is used to perform query on the MySQL database, in procedural style.
Note - The mysqli_free_result() is used to free the stored result, in procedural style.
Note - The mysqli_close() is used to close an opened connection to the MySQL database, in procedural style.
PHP MySQLi Script to Fetch Data using fetch_assoc()
<?php $conn = new mysqli("localhost", "root", "", "fresherearth"); if(!$conn -> connect_errno) { $sql = "SELECT * FROM customer"; if($result = $conn -> query($sql)) { while($row = $result -> fetch_assoc()) { echo "Name: ", $row['name']; echo "<BR>"; echo "Age: ", $row['age']; echo "<HR>"; } $result -> free_result(); } } $conn -> close(); ?>
PHP MySQLi Script to Fetch Data using mysqli_fetch_assoc()
<?php $conn = mysqli_connect("localhost", "root", "", "fresherearth"); if(!mysqli_connect_errno()) { $sql = "SELECT * FROM customer"; if($result = mysqli_query($conn, $sql)) { while($row = mysqli_fetch_assoc($result)) { echo "Name: ", $row['name']; echo "<BR>"; echo "Age: ", $row['age']; echo "<HR>"; } mysqli_free_result($result); } } mysqli_close($conn); ?>
PHP MySQLi Script to Fetch Data using fetch_array()
<?php $conn = new mysqli("localhost", "root", "", "fresherearth"); if(!$conn -> connect_errno) { $sql = "SELECT * FROM customer"; if($result = $conn -> query($sql)) { while($row = $result -> fetch_array()) { echo "Name: ", $row['name']; echo "<BR>"; echo "Age: ", $row[2]; echo "<HR>"; } $result -> free_result(); } } $conn -> close(); ?>
As an instructor, my job is to give the best advice. So what I want to suggest is, use either fetch_array() (if working with PHP MySQLi object-oriented) or mysqli_fetch_array() (if working with PHP MySQLi procedural), so that you can access the fields, using both name and number.
PHP MySQLi Script to Fetch Data using mysqli_fetch_array()
<?php $conn = mysqli_connect("localhost", "root", "", "fresherearth"); if(!mysqli_connect_errno()) { $sql = "SELECT * FROM customer"; if($result = mysqli_query($conn, $sql)) { while($row = mysqli_fetch_array($result)) { echo "Name: ", $row[1]; echo "<BR>"; echo "Age: ", $row['age']; echo "<HR>"; } mysqli_free_result($result); } } mysqli_close($conn); ?>
All the above examples of fetching data from the MySQL database, does the same job.
PHP MySQLi Fetch Data and Display in Table
You can also wrap the output into an HTML table, to display the data, looks impressive and more readable. Let me create another PHP MySQLi script to fetch data using any fetching function say fetch_array(), to display the data in the form of table, this time:
<?php $driver = new mysqli_driver(); $driver -> report_mode = MYSQLI_REPORT_OFF; $server = "localhost"; $user = "root"; $pass = ""; $db = "fresherearth"; $conn = @new mysqli($server, $user, $pass, $db); if($conn -> connect_errno) { echo "Connection to the database failed!<BR>"; echo "Reason: ", $conn -> connect_error; exit(); } $sql = "SELECT name, email FROM customer"; $result = $conn -> query($sql); if($result) { echo "<TABLE>"; echo "<TR>"; echo "<TH>Name</TH>"; echo "<TH>Email</TH>"; echo "</TR>"; while($row = $result -> fetch_array()) { echo "<TR>"; echo "<TD>$row[0]</TD>"; echo "<TD>$row[1]</TD>"; echo "</TR>"; } echo "</TABLE>"; $result -> free_result(); } else { echo "Something went wrong!<BR>"; echo "Error Description: ", $conn -> error; } $conn -> close(); ?>
Now the output should be:
Note - The mysqli_driver() is used to modify the error reporting mode, in object-oriented style.
The first two statements in above example, are used to hide the default error. For example, if I use the wrong column name say FullName in place of name, while fetching the data, then the output looks like:
Data has not been inserted! Reason: Unknown column 'FullName' in 'field list'
Note - The @ before new is used to suppress the error (if any), while creating an object of the class mysqli.
If you want to style more, then you can wrap the PHP script in HTML, in this way:
<HTML> <HEAD> <STYLE> table{width: 100%;} th, td{padding: 12px;} th{text-align: left; background-color: antiquewhite;} tr:nth-child(even){background-color: aliceblue;} </STYLE> </HEAD> <BODY> <?php // PHP script goes here ?> </BODY> </HTML>
Now the output should be:
Note - Since, this article is not about CSS, therefore I will not go in detail. But if you want, refer to its separate tutorial.
« Previous Tutorial Next Tutorial »