Retrieve Data From MySQL Database Using PHP

Previously we have discussed how to connect MySQL with PHP, this time, we’ll discuss how to retrieve data from MySQL database using PHP.

It has been mentioned in the article that we are not recommended to use functions started with mysql_ like mysql_connect(), mysql_query, etc., because these functions have been removed in PHP version 7.

So we can guarantee that our applications will still work in the future, therefore, on this tutorial we’ll use PHP functions that begin with mysqli_ (with an additional i).

Download files used in this tutorial:

DOWNLOAD FILE

Table of contents:

1. PHP Functions Used to Retrieve Data From MySQL Database
2. Creating MySQL Table
3. Using mysqli_fetch_array() Function
4. Using mysqli_fetch_assoc() Function
5. Using mysqli_fetch_row() Function
6. Creating Temporary Field
7. Do we need to clean-up the query result?

I. PHP Functions Used to Retrieve Data From MySQL Database

PHP Functions that can be used to run all of MySQL query is mysqli_query().

Because we’ll retrieve MySQL data, so we use SQL commands beginning with SELECT statement, so the function becomes mysqli_query('SELECT ...')

Well, to be able to display data from the query results, we need to access the data, to do so, we can use PHP functions beginning with mysqli_fetch_ (fetch = take). The functions are:

• mysqli_fetch_array()
  By default, this function will generate indexed array and associative array, to generate only one of them, we must give additional parameters: FETCH_ASSOC for associative array and FETCH_ROW for an indexed array, we’ll discuss it later.
• mysqli_fetch_assoc()
  This function will generate an associative array with key name taken form field names of the table.
• mysqli_fetch_row()
  This function will generate an indexed array (array with numeric key – 0, 1, 2, 3, 4, 5, etc …).

The differences of the three functions above look like the following figure:

To understand more about array, you can refer to this article: Understanding Array In PHP

From the three functions above, the most efficient is mysqli_fetch_assoc(), because it only produces one type of array, but we are free to use all of them because the difference in execution time is not significant.

The Loop Function

Each time the above functions called, it will generate one line of data row starting from 1st row (first call), 2nd row (2nd call), and so on until the last row, so we don’t know how many times we will repeat this function.

Because of its behavior, we use the while loop, to know about while loop, please refer to this article: Understanding While and Do While In PHP

II. Creating MySQL Table

In this tutorial we’ll use some sales data that is stored in a table named sales and database named tutorials. The data content look like the following:

trans_id	prod_id	trans_date	amount	cust_id
1	100	2016-09-20	265	1
2	100	2016-10-11	270	2
3	101	2016-08-17	250	2
4	102	2016-02-08	255	1
5	100	2016-06-05	290	3

To create it, first, create a PHP file, in mine data.php. Then copy-paste and run the following script (make sure there is no sales table in the database)

Note: Don’t forget to adjust MySQL host, MySQL username, MySQL password, and database name

<?php
$db_host = 'localhost'; // Host
$db_user = 'root'; // Username
$db_pass = ''; // Password
$db_name = 'tutorial'; // Database
$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
    die ('Can't connect to MySQL: ' . mysqli_connect_error());   
}
$table_name = 'sales';
$sql = 'CREATE TABLE IF NOT EXISTS `' . $table_name . '` (
          `trans_id` int(11) NOT NULL AUTO_INCREMENT,
          `prod_id` int(11) NOT NULL,
          `trans_date` date NOT NULL,
          `qty` tinyint(4) NOT NULL,
          `amount` int(11) NOT NULL,
          `cust_id` int(11) NOT NULL,
          PRIMARY KEY (`trans_id`),
          KEY `prod_id` (`prod_id`)
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1';
$query = mysqli_query($conn, $sql);
if (!$query) {
    die ('ERROR: Unable to create ' . $table_name . ' table: ' . mysqli_error($conn));
}
echo 'The ' . $table_name . ' table has been successfully created<br/>';
$sql = "INSERT INTO `$table_name` (`trans_id`, `prod_id`, `trans_date`, `qty`, `amount`, `cust_id`) 
        VALUES  (1, 100, '2016-09-20', 8, 265, 1),
                (2, 100, '2016-10-11', 3, 270, 2),
                (3, 101, '2016-08-17', 8, 250, 2),
                (4, 101, '2016-08-24', 12, 380, 2),
                (5, 101, '2016-05-10', 12, 250, 1),
                (6, 101, '2016-05-04', 11, 375, 1),
                (7, 101, '2016-07-15', 3, 265, 1),
                (8, 100, '2016-05-19', 4, 250, 1),
                (9, 101, '2016-06-17', 12, 255, 2),
                (10, 100, '2016-09-11', 12, 280, 1)";
$query = mysqli_query($conn, $sql);
if (!$query) {
    die ('ERROR: Unable to insert data to ' . $table_name . ' table: ' . mysqli_error($conn));
}
echo 'Data successfully inserted on ' . $table_name . ' table';

If successful we’ll get the following message:

III.  Retrieve Data From MySQL Database Using PHP mysqli_fetch_array()

The first function that we will use is a mysqli_fetch_array(), this function is probably the most popular because the name is familiar – contains the word array.

Code example:

<?php
$db_host = 'localhost'; // Nama Server
$db_user = 'root'; // User Server
$db_pass = ''; // Password Server
$db_name = 'tutorial_wdc'; // Nama Database
$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
    die ('Fail to connect to MySQL: ' . mysqli_connect_error());   
}
$sql = 'SELECT prod_id, trans_date, amount, qty 
        FROM sales';
$query = mysqli_query($conn, $sql);
if (!$query) {
    die ('SQL Error: ' . mysqli_error($conn));
}
echo '<table>
        <thead>
            <tr>
                <th>PRODUCT ID</th>
                <th>TRANSACTION DATE</th>
                <th>AMOUNT</th>
                <th>QUANTITY</th>
            </tr>
        </thead>
        <tbody>';
while ($row = mysqli_fetch_array($query))
{
    echo '<tr>
            <td>'.$row['prod_id'].'</td>
            <td>'.$row['trans_date'].'</td>
            <td>'.number_format($row['amount'], 0, ',', '.').'</td>
            <td class="right">'.$row['qty'].'</td>
        </tr>';
}
echo '
    </tbody>
</table>';
// Should we need to run this? read section VII
mysqli_free_result($query);
// Should we need to run this? read section VII
mysqli_close($conn);

The Result:

Short Explanation:

• On line 7, we save a connection to the database to the $conn variable.
• On Line 8, we test whether the connection succeeds (if (!$conn)) if it fails then the script will stop and display an error, otherwise, the script continued to the next line.
• On line 15, we store the query results to the $query variable.
• On line 17, we check whether the $query succeed. If fails then we stop the script and show an error message. At this step, we are free to decide whether the script is stopped or resumed by simply notice the visitor that an error have been acquired.
• At line 32, with the while loop we add a table row (<tr><td>...</td></tr>) for each generated data row, by accessing the field name of the table:

  

As we have discussed before, we use while loops to run mysqli_fetch_array() because this function only generates one data row each time it called.

To prove it, let’s try running mysqli_fetch_array() manually:

$query = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($query);
echo 'Product: ' . $row['prod_id'] . ' Transaction Date: ' . $row['trans_date'] . ' Amount: ' . $row['amount'] . '</br/>';
$row = mysqli_fetch_array($query);
echo 'Product: ' . $row['prod_id'] . ' Transaction Date: ' . $row['trans_date'] . ' Amount: ' . $row['amount'] . '</br/>';
$row = mysqli_fetch_array($query);
echo 'Product: ' . $row['prod_id'] . ' Transaction Date: ' . $row['trans_date'] . ' Amount: ' . $row['amount'] . '</br/>';

The Result:

IV. Retrieve Data From MySQL Database Using PHP mysqli_fetch_assoc()

As noted earlier, this function is similar to mysqli_fetch_array(), except that this function only generates associative array (array with key name taken form field name of the table).

Continuing the previous example, to use this function, simply change the mysqli_fetch_array() function to mysqli_fetch_assoc() like so:

while ($row = mysqli_fetch_assoc($query))
{
    echo '<tr>
            <td>'.$row['prod_id'].'</td>
            <td>'.$row['trans_date'].'</td>
            <td>'.number_format($row['amount'], 0, ',', '.').'</td>
            <td>'.$row['qty'].'</td>
        </tr>';
}
 

The Result is same as using mysql_fetch_array() function

Short Explanation:

Same as before, the difference only on line 1, we change from mysqli_fetch_array() function to mysqli_fetch_assoc()

V. Retrieve Data From MySQL Database Using PHP mysqli_fetch_row()

This function is similar to the mysqli_fetch_array() function, the difference is it only generate an indexed array (array with numeric key 0, 1, 2, 3, etc …)

The example is similar to the previous, except we change mysqli_fetch_assoc() function to mysqli_fetch_row() and we access the value based on its index not the field name of the table:

while ($row = mysqli_fetch_row($query))
{
    echo '<tr>
            <td>'.$row[0].'</td>
            <td>'.$row[1].'</td>
            <td>'.number_format($row[2], 0, ',', '.').'</td>
            <td>'.$row[3].'</td>
        </tr>';
}
 

The Result is same as using mysql_fetch_array() function

Short Explanation:

The Explanations are same as the previous example, the difference is on line 1, we use mysqli_fetch_row() and call the field  name based on its index.

The sequence of the index is taken from field name used in the SELECT statement and sorted from left to right:

VI. Using Temporary Field

Continuing the previous example,  to better understanding abut the index, let’s create a temporary field named total_byr, which is a multiplication of the price and quantity (price x quantity).

Change our query into:

<?php
$db_host = 'localhost'; // Nama Server
$db_user = 'root'; // User Server
$db_pass = ''; // Password Server
$db_name = 'tutorial'; // Nama Database
$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
    die ('Fail to connect to MySQL: ' . mysqli_connect_error());   
}
$sql = 'SELECT prod_id, trans_date, amount, qty, qty*amount AS total
        FROM sales';
$query = mysqli_query($conn, $sql);
if (!$query) {
    die ('SQL Error: ' . mysqli_error($conn));
}
echo '<table>
        <thead>
            <tr>
                <th>PRODUCT ID</th>
                <th>TRANSACTION DATE</th>
                <th>AMOUNT</th>
                <th>QUANTITY</th>
                <th>TOTAL</th>
            </tr>
        </thead>
        <tbody>';
while ($row = mysqli_fetch_array($query))
{
    echo '<tr>
            <td>'.$row['prod_id'].'</td>
            <td>'.$row['trans_date'].'</td>
            <td>'.number_format($row['amount']).'</td>
            <td>'.$row['qty'].'</td>
			<td>'.number_format($row['total']).'</td>
        </tr>';
}
echo '
    </tbody>
</table>';
// Should we need to run this? read section VII
mysqli_free_result($query);
// Should we need to run this? read section VII
mysqli_close($conn);
 
 
 

The result:

Short Explanation:

We can create a temporary field by providing  AS identifier (price*quantity AS total_byr), or without using AS (price*quantity total_byr)

I, personally, prefer to use the AS identifier because it is easier to identify that the field is a new field (temporary field)

Fields that will become the key of the array of query results are shown below:

If we use the mysqli_fetch_row() function, then the index become like this:

VII. Do We Need to Run mysqli_free_result() and mysqli_close ()?

In all of the above examples, in the last statement, we always use mysqli_free_result() and mysqli_close(), what is the use of these functions and whether we need to use it?

mysqli_free_result()

Every time we run MySQL commands that generate data (SELECT command) then PHP will save the data in computer memory (RAM), this is the same when we define a variable, where the value of the variable will be stored on a memory $variable='This text will be stored in RAM'.

Well, to erase data from this memory, for variables, simply provide a null value or the similar ($variable = ''), while for the query results we need to call the mysqli_free_result() function.

Note: PHP will always run this command at the end of each PHP script execution.

Is this function needs to be executed?

YES, if our query produces very large data, and in the rest of the PHP script we still employ a lot of code that needs some space in the memory OR we do not know what happened in the rest of the PHP script (as in developing a plugin)

NO, if the query produces a small data and we know that in the rest of PHP script we only need a small memory space.

mysqli_close ()

This function serves to close the MySQL connection, should we need to do it?

Yes, for best practice, because in some other programming languages this connection will be kept open if we do not close it manually.

No, if we want to be practical because PHP will always close this connection at the end of PHP scripts execution.

PHP is an interpreted language, which means do not relate directly to the physical computer, there is an engine which translates PHP Script into computer language, which until now using Zend Engine, a big company behind PHP.

Well, this Engine that will automatically close all connections to the database at the end of each PHP script execution.

Download the files used in this tutorial:

DOWNLOAD FILE