Imagine that you have a MySQL table that holds the information of all the employees in your company. One of the columns in this table is called "Seniority" and it holds an integer value of how many months an employee has worked at your company. Unfortunately for you, your job is to update these numbers every month.
You may be thinking that you'll have to open up your MySQL administration tool and edit each entry by hand. That would take hours. On the other hand, you could master MySQL and have an automated script that you run each month to
get the job done for you.
In this lesson you will learn how to replace the existing data of a MySQL table with freshly supplied up-to-date data using the UPDATE MySQL query.
MySQL Update Example
Once again we will be working with the data from a previous example. Sandy has just had a birthday and she now 22 years old. Our job now is to update her age using MySQL commands like UPDATE, SET, and WHERE.
- UPDATE - Performs an update MySQL query
- SET - The new values to be placed into the table follow SET
- WHERE - Limits which rows are affected
PHP & MySQL Code:
// Connect to MySQL
// Get Sandy's record from the "example" table
$result = mysql_query("UPDATE example SET age='22' WHERE age='21'")
$result = mysql_query("SELECT * FROM example WHERE age='22'")
// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result );
echo $row['name']." - ".$row['age']. "<br />";
Sandy Smith - 22
Now it is important to note that this query would have updated ALL records that had an age of 21 to the new age of 22. In a table where Sandy is not the only entry, this may become a problem, and a more sophisticated solution would be necessary.
Download Tizag.com's MySQL Book
If you would rather download the PDF of this tutorial, check out our
MySQL eBook from the Tizag.com store.
You may also be interested in getting the PHP eBook
Found Something Wrong in this Lesson?
Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!