mkaz.com : Manage Data

Web DB Tutorial : Manage Data

Once we have collected data in the database the links may get old and need updating or the links just may no longer work. So we need to be able to update and/or delete the data from the database.

There are two SQL statements which handle each of these, and they are coincidently named UPDATE and DELETE.

The format for the UPDATE statement is:

UPDATE (table) SET
(column1) = (value),
(column2) = (value),
...
WHERE (column) = (value)

The last column = value line does not have a comma after it. Also it is very important to use the WHERE clause to specify which data record you want to update. If the where clause is left out it will update all records with the specified values. This is where the primary key id becomes useful, the unique primary key is used to specify which record you want to update.

The DELETE statement format is:

DELETE FROM (table)
WHERE (column) = (value)

This will delete the complete record(s), multiple rows can be delete at once depending on the WHERE clause. Again the WHERE clause is crucial, if it is left off it will delete all records from the database.

A good example of how to manage links is at any of the portal personal pages, such my.yahoo.com. These sites display the links entered in, and you then click to edit or delete the one you want. To do this you first need to use a SELECT statment to display the available links. The code is the same as before:

<?
$category = "Local Docs";

$sql = " SELECT * FROM links ";
$sql .= " WHERE category = '$category' ";
$rs = mysql_query($sql, $cid);
?>

One difference is displaying the links. We do not need the links "live" but instead want to be able to edit them by clicking either "edit" or "delete". We will need the primary unique key id of the link we wish to edit or delete. The primary key can be passed in via the URL and the GET method by adding on ?id=(number) at the end of the URL.

The delete code is shown below, I will leave the edit page as an exercise for you to do. The edit page is only slightly more complex because you need another form to display the data to edit. This can be processed the same way as the insert script. What I usually do is copy over the insert form and modify that, it is a good base for my edit page.

Code to display the links on the screen:

print "<p><table><tr><td colspan=3><b>$category</b></td></tr>\n");
while ($row = mysql_fetch_array($rs)) {
$sitename = $row["sitename"];
$id = $row["id"];

print "<tr>";
print "<td>$sitename</td>\n";
print "<td><a href='manageedit.php?id=$id'>Edit</a></td>";
print "<td><a href='manage.php?id=$id&task=del'>Delete</a></td>";
print "</tr>";
}
print "</table>";

When the "Edit" link is clicked it will go to the "manageedit.php" page, it will also pass along the id of the link to edit. This page will display the data in a form. When that form is submitted it will update the information in the form.

When the "Delete" link is clicked it will submit to the same page (manage.php) and pass along two variables. The first variable is the id of the record to delete, the second variable is "task" which is set to "del". This is done so we can catch that variable and know we are performing a delete action when we load the page. The following code, which is placed in the top of the script, shows how:

if ($task=="del") {

$sql = " DELETE FROM links ";
$sql .= " WHERE id = $id ";
$sql .= " LIMIT 1 ";
mysql_query($sql, $cid);

}

As an extra safe guard, I usually add the LIMIT 1. This restricts the SQL statement to only effect one row.

The manage script can be download here (manage.phps) and though I left the manageedit.php page as an exercise, which you should try to create first, you can download that page here. (manageedit.phps)

This is the end of the beginner web database tutorial. I plan on adding more advanced tutorials and other tips. Look around the site and check back later.

I hope you found it useful.

Web Database Development Resources
The collection of links to books and other sites which may be helpful for continuing web application development using PHP and MySQL.

Next Page: Resources

Sections:

Manage Data