Build Your Own Database Driven Web Site Using PHP & MySQL, Part 2: Introducing MySQL

Viewing Stored Data

The command we use to view data stored in database tables, SELECT, is the most complicated command in the SQL language. The reason for this complexity is that the chief strength of a database is its flexibility in data retrieval. At this early point in our experience with databases we need only fairly simple lists of results, so we’ll just consider the simpler forms of the SELECT command here.

This command will list everything that’s stored in the joke table:

mysql> SELECT * FROM joke;

Read aloud, this command says “select everything from joke.” If you try this command, your results will resemble the following:

+----+--------------------------------------------------------------  
-+------------+  
| id | joketext  
| jokedate   |  
+----+--------------------------------------------------------------  
-+------------+  
|  1 | Why did the chicken cross the road? To get to the other side!  
| 2009-04-01 |  
+----+--------------------------------------------------------------  
-+------------+  
1 row in set (0.00 sec)

The results look a little disorganized because the text in the joketext column is so long that the table is too wide to fit on the screen properly. For this reason, you might want to tell MySQL to leave out the joketext column. The command for doing this is as follows:

mysql> SELECT id, jokedate FROM joke;

This time, instead of telling it to “select everything,” we told it precisely which columns we wanted to see. The results look like this:

+----+------------+  
| id | jokedate   |  
+----+------------+  
|  1 | 2009-04-01 |  
+----+------------+  
1 row in set (0.00 sec)

That’s okay, but we’d like to see at least some of the joke text? As well as being able to name specific columns that we want the SELECT command to show us, we can use functions to modify each column’s display. One function, called LEFT, lets us tell MySQL to display a column’s contents up to a specified maximum number of characters. For example, let’s say we wanted to see only the first 20 characters of the joketext column. Here’s the command we’d use:

mysql> SELECT id, LEFT(joketext, 20), jokedate FROM joke;  
+----+----------------------+------------+  
| id | LEFT(joketext, 20)   | jokedate   |  
+----+----------------------+------------+  
|  1 | Why did the chicken  | 2009-04-01 |  
+----+----------------------+------------+  
1 row in set (0.00 sec)

See how that worked? Another useful function is COUNT, which lets us count the number of results returned. If, for example, you wanted to find out how many jokes were stored in your table, you could use the following command:

mysql> SELECT COUNT(*) FROM joke;  
+----------+  
| COUNT(*) |  
+----------+  
|        1 |  
+----------+  
1 row in set (0.02 sec)

As you can see, you have just one joke in your table.

So far, all the examples have fetched all the entries in the table; however, you can limit your results to include only those database entries that have the specific attributes you want. You set these restrictions by adding what’s called a WHERE clause to the SELECT command. Consider this example:

mysql> SELECT COUNT(*) FROM joke WHERE jokedate >= "2009-01-01";

This query will count the number of jokes that have dates greater than or equal to January 1, 2009. In the case of dates, “greater than or equal to” means “on or after.” Another variation on this theme lets you search for entries that contain a certain piece of text. Check out this query:

mysql> SELECT joketext FROM joke WHERE joketext LIKE "%chicken%";

This query displays the full text of all jokes that contain the text “chicken” in their joketext column. The LIKE keyword tells MySQL that the named column must match the given pattern. In this case, the pattern we’ve used is "%chicken%". The % signs indicate that the text “chicken” may be preceded and/or followed by any string of text.

Additional conditions may also be combined in the WHERE clause to further restrict results. For example, to display knock-knock jokes from April 2009 only, you could use the following query:

mysql> SELECT joketext FROM joke WHERE  
   -> joketext LIKE "%knock%" AND  
   -> jokedate >= "2009-04-01" AND  
   -> jokedate < "2009-05-01";

Enter a few more jokes into the table and experiment with SELECT queries. A good familiarity with the SELECT command will come in handy later in this book.

You can do a lot with the SELECT command. We’ll look at some of its more advanced features later, when we need them.

Modifying Stored Data

Having entered your data into a database table, you might like to change it. Whether you want to correct a spelling mistake, or change the date attached to a joke, such alterations are made using the UPDATE command. This command contains elements of the SELECT and INSERT commands, since the command both picks out entries for modification and sets column values. The general form of the UPDATE command is as follows:

mysql> UPDATE tableName SET  
   ->   colName = newValue, …  
   -> WHERE conditions;

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

mysql> UPDATE joke SET jokedate = "2010-04-01" WHERE id = "1";

Here’s where that id column comes in handy: it enables you to single out a joke for changes easily. The WHERE clause used here works just as it did in the SELECT command. This next command, for example, changes the date of all entries that contain the word “chicken”:

mysql> UPDATE joke SET jokedate = "2010-04-01"  
   -> WHERE joketext LIKE "%chicken%";

Deleting Stored Data

Deleting entries in SQL is dangerously easy, which, if you’ve yet to notice, is a recurring theme. Here’s the command syntax:

mysql> DELETE FROM tableName WHERE conditions;

To delete all chicken jokes from your table, you’d use the following query:

mysql> DELETE FROM joke WHERE joketext LIKE "%chicken%";

Careful With That Enter Key!

Believe it or not, the WHERE clause in the DELETE command is actually optional.

Consequently, you should be very careful when typing this command! If you leave the WHERE clause out, the DELETE command will then apply to all entries in the table.

This command will empty the joke table in one fell swoop:

mysql> DELETE FROM joke;

Scary, huh?

Let PHP Do the Typing

There’s a lot more to the MySQL database server software and SQL than the handful of basic commands I’ve presented here, but these commands are by far the most commonly used.

At this stage, you might be thinking that databases seem a little cumbersome. SQL can be fairly tricky to type—its commands tend to be rather long and verbose compared to other computer languages. You’re probably already dreading the thought of typing in a complete library of jokes in the form of INSERT commands.

Don’t sweat it! As we proceed through this book, you’ll be surprised at how few SQL queries you actually type by hand. Generally, you’ll be writing PHP scripts that type your SQL for you. If you want to be able to insert a bunch of jokes into your database, for example, you’ll typically create a PHP script for adding jokes that includes the necessary INSERT query, with a placeholder for the joke text. You can then run that PHP script whenever you have jokes to add. The PHP script prompts you to enter your joke, then issues the appropriate INSERT query to your MySQL server.

For now, however, it’s important for you to gain a good feel for typing SQL by hand. It will give you a strong sense of the inner workings of MySQL databases, and will make you appreciate the work that PHP will save you all the more!

To date, we’ve only worked with a single table, but to realize the true power of a relational database, you’ll also need to learn how to use multiple tables together to represent potentially complex relationships between the items stored in your database. I’ll cover all this and more in Chapter 5, Relational Database Design, in which I’ll discuss database design principles and show off some more advanced examples.

For now, though, we’ve accomplished our objective, and you can comfortably interact with MySQL using the mysql client program. In Chapter 3: Introducing PHP, the fun continues as we delve into the PHP language, and use it to create several dynamically-generated web pages.

If you like, you can practice with MySQL a little before you move on by creating a decent-sized joke table. This knowledge will come in handy later on in Chapter 4: Publishing MySQL Data on the Web.