Building a Database-Driven Web Site Using PHP and MySQL

Modifying Stored Data

Once you've entered some data into a database table, you might like to change it at some point. Whether you're correcting a spelling mistake, or changing the date attached to a joke, such changes are made using the UPDATE command. This command contains elements of the INSERT command (for setting column values) and of the SELECT command (for picking out entries to modify). The general form of the UPDATE command is as follows:

mysql> UPDATE <tableName> SET
    -> <col_name>=<new_value>, ...
    -> WHERE <where clause>;

So, for example, if we wanted to change the date on the joke we entered above, we'd use the following command:

mysql> UPDATE Jokes SET JokeDate="1990-04-01" WHERE ID=1;

Here's where that ID column comes in handy. It allows us to easily single out a joke for changes. The WHERE clause here works just like it does in the SELECT command. The following command, for example, changes the date of all entries containing the word chicken:

mysql> UPDATE Jokes SET JokeDate="1990-04-01"
    -> WHERE JokeText LIKE "%chicken%";

Deleting Stored Data

Deleting entries in SQL is dangerously easy (if you can't tell by now, this is a recurring theme). Here's the command syntax:

mysql> DELETE FROM <tableName> WHERE <where clause>;

So to delete all chicken jokes from your table, you'd use the following query:

mysql> DELETE FROM Jokes WHERE JokeText LIKE "%chicken%";

One thing to note is that the WHERE clause is actually optional, but you should be very careful to know what you're doing if you leave it off because then the DELETE command applies to all entries in the table. The following command will empty the Jokes table in one fell swoop:

mysql> DELETE FROM Jokes;

Scary, huh?

Wrap-up

There's a lot more to the MySQL database system and the SQL language than the few basic commands we've looked at here, but these commands are by far the most commonly used. So far we've only been working with a single table. To realize the true power of a relational database engine, we'll also need to learn how to use multiple tables together to represent potentially complex relationships between database entities.

All this and more will be covered in Part Four of this series, where we'll be discussing database design principles and looking at some more advanced examples. For now, though, we've hopefully accomplished our objective of getting you comfortably interacting with MySQL using the command line interface.

In Part Three, the fun continues as we delve into the PHP server-side scripting language and learn how to use it to create dynamic Web pages. In the meantime, you can practice with MySQL by creating a decent-sized Jokes table, as it'll come in handy two weeks from now! Until then, your questions and comments are welcome in the SitePoint.com Forums.

Part 3: Getting Started with PHP

SitePoint.com is a fast growing Web Developer Community. Kevin Yank is the Editor of the SitePoint TechTimes, a fresh, technically oriented newsletter for the serious Webmaster.

Related Pages:

Building a Database-Driven Web Site Using PHP and MySQL