PHP Tutorial – Lesson 10 Ooops… I messed up, can I start over? – Using the UPDATE statement
We always have to make changes. In fact, I think that is one of the first things that people quote as an advantage of using computers, “it’s easy to change!”. That is right up there with “these are real time savers!”. Your mileage will vary I am sure…
Information in the database is easy to modify. Using the UPDATE statement, we can modify any record. One concept that we haven’t covered yet is that of filters. Filters are VERY important in three out of four of the big four SQL queries (INSERT, UPDATE, DELETE, SELECT). With INSERT, we simply dumped data into an existing table. There was no reason to filter it, just shove the data in and done. Now that we want to change existing data, we need to be careful to tell the database WHICH data should be changed. For instance, we could use the following UPDATE statement to give every single user in the table the same password:
UPDATE mytable SET password='guess'
After this statement, every single user would have the same password (guess). This is generally not what is inteneded. If you allow a user the ability to change their own password (a common feature in most applications) then you would want to make sure that you only change the password for the user in question. Keep this stewing in your brain for now and we will get back to how to filter what is changed.
The previous UPDATE statement is mostly complete (it is just missing the filter). Lets break that down.
Like all SQL statements, it starts with the command (UPDATE). Next it is followed by the table where the changes are going to take place (this can also be the db.table notation if you are providing the full path). Note that the UPDATE statement will NOT change the structure of the table (like adding a column), you would use a different SQL statement for that (ALTER TABLE). The UPDATE statement only changes existing row(s) of data.
After you identify the table where changes will occur, you need to specify which columns to change. You use the SET keyword to indicate that the changes follow. You only need to specify columns that will change. In this case, we are only updating the password, so we specify the password column and set it equal to our new value. As in the INSERT statement discussed in the last lesson (PHP Tutorial 9) we need to put single quotes around non-numeric values. If we want to change more than one column value at a time, we can use a comma and specify another column=value.
UPDATE mytable SET user_name='frankyr', password='abcd'
In this statement, we will change the value of the user_name column and set it to frankyr and change the value of the password column and set it to abcd. Again, without a filter, this statement will apply to ALL records in the table. That means we would all be frankyr and have to use the abcd password to log in. The good news though is that since the age column wasn’t changed, we would all retain our youth or lack thereof.
The UPDATE statement itself is fairly simple but updating a whole table all at once is only really usefull in a fraction of the time. Lets look at how to limit which records are updated.
To add a filter, you use the WHERE statement. By putting this in, we are telling the database server that we only want to update records where the following expression matches. The nice thing about the WHERE clause is that it is pretty standard and will be very similar when you learn to DELETE and SELECT data.
For the WHERE statement to work, you need an expression. This expression is evaluated against each and every record in the table. Every record that evaluates to true is updated, all that are false are not.
UPDATE mytable SET password='abcd' WHERE user_name='bobs'
In this example, we would only change the password for rows where the user_name is bobs. This is a more appropriate SQL statement to use when letting a user change their own password. The filter (WHERE clause) keeps the database server from updating every single record. As long as you only have one user named bobs, you will only update one record.
Lets say for instance that you had columns for address, city, state, and zip. After you get two million users, you realize that the zip code 91451 is correct, but when users selected this zip code, it entered the state incorrectly (it put CA instead of OR). You could use the following UPDATE statement to correct the problem on all records that were incorrect.
UPDATE mytable SET state='OR' WHERE zip_code='91451'
You could do a mass update very easily. You of course want to be carefull doing mass updates like this as you may inadvertantly change more than you intend, but it is great to have this kind of power. Imagine bragging to all your friends that you changed 1 million records today with 100 percent accuracy. Not exactly a good pickup line, but some of your tech friends would be less then impressed.
I hope you are starting to see that SQL really isn’t that bad. The only one that starts to get really ugly is the SELECT statement, and then only when you start getting into joins and subqueries. If you break it down though, its really not so bad.
Your assignment is to write some update statements to make changes to your current data. Practice this some as UPDATE statements are used all the time in your programming code and you need to have a good grasp on this before trying to put SQL statements into PHP. The hard part about PHP is generally not so much the language, but the fact that you toss in a little SQL, and a little HTML, and the water gets muddy real fast (and don’t forget about Javascript/Ajax, CSS, XML, etc.. etc.. etc..). The better you understand this one component, the easier it will be for you later.
Ray Pulsipher
Owner
Computer Magic And Software Design
