Welcome to S-Design
The home of your bespoke, professional, fast-loading, interactive, database-driven web presence.
Menu
MySQL TUTORIALS


MySQL Update

Once you've got some records in your database, you might want to change, or update, them!
So how do we do that?

Update a record in a MySQL Database: <?PHP
include_once('DBConnect.php');

mysql_query("update table_name set email='$email', address='$address', phone='$phone' where name='$name' limit 1");

?>
As with all actions relating to a MySQL Databse, we have to use the mysql_query() function.
Anything between the quote marks is what we want to do.

update This tells the database that we're updating a record(s)

table_name This tells the database which table we'll find the record to update.

set email='$email', address='$address', phone='$phone' This tells the database to set which fields to what value. So, set email='$email' means set the field called email to the value of $email.
You only need to use the word set once, and then simply state each field='value' separated with a comma.
The single quotes around each value tells MySQL where each value starts and ends!
If your value is likely to include a single or double quote, make sure you escape them by adding a slash (\).
If the value of the $address variable was 'the smith's house'... mysql would insert 'the smith' and anything after the second single quote would generate an error.
PHP has a dedicated function for doing that: addslashes(). So to add slashes to the variable $address, you would use addslashes($address) which would make the value of $address 'the smith\'s house'. MySQL will understand not to interpret the middle single quote as a start or end point, and will insert the whole value into the database, and will remove the added slash in doing so... so the extra slash won't be stored!!!

where name='$name' The where clause tells MySQL which record(s) inside table_name to update. Without this, Mysql would update all records it finds!!!
In this case, we're updating the email, address & phone fields for any record where the name field matches whatever value is stored in $name.

limit 1 This tells MySQL to update only one record.
In our example, there should only be one record for each name, so the limit clause isn't really needed. However, if your database has more than one John Smith, and you only want to update the first one MySQL finds, then limit 1 would do that. However, if you had more than one John Smith and you need to update a particular one, then you might need more than one condition in the where clause, like so: where name='$name' and address='$address'.
Another advantage of the limit clause... is that it's quicker! Imagine you have a database with 1000 records, and you tell it to update the record belonging to John Smith, and let's assume there is only one. Without the limit clause, MySQL would scan the database for a record with the name of John Smith and updates it when it finds it. MySQL would then carry on looking to see if there are any other records for John Smith... and that takes time! Using limit 1 tells MySQL that as soon as it's found & updated 1 record... to stop looking and finish!

Title