One of the most compelling things PHP has going for it is it support for a wide variety of databases. And this week, PHP 101 is going to take advantage of that database support to create dynamic data-driven Web sites. This primer covers different techniques to select, insert and delete records, together with some tips to track and squash bugs when building SQL-driven sites.
What you just saw was a very basic example. Our next example will query the database, return the list of users with their URL list, and display it all in a neat
<html>
<head>
</head>
<body>
<?php
// set up some variables
//
server name
$server = "localhost";
// username
$user = "test";
// password
$pass
= "test";
// database to query
$db = "php101";
// open a connection to the database
$connection
= mysql_connect($server, $user, $pass) or
die("Invalid server
or user");
//
formulate the SQL query
$query = "select * from url_list" or die("Error in query");
//
run the query on the database
$result = mysql_db_query($db, $query, $connection)
or
die("Error in query");
// display the result
echo "<table width=450 border=1
cellspacing=0
cellpadding=0>";
echo "<tr><td width=150 align=left>User</td><td
width=100
align=left>Site
#1</td><td width=100 align=left>Site #2</td><td
width=100
align=left>Site
#3</td></tr>";
// with a while loop
// this loop
will iterate as many times as there are records
while($myrow = mysql_fetch_array($result))
{
$user = $myrow["uid"];
$title1 = $myrow["title1"];
$url1 = $myrow["url1"];
$title2 = $myrow["title2"];
$url2 = $myrow["url2"];
$title3 = $myrow["title3"];
$url3 = $myrow["url3"];
echo "<tr><td width=150 align=left>$user</td><td
width=100
align=left><a href=$url1>$title1</a></td><td width=100 align=left><a
href=$url2>$title2</a></td><td
width=100 align=left><a
href=$url3>$title3</a></td></tr>";
}
// memory flush
mysql_free_result($result);
?>
</body>
</html>
As in our previous example, the script first sets up a connection to the database.
The query is formulated and the result set is returned to the browser. In this case, since there's going to be much more data than before, it's a good idea to use the mysql_fetch_array() function - this function returns the values from the database as an enumerated array, allowing you to access each element by the column name
and so on. Note our use of a "while" loop to parse and display each available
record of the result set, and of the die() function to exit the script with an error message should there be an error in the configuration information or the query string. And here's the output:
PHP 
