Displaying MySQL Records Using PHP

by njs12345   | filed under Web, HTML, CSS, and XML

This tutorial should do two things. In the first part, I will guide you around installing PHP and MySQL, and preparing the database we will use later. We will also install a tool for writing PHP.

Then we will write a small program to query the database and display the records in a grid.

Installing PHP and MySQL

In order to follow this tutorial, you will most likely need a server. I use a pre-packaged server called WAMP, which you can download here. This includes Apache, MySQL, PHPMyAdmin, and PHP.

1. Follow the instructions to install WAMP.
    
2. Then visit http://localhost/phpMyAdmin/ using your web browser, login using the name e_novative(password: e_novative). Enter 'members' in the 'Create new database' field and click Create.
    
3. Then click the SQL tab, and copy and paste the following into the textbox:

CREATE TABLE `members` ( `id` INT NOT NULL AUTO_INCREMENT, `name` TEXT NOT NULL , `email` TEXT NOT NULL , `website` TEXT NOT NULL , INDEX ( `id` ) ) TYPE = MYISAM ; INSERT INTO `members` ( `id` , `name` , `email` , `website` ) VALUES ( '0', 'njs12345', 'njs12345@hotmail.com', 'www.skehin.com' ); INSERT INTO `members` ( `id` , `name` , `email` , `website` ) VALUES ( '0', 'Syko', 'syko@vmkdsn.com', 'http://www.vmkdsn.com/sykopage/' ); INSERT INTO `members` ( `id` , `name` , `email` , `website` ) VALUES ( '0', 'kirupa', 'kirupa@blah.com', 'www.kirupa.com' );

[ paste the above into the textbox ]
 

3. I would also recommend using an IDE (integrated developement enviroment) to improve the ease of writing code. My pick would be PHPEdit, which can be found here.

[PHPEdit in action]

5. Now you are ready to move on to the next part of this tutorial. Next, we will start writing the PHP code required to interact with the database..
     

Now, let's continue on to the next section.

Source Files

First you should download the source files, which you can find here.
By the end of this tutorial, you should have created something like this:

Making it Work

Open up PHPEdit and open the document mysql.php. The code you will need to write or copy/paste is in the text box below. Paste it in between the first <tr> and the ending </table>.

<?php $connection = mysql_connect("localhost", "e_novative", "e_novative") or die("Error connecting to database"); mysql_select_db("members", $connection); $result = mysql_query("SELECT * FROM members ORDER BY id", $connection) or die("error querying database"); $i = 0; while($result_ar = mysql_fetch_assoc($result)){ ?> <tr <?php if($i%2 == 1){ echo "class='body2'"; }else{echo "class='body1'";}?>> <td> <?php echo $result_ar['name']; ?></td> <td> <?php echo $result_ar['email']; ?> </td> <td> <?php echo $result_ar['website']; ?> </td> </tr> <?php $i+=1; } ?>
 

Code Explanation:

$connection =
  mysql_connect("localhost", "e_novative", "e_novative") or
  die("Error connecting to database");

This code connects to the database located on localhost (i.e this computer) using the username e_novative, and the password e_novative. This is the default username/password combination that comes with WAMP. Note that this must be put in a variable, in this case, $connection. Many functions require a variable like this. The or die(); part just means "If there is an error, exit the script providing this error message:".

mysql_select_db("members",
  $connection) or die("Error selecting database");

This sets the database that the query will be used on as members, the database we created earlier. It also uses the $connection variable that holds the connection to the MySQL database.

$result = mysql_query("SELECT
  * FROM members ORDER BY id", $connection) or die("Error
  querying database");
  

This line also uses the $connection variable. This time it is querying the database using a language called SQL. That SQL statement simply means "Select everything from the table members, ordering the records by the field id". The result of this query is stored in the imaginatively named variable $result.

$i = 0;

  while($result_ar = mysql_fetch_assoc($result)){

In the variable result, there is a pointer which stores the current record being processed. The mysql_fetch_assoc function returns false if the pointer is at the end of the recordset, or true if the pointer is on a usable record. It also creates an associative array with the names of the fields being the keys, and the values of the array being the values, which in this case is stored in $result_ar. The variable $i is simply a counter variable.

?>

  < tr <?php if($i%2 == 1){

  echo "class='body2'";

  }else{

  echo "class='body1'";}?>>

We then exit out of PHP. But wait! We didn't finish off the while loop, which means that PHP echoes out what comes after once for every iteration of the while loop. The next part simply echoes the start of a table row, then chooses one CSS class depending on whether $i (our counter variable) is even or odd.

<td>
<?php echo $result_ar['name']; ?></td>
<td>
<?php echo $result_ar['email']; ?>
</td>
<td>
<?php echo $result_ar['website']; ?>
</td>
</tr>

This part makes up the rows of our data grid, by echoing three fields, name, email and website, from the associative array we defined earlier in the test for our while loop.

<?php

  $i+=1;

  }

  ?>
  

Finally, we finish the while loop, incrementing the counter variable by 1.

If you need help, feel free to post in the Server-Side/Scripting forum!

Just a final word before we wrap up. What you've seen here is freshly baked content without added preservatives, artificial intelligence, ads, and algorithm-driven doodads. A huge thank you to all of you who buy kirupa's books, became a paid subscriber, watch the videos, and/or interact on the forums.

Your support keeps this site going! 😇