Displaying Data from a MySQL Database with PHP

$con = mysql_connect("localhost","jdoe","TheDatabasePassword");

E.g., in this example, the user's MySQL account is jdoe and her password is TheDatabasePassword.

Note: if you need to create a MySQL database, but are unfamiliar with SQL commands, phpMyAdmin provides a graphical interface that makes creating and manipulating databases easier. Many website hosting companies provide it with a website account.

I have a MySQL database which I use to track the installed versions of various software packages on several websites. If the database is named installed_packages, I can select that datbase using the following PHP statement, given that I've already used the PHP statement above:

mysql_select_db("installed_packages", $con);

Let's say I have just four fields in the database with the following database structure.

Name is the name of the installed package, Current is the current version, and the values that will be stored in the columns Site A and Jane's Site are the version installed for a particular package on those sites.

I can use the following PHP code to query the database and display the values in each row in a table named Versions in the database named installed_packages.

<?php
$con = mysql_connect("localhost","jdoe","TheDatabasePassword");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("installed_packages", $con);

$result = mysql_query("SELECT * FROM Versions");

echo "<table border=\"1\" cellpadding=\"3\">\n";
echo "<thead>\n";
echo "<tr>\n";
echo "<th>Name</th>";
echo "<th>Current</th>";
echo "<th>Site A</th>";
echo "<th>Jane's Site</th>";
echo "</thead>\n";
echo "<tbody>\n";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>\n";
  echo "<td>" . $row['Name'] . "</td>\n";
  echo "<td>" . $row['Current'] . "</td>";
  echo "<td>" . $row['Site A'] . "</td>";
  echo "<td>" . $row['Jane\'s Site'] . "</td>";
  echo "</tr>\n";
  }

echo "</tbody>\n";
echo "</table>\n";

mysql_close($con);

?>

The "\n" at the end of some lines, such as the one below, isn't absolutely needed, it's putting a newline or carriage return character in the output for the code generated for the webpage, so that if I view the source code it is easier for me to read.

echo "<thead>\n";

I am enclosing text to be output with the echo command in double quotes. If you do so, but wish to have a double quote appear in the output on the webpage, then you have to "esape" its meaning with an escape character, which is the backslash character, "\".

echo "<table border=\"1\" cellpadding=\"3\">\n";

So I put a \"1\" to have PHP treat the " quote characters around the 1 like any other character.

For the same reason, I need a "\" before the single quote in Jane's in the line below.

echo "<td>" . $row['Jane\'s Site'] . "</td>";

For the above code, I might see something like the following displayed for the .php page on which I include the code.

For packages that are not installed on one site, where the value stored in the field is the null value, i.e., there is nothing stored in the field, the cell in the table for that package for the site is empty.

Sorting Results

If I want the results to be displayed in alphabetical order by name, I can change the mysql_query statement to include an ORDER BY clause, e.g. ORDER BY field order, where field is the name of the field to sort on and order is DESC for descending order or ASC for ascending order..

Unsorted Results

$result = mysql_query("SELECT * FROM Versions");

Sorted in Ascending Order

$result = mysql_query("SELECT * FROM Versions ORDER BY Name ASC");

That code would display the results below for the same table as above. The Name field is sorted in ascending alphabetical order.

If I, instead, wished to have the results displayed in descending order, I could use DESC, instead of ASC

$result = mysql_query("SELECT * FROM Versions ORDER BY Name DESC");

If you want to provide the capability to interactively sort the displayed data on the web page, you can create a variable that will control how the records are sorted when they are displayed on the webpage and set that variable through a parameter that is included with the URL used to access the page.

E.g., suppose, in addition to the fields in the database listed above, I have another field "Author" and I want to be able to sort the displayed entries from the database by either the "Name" column, which contains the name of the software package or by the name of the author of the software package. Let's also suppose I have another column in the database named "URL", which contains the URL for the author's website. I could include the following PHP code for a page that displays the data from the Versions table in the installed_packages database assuming I am accessing the webpage that accesses the database at http://example.com/versions.php.

<?php

function displayPackage($package) {

   echo "<tr>\n";
   echo "<td>" . $package['Name'] . "</td>";
   echo "<td>" . $package['Current'] . "</td>";
   echo "<td>" . $package['Site A'] . "</td>";
   echo "<td>" . $package['Jane\'s Site'] . "</td>";
   echo "<td><a href=\"" . $package['URL'] . "\">" .
         $package['Author'] . "</a></td>";
   echo "</tr>\n\n";

}

$con = mysql_connect("localhost","jdoe","TheDatabasePassword");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("installed_packages", $con);

?>

<table cellpadding="3" border="1">
<thead>
<tr>
<th><a href="/versions.php?orderby=Name">Name</a></th>
<th>Current</th>
<th>Site A</th>
<th>Jane's Site</th>
<th><a href="/versions.php?orderby=Author">Author</a></th>
</tr>
</thead>
<tbody>

<?php

if(isset($_GET["orderby"])) {
   $orderby = $_GET["orderby"];
}
else {
   $orderby = "Name";
}

$packages = mysql_query("SELECT * FROM Versions ORDER BY $orderby ASC");
// Display advertiser
while($package = mysql_fetch_array($packages))
{
   displayPackage($package);
}

?>

</tbody>
</table>

For the above code, I've also moved the display for each entry in the table to a PHP function named displayPackage. When the page is displayed, clicking on either the "Name" or "Author" column header will resort the data by either name or author depending on which is clicked on. By default, the output will be sorted by the name of the software package. So, if a parameter is not specifed in the URL, the output will be sorted by name.

I could access the data sorted by the name of the software package using either http://example.com/versions.php or http://example.com/versions.php?orderby=Name. The output can be displayed sorted by the author field by using http://example.com/version.php?orderby=Author. To include the parameters, after the .php at the end of the URL, you include a question mark followed by the name of the parameter, in this case orderby, though I could have used sorted, etc., or any name that seemed relevant, then an equal sign and the value I want to submit for the parameter, e.g. Name or Author. And once the page is displayed, I can resort the displayed data by either Name or Author by clicking on the column header of either of those columns.

To make the author's name a clickable URL with the PHP code below, since the URL will be surrouned by double quotes, since those double quotes also have a meaning to PHP itself, I need to prefix the inner ones surrounding the URL with the backslash escape character.

 echo "<td><a href=\"" . $package['URL'] . "\">" .
         $package['Author'] . "</a></td>";

References:

1. PHP MySQL Select
   w3schools.com, the world's largest web development site
   educate yourself
   beginners and experts
2. PHP sort MySQL results
   PHP Server Side Scripting