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.
You should also give your users the option of updating their list of preferred Web sites - in SQL, this is accomplished via an UPDATE statement. The example below asks for a user name and then displays the list of URLs which can be edited - this list is obtained from the database.
Once you submit the form with your modifications, another PHP script, "modify.php4", is called; this script is responsible for generating the UPDATE query and modifying the items in the database.
[list.php4]
<?php
// this script checks the username and then displays the
URLs
// for
the user as editable text boxes
// 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>
Select the sites
you'd like to modify:
<p>
<form action="modify.php4" method="post">
<table
border=1>
<tr>
<td>
<input type=text name=title1 value="<? echo $title1;
?>">
</td>
<td>
<input type=text name=url1 value="<? echo $url1; ?>">
</td>
</tr>
<tr>
<td>
<input
type=text name=title2 value="<? echo $title2; ?>">
</td>
<td>
<input
type=text name=url2 value="<? echo $url2; ?>">
</td>
</tr>
<tr>
<td>
<input
type=text name=title3 value="<? echo $title3; ?>">
</td>
<td>
<input
type=text name=url3 value="<? echo $url3; ?>">
</td>
</tr>
<tr>
<td
colspan=3 align=center>
<input type=submit value="Modify!">
</td>
</tr>
</table>
<input
type=hidden name=username value="<? echo $username;
?>">
</form>
</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
}
?>
[modify.php4]
<?php
// this script takes all the information from list.php4
// and updates
the database
// connect
$connection = mysql_connect("localhost", "test", "test")
or
die("Invalid
server or user");
// select database
mysql_select_db("php101",$connection)
or die("Invalid
database");
// generate query
$query = "update url_list set title1
= '$title1', url1 =
'$url1', title2 =
'$title2', url2 = '$url2', title3 = '$title3',
url3 =
'$url3' where uid =
'$username'";
$result = mysql_query($query,$connection)
or die("Error in
query");
?>
<html>
<head>
<basefont face=Arial>
</head>
<body>
<?php
if($result)
{
echo "<center><h3>Success!</h3><p>$username's bookmarks
have been
modified.</center>";
}
?>
</body>
</html>
