Subscribe via email

Grab our RSS Feed

MySQL update and delete

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.

Filed under PHP and MySQL. You can follow any responses to this entry through the RSS 2.0. You can leave a response by filling following comment form or trackback to this entry from your site

Leave a Reply

Name (required)

Mail (will not be published) (required)

(Maximum characters: 250)
You have characters left.