mulyadi

MySQL Update and Delete

There are no special ways in PHP to perform update and delete on MySQL database. You still use mysql_query() to execute the UPDATE or DELETE statement.

For instance to update a password in mysql table for username phpcake can be done by executing an UPDATE statement with mysql_query() like this:

Example : update.php
Source code : update.phps

There is one important thing that you should be aware of when updating and deleting rows from database. That is data integrity.

If you’re using InnoDB tables you can leave the work of maintaining data integrity to MySQL . However when you’re using other kind of tables you need to enforce the data integrity manually.

To make sure that your update and delete queries will not break the data integrity. You have to make appropriate update and delete queries for all tables referencing to the table you update or delete.

For example, suppose you have two tables, Class and Student. The Student table have a foreign key column, cid which references to the class_id column in table Class. When you want to update a class_id in Class table you will also need to update the cid column in Student table to maintain data integrity.

Suppose i want to change the class_id of Karate from 3 to 10. Since there is a row in Student table with cid value of 3, I have to update that row too.

$query = “UPDATE Class SET class_id = 10 WHERE class_id = 3″;
mysql_query($query);

$query = “UPDATE Student SET cid = 10 WHERE cid = 3″;
mysql_query($query);

Below are the data in Table and Student class before an update query :
Table Class
class_id class_name
1

Silat
2 Kungfu
3 Karate
4 Taekwondo

Table Student
student_id student_name cid
1 Uzumaki Naruto 1
2 Uchiha Sasuke 3
3 Haruno Sakura 2

Now the content of Table and Student class after the update query are :
Table Class
class_id class_name
1

Silat
2 Kungfu
10 Karate
4 Taekwondo

Table Student
student_id student_name cid
1 Uzumaki Naruto 1
2 Uchiha Sasuke 10
3 Haruno Sakura 2

You can go as far as creating your own functions in PHP to ensure the data integrity. I have done this before and I hope you don’t do it. Save yourself the headache and just write appropriate queries to maintain your data integrity whenever you update / delete rows from a table.

This means that whenever you make a query to update / delete always consult your database design to see if you need to update / delete another table to maintain data integrity. Your code will be more portable like this.

Using LOCK TABLES

When your web application is used by more than one user using LOCK TABLES before any update / delete query is a safe bet. This will make sure that only one user change the table at a time.

Using the above update code examples again, suppose there are two users. The first one want to update one row in Class table and the second want to delete it

$query = “LOCK TABLES Class WRITE, Student WRITE”;
mysql_query($query);

$query = “DELETE FROM Class WHERE class_id = 3″;
mysql_query($query);

$query = “DELETE FROM Student WHERE class_id = 3″;
mysql_query($query);

$query = “UNLOCK TABLES”;
mysql_query($query);

The update queries above can be rewritten as :

$query = “LOCK TABLES Class WRITE, Student WRITE”;
mysql_query($query);

$query = “UPDATE Class SET class_id = 10 WHERE class_id = 3″;
mysql_query($query);

$query = “UPDATE Student SET cid = 10 WHERE cid = 3″;
mysql_query($query);

$query = “UNLOCK TABLES”;
mysql_query($query);

By issuing the LOCK TABLES all other users are blocked from reading and writing to the tables. So you’re update / delete query will continue to completion without any worries that the intended table already changed by another user