| PHP | Name Generators | Perl | CSS | Javascript | Java | MySql | How Tos | Resources |
|
Get best price on Prepaid Samsung Galaxy A01
|
|
||||||||||
|
||||||||||||
|
||||||||||||
|
||||||||||||
|
|
|
|
MySQL PHP Database Tutorial : Manage Data
Now that we have a database populated with data, we need to be able to work with that data. We have collected numerous links, but the links will inevitably get old and need to be updated. In this section of the tutorial, we update and delete data from the database. The two SQL statements that handle these actions are helpfully named UPDATE and DELETE. The structure of the UPDATE statment is:
UPDATE (table) SET
(column1) = (value), (column2) = (value), ... WHERE (column) = (value) The last line (ends with = value) does not have a comma after it. 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 structure of the DELETE statement is:
DELETE FROM (table)
WHERE (column) = (value) This will delete the complete record(s). Multiple rows can be deleted 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. Now, we look at how to edit or delete a link. First, use a SELECT statment to display the available links.
The code is the same as before:
<?php
$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 from earlier in this tutorial is displaying the links. We do not need the links "live." Instead we 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. A working form gives me a good base for new form, in this case, the edit page. The PHP code snippet to display the links on 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 downloaded here (manage.phps). Though I left the manageedit.php page as an exercise that 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. Related Links:
|
|
|
|
| privacy policy || © 1997-2016. astonishinc.com All Rights Reserved. |