mkaz.com : View Data

Web DB Tutorial : View Data from DB

Now we want to retrieve the data from the database and display them as links on the page. The SQL statement used to get data from a database is SELECT. The format is:

SELECT (columns) FROM (table)
WHERE (exclusive criteria)

For our database we want to select all columns, so a '*' is used instead of listing out each column. Also if we only want to select a specific category of links, let's say "Local Docs" is the category we want. Our SELECT SQL statement would be:

SELECT * FROM links
WHERE category = 'Local Docs'

Single quotes specify a string value in the WHERE clause, if we were using a column which was a number value no quotes would be needed. The WHERE part is optional, if you want to select everything from the database you can leave off the WHERE portion. Review the SQL Tutorial for more examples of what can be done with SELECT statements and WHERE clauses.

The script to display links out of the database starts with the usually code to initialize the database connection.

<?
$usr = "--username--";
$pwd = "--password--";
$db = "linksdb";
$host = "localhost";

$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { echo("ERROR: " . mysql_error() . "\n"); }
?>

The next part of the script is setting up and executing the SQL statement. This should look familiar to the previous pages. I set the category as a variable at the beginning so the code can be copied and pasted for other category selects and only one change is needed.

<?
$category = "Local Docs";

$sql = " SELECT * FROM links ";
$sql .= " WHERE category = '$category' ";
$rs = mysql_query($sql, $cid);
if (mysql_error()) { print "DB Error"; }

The SELECT statement returns rows of data from the database, called a "result set". We need to loop through that data and display the information we want, which will be our links. The command to grab a row is:

$row = mysql_fetch_array($rs);

This sets $row as an array holding one record from the database, with the column names as the "keys" for the array. So to retrieve the siteurl value from that array you would use:

$siteurl = $row["siteurl"];

When the mysql_fetch_array command is called next, it moves to the next data record returned by the SELECT. If there are no more rows of data the command returns false. So to loop through all rows of data we can setup a while statement with the mysql_fetch_array in it. So here is the code to loop through the data and display it to the screen:

while ($row = mysql_fetch_array($rs)) {
$siteurl = $row["siteurl"];
$sitename = $row["sitename"];

print "<dd><a href='$siteurl'>$sitename</a></dd>";
}

?>

Remember that PHP writes out HTML to the page which the browser will then render. Download the complete script for viewing data (view_data.phps)

This completes most of the web application. We can now collect links entered in from a web page, and then display these links according to the categories they have been entered in. The next page deals with managing these links after they are in the database.

Problems:
Your select statement does not return any data. You should check to see if the category specified in the WHERE clause does have links under it. For example, if there are no links with the category "Local Docs" then no records were be selected. So there would be nothing to display.

Next Page: Manage Data

Sections:

View Data