MySQL update and delete

Last update on: 10-11-2008
update and delete are two functions that will serve you in your future applications.

Update: update function allows you to change the records of one or more columns in your table.
Delete: Like is name says, it deletes one or more records from your mysql table.

The Update function:
We will use the same test_tbl mysql table.

CREATE TABLE test_tbl (
id int NOT NULL auto_increment,
date varchar(20) NOT NULL,
name1 varchar (50) NOT NULL,
email varchar(55) NOT NULL,
PRIMARY KEY (id)
);

With the same records:

1/10-10-2008/leo/webmaster@iteachweb.net 
2/10-10-2008/sarah/sarah@sarah.com
3/10-11-2008/john/john@johndomain.com
4/10-12-2008/jack/jack@jackdomaine.com

Now we will replace leo by webmaster:

<?php
//get connected to the data base
$db = mysql_connect('host','login','password') or die 
("connexion error");
//select the data base
mysql_select_db('base name',$db) or die ("base connexion error");
//Update the name1 for the reocrd's id = 1
mysql_query("Update test_tbl Set name1 ='webmaster' where id ='1' ");
?>

Explanation: the structure of the update function is:
Update //The name of the function
test_tbl //the name of the table to update
Set //Select the column
name1 //the name of the column to change
= 'webmaster' // is equal to the new value (webmaster)
where //to choose
id // we choose here the id of the record to update
= '1'// here the id number is 1

Note: You can set several parameters for SET separated by commas, like this SET column1 = 'value1', column2 = 'value2' etc...

Move on now to the delete function:

The delete function:
With the same test_tbl mysql Table

If you want to empty a mysql table, use a query like this:
<?php
//get connected to the data base
$db = mysql_connect('host','login','password') or die 
("connexion error");
//select the data base
mysql_select_db('base name',$db) or die ("base connexion error");
//we delete all the records from the table 
mysql_query("Delete from test_tbl");
mysql_close();
?>

To delete just one record, we specify its id 
<?php
//get connected to the data base
$db = mysql_connect('host','login','password') or die 
("connexion error");
//select the data base
mysql_select_db('base name',$db) or die ("base connexion error");
//we delete the record that has the id = 1
mysql_query("Delete from test_tbl where id ='1'");
mysql_close();
?>

Explanation: The close where let you select the record you want to remove, if you do not use it like, all the records will be deleted, so handle with care!

Next, we will focus on that close where and how to use it to choose what you want to appear on the screen.

PHP and MySQL's lessons:

Introduction To PHP
Get Started With PHP
PHP Variables
PHP Variables Of Environment
PHP Conditions
PHP Looping
PHP Cookies
PHP Working With Dates
PHP Working With Arrays
PHP Working With Files
PHP Play With Strings
PHP And Forms
Send Emails With PHP
The Include Statement
Get Started With MySQL
MySQL Update And Delete
The WHERE Clause
MySQL Functions
Guestbook Script
Websites Directory Script
Multiple Pages With PHP
Create Your Forum With Php

MySQL Update And Delete

Free Newsletters

Get started

MySQL update and delete

PHP and MySQL's lessons: