Creating A Simple Data Browser With PHP and MySQL

Creating A Simple Data Browser With PHP and MySQL – Part 1

If you have a large amount of data you want to make available on the web, a database-driven website is the solution. Using a database to store the information has a lot of advantages, going from easier data management to search capabilities.

This post will be the first of a serie of three: building a simple data browser, adding a page navigator and sorting data. These upcoming posts are aimed at PHP beginners. When I started programming in PHP a few years ago, I thought that there was a lack of good and simple tutorials.

Before we go into PHP development, start by creating a MySQL database and user. I’ve already built two tables: one will contain a list of musical artists and the other one contains a list of musical genres.

Download the SQL script to create the tables here.

The database schema goes like this:

Artist Table:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| artist_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| artist_name | varchar(255) | NO   |     |         |                |
| genre_id    | int(11)      | NO   |     |         |                |
+-------------+--------------+------+-----+---------+----------------+

Genre Table:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| genre_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| genre_desc | varchar(255) | NO   |     |         |                |
+------------+--------------+------+-----+---------+----------------+

Create A Simple HTML Document

Start by creating a simple HTML document and save it with a .php extension. This the HTML code I’ve used:

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd“>
<html xmlns=”http://www.w3.org/1999/xhtml“>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>A Simple PHP / MySQL Data Browser</title>
</head>

<body>
</body>
</html>

Establish A Connection To The MySQL Database

At the very beginning of the document, add the PHP code to connect to the database you’ve created.

<?php

$dbname = ‘your database name‘;
$dbhost = ‘localhost’;
$dbuser = ‘your database user‘;
$dbpwd = ‘your password‘;

if(mysql_connect($dbhost, $dbuser, $dbpwd))
mysql_select_db($dbname);
else
die(“Could not connect to database $dbname”);

?>

Create The Table Header

We will store the data in an HTML table to which we will add column headers. Between the and tags, add the following HTML code:

<table width=”400″ cellspacing=”0″ cellpadding=”3″ border=”0″>
<tr>
<th>Artist</th>
<th>Genre</th>
</tr>
</table>

Query The Database

Between the </tr> and </table> tags you’ve just inserted through the last step, insert the PHP code to query the database:

<?php

$sql = “SELECT artist.artist_name, genre.genre_desc ” .
”FROM artist INNER JOIN genre ON artist.genre_id = genre.genre_id ” .
”ORDER BY artist.artist_name;”;
$result = mysql_query($sql);

?>

Generate The HTML Output From The Database Results

While we iterate through the result set, we will format the output and store it in a variable named $html. We will also set a class for each table row so that we can control their background color. This code goes right after “$result = mysql_query($sql); “.

while($row = mysql_fetch_array($result))
{
  $html .= “<tr class=\”$rowclass\”>”;
  $html .= ‘<td valign=”top”>’ . $row['artist_name'] . “</td>\n”;
  $html .= ‘<td valign=”top”>’ . $row['genre_desc'] . “</td>\n”;
  $html .= “</tr>”;

  if($rowclass == “row”)
   $rowclass = “altrow”;
  else
   $rowclass = “row”;
}

Then simply echo the content of the $html variable.

echo $html;

Of course it would be best to make a function out of the PHP code that goes through the database record but let’s keep it that way for now for simplicity reasons.

Adding Some Style

To make things look nicer, add some CSS style between the <head> and </head> tags.

<style type=”text/css”>
<!–
body, td {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 11px;
}

th {
text-align: left;
background-color: #000000;
color: #FFFFFF;
font-family: Arial, Helvetica, sans-serif;
font-size: 11px;
}

tr.row td {
background-color: #FFFFFF;
}

tr.altrow td {
background-color: #CCCCCC;
}

h1 {
font-family: Arial, Helvetica, sans-serif;
font-size: 18px;
margin-bottom: 0px;
}

h2 {
font-family: Arial, Helvetica, sans-serif;
font-size: 16px;
margin-top: 0px;
}
–>
</style>