PHP mysqli code to fetch data from the database

This article will describe how to retrieve and display data from the MySQL database.

Here is the list of functions that can be used to fetch data from a MySQL database using the PHP mysqli script:

• The fetch_row() function is used to fetch and return the result as an enumerated array in object-oriented style.
• The mysqli_fetch_row() function is used to fetch and return the result as an enumerated array in procedural style.
• The fetch_assoc() function is used to fetch and return the result as an associative array in object-oriented style.
• The mysqli_fetch_assoc() function is used to fetch and return the result as an associative array in procedural style.
• The fetch_array() function is used when we need to fetch and get the result as an enumerated array, an associative array, or both in object-oriented style.
• The mysqli_fetch_array() function is used when we need to fetch and get the result as an enumerated array, an associative array, or both in procedural style.
• The fetch() function is used to fetch results from a prepared statement into bound variables in PHP mysqli object-oriented style.
• The mysqli_stmt_fetch() function is used to fetch results from a prepared statement into bound variables in PHP mysqli procedural style.

Note: The fetch_row(), fetch_assoc(), fetch_array(), and fetch() functions are used in PHP mysqli object-oriented script.

Note: The mysqli_fetch_row(), mysqli_fetch_assoc(), mysqli_fetch_array(), and mysqli_stmt_fetch() functions are used in PHP mysqli procedural script.

PHP mysqli script to fetch data using fetch_row()

<?php
   $server = "localhost";
   $user = "root";
   $pass = "";
   $db = "codescracker";
   
   $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 the above PHP example of fetching data from database is shown in the snapshot given below:

In the above example, since the name field is available in the second column, I have used 1 as the index to get that column. In an array, indexing starts with 0, so 1 refers to the second column.

Note: The mysqli() function 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 or return the error code (if any) from the last connect call in object-oriented style.

Note: The connect_error is used to get the error description (if any) from the last connection in object-oriented style.

Note: The exit() function is used to terminate the execution of the current PHP script.

Note: The query() function is used to perform queries on the MySQL database in object-oriented style.

Note: The free_result() function is used to free the stored result in object-oriented style.

Note: The error is used to return the description of the error (if any) from 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", "", "codescracker");
   
   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", "", "codescracker");
   
   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() function is used to open a connection to the MySQL database server in procedural style.

Note: The mysqli_connect_errno() function is used to get or return the error code (if any) from the last connect call in procedural style.

Note: The mysqli_query() function is used to perform queries on the MySQL database in procedural style.

Note: The mysqli_free_result() function is used to free the stored result in procedural style.

Note: The mysqli_close() function is used to close an opened connection to the MySQL database in procedural style.

Similarly, you can use PHP and mysqli object-oriented or procedural scripts to retrieve records from the table. You can also use the "fetch()" and "mysqli_stmt_fetch()" functions to safely fetch records from the database table using prepared statements.

PHP mysqli Fetch Data and Display in Table

You can also wrap the output into an HTML table to display the data, which looks impressive and is 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 a table, this time:

<?php
   $driver = new mysqli_driver();
   $driver -> report_mode = MYSQLI_REPORT_OFF;
   
   $server = "localhost";
   $user = "root";
   $pass = "";
   $db = "codescracker";
   
   $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 the 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 this:

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 more style, 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, I will not go into detail. But if you want to learn more, refer to the separate tutorial.

PHP Online Test