| Home | PHP | Name Generators | Perl | CSS | Javascript | Java | devTips | Resources |
|
||||||||||
|
||||||||||
|
||||||||||
|
||||||||||
|
|
|
|
MySQL PHP Web Database Tutorial : Manage DataOnce 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
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.
(column1) = (value), (column2) = (value), ... WHERE (column) = (value) The DELETE statement format is:
DELETE FROM (table)
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.
WHERE (column) = (value) 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:
<?
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.
$category = "Local Docs"; $SQL = " SELECT * FROM links "; $SQL = $SQL . " WHERE category = '$category' "; $retid = mysql_db_query($db, $SQL, $cid); if (!$retid) { echo( mysql_error()); } ?> 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 ("<P><TABLE><TR><TD COLSPAN=3><B>$category</B></TD></TR>\n");
while ($row = mysql_fetch_array($retid)) { $sitename = $row["sitename"]; $id = $row["id"]; echo ("<TR>"); echo ("<TD>$sitename</TD>\n"); echo ("<TD><A HREF=\"manageedit.php?id=$id\">Edit</A></TD>"); echo ("<TD><A HREF=\"manage.php?id=$id&task=del\">Delete</A></TD>"); echo ("</TR>"); } echo ("</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. 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); } 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 last page of the tutorial. I hope you found it useful, please e-mail me at web@blazonry.com if you did, or if you found any mistakes, or have any suggestions for it. Tutorial Sections Related Links: |
|
|
|
| © 1997-2007. astonishinc.com All Rights Reserved. |