PHP Tutorial – Lesson 11 Just make it go away! – Using the DELETE statement
The next of the big four SQL statements that you need to master on this long and bumpy road is the DELETE statement. At some point, a customer will leave, or an item will permanently go out of stock or some other reason will pop up where you will need to remove data that is no longer relevant. This is where the DELETE statement comes into play.
A side note on removing old data. The Sarbanes-Oxley act imposes many new rules on the collection of financial data. This can directly effect the jobs of many overworked database administrators and programmers. It is important to realize that when you use the DELETE statement, the record is gone and will not come back. If you need to keep records of old transactions, it is good to have another method in place to hide deleted items without actually deleting them. One popular method is to have a field where you can mark the item as deleted rather then actually removing it. Another is to setup an identical table and move entries to that table rather then just release the data into the void never to be seen again. For more information on how this act can burden you in your work, check out http://www.sarbanes-oxley.com/.
On to the show…
The DELETE statement itself is very similar to the UPDATE and SELECT statements, and has certain similarities to the INSERT statement also. Here is an example of a DELETE statement.
DELETE FROM mytable
Again, our SQL statement starts with the command (DELETE) then tells the databas which table we want to remove data from. In this case, you will remove ALL records from the table. You very seldom want to actually do this. If you do want to remove all records in a table, TRUNCATE TABLE is a much faster implentation as it is super optimized to remove all data from a table (but can not be used to remove individual rows!).
What we would really prefer is to remove only certain rows. Here is an example of a DELETE statement that only removes one row.
DELETE FROM mytable WHERE user_name='bobs'
A common theme in SQL statements is that without a filter (WHERE clause) the statement applies to ALL records. It is therefore important that you make sure to always use a filter on DELETE and UPDATE statements (unless you are really really sure you don’t want one!). Your boss at amazon.com isn’t going to like to hear “oops” when you accidentally DELETE all customer records 🙂
In this last example, we specify that we will remove any and all records where the fields user_name contains the vvalue ‘bobs’. If you happen to have two users in this table with the same user name and that name is ‘bobs’, then DELETE will remove two records. This is why it is common to have an ID field in all tables. What if you didn’t want to allow two users with the same user name. How do you get rid of the correct one? With the ID field, each one should have a unique number and you can therefore supply the correct ID of the user in question.
DELETE FROM mytable WHERE id=5
Again, this will DELETE all rows that match this criteria. With an ID field that is numeric and has auto_increment set, you should be able to identify each row uniquely.
Is this looking similar to the UPDATE statement at all? It should. All the WHERE clauses follow the same logic and syntax for UPDATE, DELETE, and SELECT. You don’t need a WHERE clause for INSERT since you are adding data.
Also, notice that we don’t use the SET keyword as we aren’t changing any data, we are simply removing the data we don’t want.
Deleting without deleting…
As I mentioned earlier, the Sarbanes-Oxley act has added a lot of stress to many database administrators and programmers. How do you delete a record without deleting it? We will show one way here.
Rather than use the DELETE statement, add an extra field to your table. Call it ‘deleted’ and make it a bit field. Set the default to 0 and choose the NOT NULL option. It would be easiest to use one of your GUI front ends to do this (PHPMyAdmin or SQLYog). You could use the ALTER TABLE command to do it manually, but you will have to look up the syntax.
By setting a default of 0, all new records as well as all old records will start with the value of 0 (false). To remove a record, we don’t have to use the DELETE command, we can use the UPDATE command and change the field to equal 1 (true).
UPDATE mytable SET deleted=1 WHERE user_name='bobs'
We have just marked bobs as a deleted user. We may also want a field that will hold a Date/Time. This way we could keep track of when the user was deleted. We might even want a field to keep the user name of the person that deleted this user. This would give us the ability to audit actions taken in the system.
Now, we didn’t really DELETE the user. This is just an illusion. When we want to authenticate the user, we would want to add into the WHERE clause an expression that would only allow users who were present and who hadn’t been marked as deleted to show up. By using these WHERE clauses, we can make sure to keep up the appearance of the illusion by not showing the deleted records where they should be hidden. We will talk more about the SELECT statement in the next article and continue this scenario.
Until then, keep working with the DELETE statement and try different expressions in the WHERE clause. Understanding the statements themselves (UPDATE, DELETE, INSERT, SELECT) is easy. It is the WHERE clause that trips people up. Rember that the expressions in the WHERE clause are the same basic thing that you would write in an IF statement in PHP. In database land though, the variables are the field names in question and the expression is evaluated against each record.
Ray Pulsipher
Owner
Computer Magic And Software Design
