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.
Now that you've got the basics straight, let's evolve the application a little further. Our next example will ask the user to enter a user name. If it's a valid user name, the script will connect to the database and display the corresponding list of URLs; if not, it'll return an error.
We'll be using a single page for the entire operation - the $submit variable [you remember this technique, don't you?] is used to decide whether to display the initial form or the result page. Take a look:
<?php
// check if the form has been submitted
if($submit)
{
// initialize
database connection
$conn = mysql_connect("localhost", "test", "test");
mysql_select_db("php101",
$conn);
$result = mysql_query("select
title1,url1,title2,url2,title3,url3 from
url_list
where uid = '$username'", $conn);
// check if the user name is valid
$num_rows
= mysql_num_rows($result);
// if no rows are returned then the username is invalid
if(!$num_rows)
{
?>
<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>
<font
size="3">User name not found!</font><br>
<a href="list.php4">Click here
to try again.</a>
</center>
</body>
</html>
<?php
}
else
{
// else if user name is valid
// display list
$result = mysql_query("select
title1,url1,title2,url2,title3,url3
from
url_list where uid = '$username'", $conn);
list($title1,$url1,$title2,$url2,$title3,$url3)
=
mysql_fetch_row($result);
?>
<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>
<font
size="3">Welcome, <?php echo $username; ?>!</font><br>
Pick your destination:
<p>
<?php
echo "<a href=$url1>$title1</a>"; ?>
<?php echo "<a href=$url2>$title2</a>";
?>
<?php echo "<a href=$url3>$title3</a>"; ?>
</center>
</body>
</html>
<?php
mysql_free_result($result);
}
}
else
{
//
$submit not found
// so display a form
?>
<html>
<head>
<basefont face="Arial">
</head>
<body>
<form
action="list.php4" method="POST">
<table>
<tr>
<td>
Username:
</td>
<td>
<input
type="text" name="username" length=10 maxlength="30">
</td>
</tr>
<tr>
<td
colspan="2" align="center">
<input type="submit" name="submit" value="Log in">
</td>
</tr>
</table>
</form>
</body>
</html>
<?php
}
?>
Log in as "bill" or "john" and watch as PHP connects to the database and retrieves
that user's list of sites.
If you look closely, you'll see that the script above actually contains three HTML pages embedded within it, one for each of the three possible scenarios. The first time the user visits the page, the variable $submit will not be set, and so a simple HTML form is displayed; this forms asks for a user name, which is stored in the variable $username.
Once the form has been submitted, the same page is called again; however, this time around, the $submit variable will be found and so PHP will initiate a connection to the database to check if the user name is valid.
One simple technique that can be used for simple "yes/no" type queries [such as the one above] is the mysql_num_rows() function; this function tells you the number of result rows returned by the query. A value of zero implies that no rows were found, which in turn implies that the user was not found.
The result of mysql_num_rows() is stored in the variable $num_rows. If this variable exists, it implies that the user name is valid, and it's then possible to move ahead and fetch the Web addresses corresponding to that user name from the database. These addresses are then formatted and displayed on the page as active hyperlinks.
