Connecting to a MySQL Database Part 4

The Finished Page

In this part we shall be creating a select.php file, which will connect to our database via the dbconnection.php file and run MySQL SELECT query, before finally outputting any data to a html table. So lets get started.

Start by opening the file select.php that we created in part 2 and enter the following code:-

<?php
    include 'dbconnection.php';
    
    $query_select = "SELECT id, business, firstName, lastName, email, telephone FROM clients_tbl";

    $results = mysqli_query($connection, $query_select);

?>

Let’s take a closer look.

  1. Firstly we create a pair of <?php ?> tags.
  2. On the next line we add the line :- include ‘dbconnection.php’; This if you remember from part 2 will establish the connection to the database.
  3. Thirdly we create a variable “$query_select”, the choice of name is entirely up to you.
  4. Then we create a SQL query “SELECT id, business, firstName, lastName, email, telephone FROM clients_tbl”; This is a standard SQL query that would work in almost any relational database management system, such as MySQL, Oracle, Postgres and many many more. The query is assigned to the $query_select variable.
  5. Now we create a variable $results and assign to it the results of a function mysqli_query($connection, $query_select).
  6. mysqli_query(). is a built in PHP function that performs a query against the database. We pass in two parameters, $connection (from our dbconnection.php file, to establish a connection to our database) and our newly created $query_select (to tell the database what we want).

Next comes the slightly complicated bit of formating your data to a readable format. Take a look at the code, and I will explain what is going on.

<table class="w3-table w3-striped">
 <tr>
  <th>ID</th>
  <th>Business</th>
  <th>First Name</th>
  <th>Last Name</th>
  <th>Email</th>
  <th>Telephone</th>
 </tr>
 
 <?php if(mysqli_num_rows($results) > 0){
   while($row = mysqli_fetch_assoc($results)) { ?>
 <tr>
  <td><?php echo $row["id"]; ?></td>
  <td><?php echo $row["business"]; ?></td>
  <td><?php echo $row["firstName"]; ?></td>
  <td><?php echo $row["lastName"]; ?></td>
  <td><?php echo $row["email"]; ?></td>
  <td><?php echo $row["telephone"]; ?></td>
 </tr>

 <?php }
   } else {
    echo "0 results";
    } ?>

</table>

<?php mysqli_close($connection); ?>
  1. We start by creating table <table></table> and give it the classes w3-table and w3-striped. you can see what these classes do here https://www.w3schools.com/w3css/w3css_tables.asp
  2. Next we add the Table Headers.
  3. Now we drop into our first bit of php. This code creates an if statement using the mysqli_num_rows(), which is a function that returns the number of rows in a result set. If this is greater than 0 ( >0) we enter a while loop. Within the while loop we create array variable called $row to hold the contents of another special php function mysqli_fetch_assoc(). This function fetches a result row as an associative array. In our case we pass in the $results(from our earlier query) as a parameter.
  4. We stay in the while loop but temporarily drop out of php to create another row <tr></tr>
  5. We now drop in out out of php creating table cells <td></td> outputting the values of our $row associative array.
  6. We the create an else statement to echo out “0 results” if there are no rows.
  7. Finally below our closing table tag </table> we close our connection to the database. <?php mysqli_close($connection); ?>

So the complete code looks like:

<?php
    include 'dbconnection.php';
    
    $query_select = "SELECT id, business, firstName, lastName, email, telephone FROM clients_tbl";

    $results = mysqli_query($connection, $query_select);
?>

<table class="w3-table w3-striped">
 <tr>
  <th>ID</th>
  <th>Business</th>
  <th>First Name</th>
  <th>Last Name</th>
  <th>Email</th>
  <th>Telephone</th>
 </tr>
 
 <?php if(mysqli_num_rows($results) > 0){
   while($row = mysqli_fetch_assoc($results)) { ?>
 <tr>
  <td><?php echo $row["id"]; ?></td>
  <td><?php echo $row["business"]; ?></td>
  <td><?php echo $row["firstName"]; ?></td>
  <td><?php echo $row["lastName"]; ?></td>
  <td><?php echo $row["email"]; ?></td>
  <td><?php echo $row["telephone"]; ?></td>
 </tr>

 <?php }
   } else {
    echo "0 results";
    } ?>

</table>

<?php mysqli_close($connection); ?>

That’s it for this section. If you upload this file and hit the show/hide all clients button you should see “0 results”, as we have not entered a data yet. That is what we will be doing next, in Part 5.


« | »