web blazonry web development scripts and tutorials Get best price on Unlocked Moto G Stylus
   PHP       Name Generators       Perl       CSS       Javascript       Java       MySql       How Tos       Resources   

MySQL Home

MySQL Tutorial
  Getting Started
  Create Database
  Insert Data
  View Data
  Manage Data

Bookmark and Share





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:

Tutorial Sections

 

Newest Pages
Test Google Ads Ver. 2
Free Linux Admin Books
Free Linux Books for Programmers
Free Books for Linux on the Desktop
Free PHP Books
Free JavaScript Books
Free Java Books - Advanced
Free Java Books - Basic
Free Perl Books
Free Python Books
Quote of the Day (PHP)
Debugging Part 2
How to Test Google Ads
Most Popular Pages
Baby Name Generator
U.S. Name Generator
Wu Name Generator
Popup Windows (JavaScript)
Upload and Resize an Image (PHP)
How To Install Apache + PHP + MySQL
Intro to Web Databases (PHP, MySQL)

Least Popular Pages
iNews Applet (Java)
Java Resources
Site Monitor (Perl)
PHP Resources
 
 

  privacy policy     ||     © 1997-2016. astonishinc.com   All Rights Reserved.