MySql : View Data from Database

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 so select all columns, so a '*' is used instead of listing out each column. We also only want to select a specific category of links, let's say "Local Docs" is the cateogry we want. So 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 = $SQL . " WHERE category = '$category' ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }

The SELECT statement returns rows of data from the database. 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($retid);

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($retid)) {
$siteurl = $row["siteurl"];
$sitename = $row["sitename"];

echo ("
$sitename
\n");
}
echo ("");

?>

You can download the complete script of viewing data from clik the title of this posting