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");

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

Created: Saturday January 7, 2012