Technology. Education. Art (TEA) tealearn.org

Home > Courses > Web development using PHP and MySQL > Select and Display Database record using PHP

Select and Display Database record using PHP

Subject: Web development using PHP and MySQL

Despite the simplicity and easy environment of PhpMyAdmin, it’s still not the best place to ask your clients (website owner) to view records or data. First, It may not be easy to access for a non-IT personnel; secondly it present too many database administration features which can baffle a non-IT personnel (and if care is not taken he or she may drop the entire database by mistake) and Finally for security reason, a database of a company is a key component of the organization or project that must be within the reach of only key, trained and experience IT personnel.

So we can query or retrieve some records from time to time for some routine operation or decision making.
For example we will be learning how to retrieve the records in the Feedback table and display it on a PHP file named feedback_list.php

As said earlier about isolating the connection string to a file call conn.php, the feedback_list.php page we will be creating will also include the conn.php since the page will require some database interaction.

feedback_list.php


<HTML>
<HEAD>
<TITLE> Company Feedback </TITLE>
</HEAD>
<BODY>
<b>FEEDBACK LIST</b>
<br />
<br />
<?php
//include the connection string
include("conn.php");

//query the table
$query  = "SELECT * FROM feedback";
$result = mysqli_query($conn, $query) or die(mysqli_error($conn));	

//loop through the query and print the result
while($info = mysqli_fetch_array($result)){
     $data_id = $info['id'];
     $data_name = $info['name'];
     $data_email = $info['email'];
     $data_comment = $info['comment'];
     $data_date = $info['date_submit'];

           print "   
           NEME: $data_name
           <br />
           EMAIL: $data_email
           <br />
           COMMENT: $data_comment
           <br />
           DATE POST: $data_date
           <hr>
           ";
}
?>
</BODY>
</HTML>

In the code above, we use a SELECT query statement since we are retrieving, unlike the last example where we use INSERT. After the query the result was held with this function mysqli_fetch_array($result) which enable us to loop though this array and print all the record or result of the query.

We use HTML break line and horizontal line to better structure the record displayed (See screenshot - List Display)

Tabular display
To use table for the record display. You wrap some HTML table tag with the PHP code:

feedback_table.php


<HTML>
<HEAD>
<TITLE> Company Feedback </TITLE>
</HEAD>
<BODY>
<b>FEEDBACK LIST</b>
<br />
<br />
<?php
//include the connection string
include("conn.php");

//query the table
$query  = "SELECT * FROM feedback";
$result = mysqli_query($conn, $query) or die(mysqli_error($conn));	

//the table header
print "
<table width=\"50%\" border=1>
<tr>
<td>NAME</td>
<td>EMAIL</td>
<td>COMMENT</td>
<td>DATE POST</td>
</tr>
";

//loop through the query and print the result
while($info = mysqli_fetch_array($result)){
     $data_id = $info['id'];
     $data_name = $info['name'];
     $data_email = $info['email'];
     $data_comment = $info['comment'];
     $data_date = $info['date_submit'];

           print "
           <tr>
               <td> $data_name </td>
               <td> $data_email </td>
               <td> $data_comment </td>
               <td> $data_date </td>
           </tr>
           ";
}
print "</table>";
?>
</BODY>
</HTML>