MySQL Update

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:

<?php
// Connect to MySQL

// Get Sandy's record from the "example" table
$result = mysql_query("UPDATE example SET age='22' WHERE age='21'") 
or die(mysql_error());  


$result = mysql_query("SELECT * FROM example WHERE age='22'") 
or die(mysql_error());  

// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result );
echo $row['name']." - ".$row['age']. "<br />";
?>

Display:

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.

Posted in: MySQL