In the previous chapter, we saw how MySQL can fetch data from a database quite easily. We also discovered how powerful the SQL language is, offering lots of ways to filter results (like with WHERE, ORDER BY, etc...).

All of that is great, but if all you know how to do is read from a database and not write to it, you're going to run into some problems :)

You guessed it, this chapter is a direct continuation of the last one. Once you've worked through both, you'll be able to create all sorts of PHP scripts. This time, we're going to see how to add and update data in the database. To do that, we'll look at three fundamental SQL queries: INSERT, UPDATE, and DELETE.

INSERT: Adding Data

Your goal for today: add a new entry to the video_games table we used in the previous chapter.

No no. :p

Sure, any database management interface (like PhpMyAdmin) lets you manually add entries to a table. But what we're interested in here is doing it through a PHP script using an SQL query — that way, the whole process can be automated!

First, here's a quick reminder of what the video_games table looks like:

The INSERT INTO query adds a new entry

To insert a new row, you'll need to use an SQL query like this one:

INSERT INTO video_games(ID, name, owner, console, price, max_players, comments)
VALUES('', 'BattleFront II', 'Patrick', 'PC', 55, 70, 'Star Wars game...')

Numbers like 55 and 70 don't need to be in quotes — only text strings do.

Let's break the query down:

• Start with the keywords INSERT INTO, which tell MySQL you're adding a new row.
• Then, specify the name of the table (video_games in this case), followed by a list of the column names you want to insert data into.
• Finally, use VALUES, followed by the values you want to insert — in the same order as the columns you listed.

Notice how for the first column (ID), I just put empty quotes. That's on purpose: since the ID column is set to auto-increment, MySQL will automatically assign a value. In fact, we can leave the ID out entirely:

INSERT INTO video_games(name, owner, console, price, max_players, comments)
VALUES('BattleFront II', 'Patrick', 'PC', 55, 70, 'Star Wars game...')

Even simpler! MySQL will handle the ID for us, so there's no need to include it.

Also, if you want, you can skip listing the column names entirely. This version works too (though it's less readable):

INSERT INTO video_games
VALUES('', 'BattleFront II', 'Patrick', 'PC', 55, 70, 'Star Wars game...')

But in that case, you must include a value for every column, in the exact right order — ID included.

Using it in PHP

Now let's use this SQL query inside a PHP script. Instead of using query() (which we used in the previous chapter to fetch data), this time we'll use exec(), which is meant for making changes to the database:

<?php
try
{
    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $db = new PDO('mysql:host=localhost;dbname=test', 'root', '', $pdo_options);
    
    // Add a new entry to the video_games table
    $db->exec("INSERT INTO video_games(name, owner, console, price, max_players, comments)
                VALUES('BattleFront II', 'Patrick', 'PC', 55, 70, 'Star Wars game...')");
    
    echo 'The game has been added successfully!';
}
catch(Exception $e)
{
    die('Error: '.$e->getMessage());
}
?>

What does this code do? It inserts a new row into the database for the game "BattleFront II", owned by "Patrick", available on "PC", priced at $55, and so on...

Because of all the apostrophes, this query can be a bit tricky to read and write — we have to escape each quote with a backslash (\). Plus, it always inserts the same data every time.

As we saw earlier, if you want to insert dynamic values, the best and safest way is to use prepared statements.

Inserting dynamic data using a prepared statement

If you decide to use a prepared statement (and I recommend it whenever you're inserting variables), the approach is basically the same as what we did in the previous chapter:

<?php
$req = $db->prepare('INSERT INTO video_games(name, owner, console, price, max_players, comments)
                      VALUES(:name, :owner, :console, :price, :max_players, :comments)');
$req->execute(array(
    'name' => $name,
    'owner' => $owner,
    'console' => $console,
    'price' => $price,
    'max_players' => $max_players,
    'comments' => $comments
));
echo 'The game has been added successfully!';
?>

I'm not including the PDO connection code here anymore, just to keep things short. But of course, you do need to connect to MySQL first, or the query won't work!

I've used named placeholders here for clarity. As you can see, I wrote the array over several lines — that's totally allowed and it makes things much easier to read.

Variables like $name and $owner should already be defined. Most of the time, we'll grab values from $_POST (form inputs) to insert them into the database. We'll look at a real-world example in the next hands-on project.

UPDATE: Editing Data

You just added Battlefront 2 to the database, everything went smoothly.

But... to your horror, you realize that Battlefront 2 actually supports only 32 players max (instead of 70), and on top of that, the price has dropped — you can now find it for just 10 bucks (instead of 55).

The UPDATE query lets you modify an entry

A small SQL query is all it takes to fix this. With UPDATE, you'll be able to tweak the entry that's causing trouble:

UPDATE video_games SET price = 10, max_players = 32 WHERE ID = 51

How does it work?

• First, the keyword UPDATE tells MySQL you want to change an entry.
• Then comes the name of the table: video_games.
• The keyword SET separates the table name from the list of fields you want to update.
• After that, you list the fields to change, separated by commas. Here, we're updating the "price" field, setting it to 10 (price = 10), and same with "max_players". Any fields you don't mention will remain unchanged.
• Finally, the all-important WHERE clause. This tells MySQL which entry to modify (otherwise it would change all of them!). We usually rely on the ID field to specify which row needs editing. In this example, we're assuming Battlefield got saved with ID 51.

If you want, you can use the game's name instead of the ID to select the row:

UPDATE video_games SET price = '10', max_players = '32' WHERE name = 'BattleFront II'

Breaking news! Phil just bought all of Matt's games! We need to update that right away!

No way! We're not going to waste hours editing each row one by one. Think about it for, like, 0.5 seconds and you'll figure out the SQL query that does exactly what we need.

Got it? Okay, here's the winning answer:

UPDATE video_games SET owner = 'Phil' WHERE owner = 'Matt'

Translation: In the video_games table, update all entries where the owner field is equal to Matt, and replace it with Phil.

Whether there are 1, 10, 100, or 1,000 entries, this one query is enough to update the whole table! If that's not SQL magic, I don't know what is. 😄

Using it in PHP

Just like before, in PHP we'll use exec() to run the update:

<?php
$db->exec('UPDATE video_games SET price = 10, max_players = 32 WHERE name = \'BattleFront II\'');
?>

Note: exec() returns the number of rows affected. Try storing that in a variable and displaying it, like this:

<?php
$mod = $db->exec('UPDATE video_games SET owner = \'Phil\' WHERE owner = \'Matt\'');
echo $mod . ' entries were updated!';
?>

This might show something like: 13 entries were updated!

Using a prepared statement

If you're working with variable data — for example, user input — it's best to go with a prepared statement:

<?php
$req = $db->prepare('UPDATE video_games SET price = :newprice, max_players = :new_max_players WHERE name = :name_game');
$req->execute(array(
    'newprice' => $newprice,
    'new_max_players' => $new_max_players,
    'name_game' => $name_game
));
?>

DELETE: Removing Data

And finally, here's one last query that might come in handy: DELETE. It's fast and simple to use, but a little dangerous — once data is deleted, there's no going back. So be careful!

Here's how to delete the BattleFront entry, for example:

DELETE FROM video_games WHERE name='BattleFront II'

Couldn't be easier:

• DELETE FROM: tells MySQL "delete from this table"
• video_games: the table's name
• WHERE: absolutely essential for targeting the specific row(s) you want gone

I'll let you try this one out in PHP. You can once again use exec() for a precise query, or go with a prepared statement if you're using variables.

Wrapping Up on Databases

At this point, you now know how to read, add, edit, and delete data from a database. These are the four fundamental operations — often grouped under the acronym CRUD: Create, Read, Update, Delete.

In real-world use, you'll often combine these actions — reading from and writing to the database — and that's exactly why SQL is so useful: it gives you the perfect framework for managing data.

In this chapter (and the previous one), we used PDO. But if you'd rather use MySQLi, it's 99% the same (only the function names change... barely).

Here's an example using MySQLi:

$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}
$name = "John SiteRaw";
$email = "john@siteraw.com";
$req = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$req->bind_param("ss", $name, $email);
$req->execute();
$req->close();
$mysqli->close();

Surprised that you can follow the code? That's because SQL is practically universal. Whether you use PDO or MySQLi, both rely on the same SQL language and queries.

Technically, this could be the end of your intro to SQL. But in reality, SQL has much more to offer — we'll explore more of its awesome features in the upcoming chapters!

Enjoyed this PHP & MySQL course?

If you liked this lesson, you can find the book "How to Build a Website in HTML and CSS" from the same authors, available on SiteRaw, in bookstores and in online libraries in either digital or paperback format. You will find a complete PHP & MySQL workshop with many exclusive bonus chapters.

More information