MySql : 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 statment 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 as my.excite.com, or 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 = $SQL . " WHERE category = '$category' ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }
?>
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 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:
echo ("

\n");
while ($row = mysql_fetch_array($retid)) {
$sitename = $row["sitename"];
$id = $row["id"];

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

$category
$sitename	Edit	Delete

");
?>

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.

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 = $SQL . " WHERE id = $id ";
mysql_db_query($db, $SQL, $cid);

}