In the last tutorial, we added a row to a simple user registration database. In this tutorial we shall be looking at updating rows using the PDO extension, a much safer and more robust method of interfacing with your MySQL database, than the standard and more common mysql extension.
This tutorial will assume the following – if you are lacking any of these then follow the links for instructions:
- A XAMPP ( or equivalent for your OS ) development stack preferred.
- A MySQL database set up.
- Understanding what PDO is and how to connect to a database using PDO.
Updating a Row
Got all of the above? Good, then let’s update our database. Let’s assume we have a table with the following row:
If we want to change, or update, this record, then the first thing we need to do is connect to the database:
$user = 'your_user'; $pass = 'your_pass'; $db = new PDO( 'mysql:host=localhost;dbname=my_database', $user, $pass );
Now we write the MySQL query to update the record, and save this in a variable. If we wanted to update the first_name column, this is the syntax we would follow:
$id_to_update = 2; $sql = "UPDATE users SET first_name = :first_name WHERE id = $id_to_update";
The syntax for MySQL, at least for simple queries such as this one, are mercifully simple. First of all we specify the table we want to update with UPDATE users, then we supply the column we want to update and the value we want to udpate it with – SET first_name = : first_name. If you don’t remember what the colon notation means here, then have a quick refresher on PDO in this previous post. Next, we specify which row we want to change. That may be obivous in this case, given there is only one row in the whole table, but lets imagine our tables will be bigger in future. We specify our row as being the one with an id of 2 – WHERE id = $id_to_update.
We then prepare our statement, before executing, remembering to define and pass in the actual values to replace the prepared variables:
$new_first_name = "Tails"; $query = $db->prepare( $sql ); $query->execute( array ( ":first_name" => $new_first_name ) );
Now, if you run your page and check your database you should see that the first_name column value has been changed to ‘Tails’.
Updating multiple Columns
Updating multiple columns is just as easy, there are only a few things you need to change.
The first thing to change is your MySQL query, separating the different columns you want to change with a comma:
$sql = "UPDATE users SET first_name, surname VALUES :first_name, :surname ";
The only other change is in the execute statement. Here we need to set the placeholders with the new values, which we do in the array that we pass to the execute function:
$another_first_name = "Paws"; $new_surname = "McClaws"; $query->execute( array ( ":first_name" => $another_first_name, ":surname" => $new_surname ) );
If you now update your code, you will see that both of the columns, first_name and surname, have been updated.
And that’s it…
Easy, eh? I hope you are starting to see how PDO works and realising that it isn’t as confusing as it may have first appeared. In the next tutorial I will be looking at deleting records, the last of the standard operations you will use with PDO.
If you would like to be kept up to date with new posts here at the web developing cat, then be sure to add your name to our mailing list below.