Read & Display From MySQL

January 16, 2012

Following up on a recent tutorial on how to create a database driven form, we’re going to review how to retrieve the data from the same database and explore the ability to sort the data. To fully understand how this will work, you’ll need to know how SQL statements are structured, and how to use and capture query strings.

Basic SQL

The most basic of SQL statements include SELECT, FROM, WHERE – and using these statements, we’ll display a list of contacts that we can later sort through. Here’s how these statements work:

SELECT – select fields
FROM – the table of the database
WHERE – a conditional statement

ORDER BY – another conditional statement that we’ll be using for sorting results

Query Strings

This is the part of the URL that contains data to be passed to a script or handler. In the context of php parameters, it would look like this:

https://www.barryko.com/products.php&cat=widgets&price=asc

You may not have noticed when you’ve shopped for things online, but this is how products are sorted and displayed. Why is this important? Because this is how we can manipulate how your data is displayed.

Putting It Together

By now, you should have an idea of the direction we’re headed. We’re going to create a “read” script, called readdata.php, that will output the data from a database, which will be sorted based on the query string we provide. So here we go:

<?php $username="your_db_username"; // database username $password="your_db_password"; // database password $database="your_db_name"; // name of the database $table="your_db_table"; // name of the database table $host="your_db_server"; // hostname or url of the database server $order = $_GET["order"]; // grab value "order" from the query string

How to handle an empty query string:

if (!isset($order)) { $order = "id"; // if parameter is empty, use "id" }

Some error handling (this is a good idea for troubleshooting):

mysql_connect($host, $username, $password) or die(mysql_error()); mysql_select_db("$database") or die(mysql_error());

The dirty work: (note how the SQL statement sorts the result set based upon the parameter)

$data = mysql_query("SELECT * FROM info ORDER BY $order") or die(mysql_error());

Display The Data

print "<table border cellpadding=3>"; print "<tr><th><a href='readdata.php?order=id'>ID</a></th><th><a href='readdata.php?order=first'>First</a></th><th><a href='readdata.php?order=last'>Last</a></th><th><a href='readdata.php?order=phone'>Phone</a></th></tr>"; while($info = mysql_fetch_array( $data )) { print "<tr><td>".$info['id'] . "</td>"; print "<td>".$info['first'] . "</td>"; print "<td>".$info['last'] . "</td>"; print "<td>".$info['phone'] . "</td></tr>"; } print "</table>"; ?>

This last chunk of code is a little messy, but here’s the lowdown – this script will output the data into a HTML table. We’ve created a loop that goes through the array and dumps each row from the database into a row of the table. Take a look at what I’ve done with the table headers. I’ve created hyperlinks that link back to the script, but with different parameters. So clicking on the respective link, I can pass that parameter back to the script and the SQL statement will sort the result set accordingly. Cool, right?Following up on a recent tutorial on how to create a database driven form, we’re going to review how to retrieve the data from the same database and explore the ability to sort the data. To fully understand how this will work, you’ll need to know how SQL statements are structured, and how to use and capture query strings.