You’ve seen that PHP gives you arrays to serve as a sort of programmer’s file cabinet (The $_REQUEST Variable). So is an array a database? It fits the definition in the simplest possible sense, but it’s not going to serve your needs very long. For one thing, arrays and their contents in PHP are trashed every time your program stops and starts again. That’s not a very helpful database. You’d be better off with an old metal file cabinet.

A good database can store information for the long term. So just because your program stops running—or your entire web server has to be restarted—a database doesn’t lose your information. Imagine if every time your web server had to be shut down for an upgrade, your database lost every user’s first name, last name, and email! Do you think your users would come back to your site just to type everything in again? Not a chance.

So a good database needs to store information more permanently. In programming terms, that’s called persisting your information. In other words, if your web server goes down, or even if your database has to shut down and be restarted, the information you put in your database sticks around. (See the box below to find out how long this data really sticks around.)

If you think about it, you’re constantly working with something kind of like this on your computer: a system that stores your information long-term. It’s your hard drive and file system. All files are on your computer are address files, email messages, financial documents, what level you’ve made it to on Angry Birds, and other pieces of information. And you can shut down your computer and start it back up, or even upgrade to a new computer, and keep all your information intact.

So a file system is really a sort of database. In fact, lots of databases actually use files much the way your computer does to do the persisting of its information. So why doesn’t PHP just store information in files? After all, it has a whole set of tools for working with files, including creating, writing, and reading files. Isn’t that enough? Not really. Read on to find out why.

If you think about it, there’s something pretty clunky about your computer’s file system. Have you ever tried to remember the last time you sent an email to someone? You can’t go to that person’s address book card, because that’s not connected to your email program. And your email program may not be much help if you can’t remember the person’s exact email address.

Then, even if you actually find that email message, you may need to reference some documents related to the email. And where are those? In another folder somewhere, probably in some organizing scheme about which you’ve long forgotten.

That’s why your computer has all kinds of Search options. On Mac OS X, you can use Spotlight (see Figure 3-1) or something like QuickSilver (http://quicksilver.en.softonic.com/mac). Windows users can download Google’s Desktop Search (www.google.com/quicksearchbox, shown in Figure 3-2). These programs look for all occurrences of a certain word or topic across your entire system.

Figure 3-1. Mac OS X’s Spotlight tries to relate files in different places by their name, the folder they’re in, or their content. In other words, Spotlight seeks to determine the relationship between different files and folders.

In fact, these search programs are trying to do what databases do by nature: find and organize information. But if you’ve ever tried to make these sort of connections on your computer—whether you’re using Spotlight or Google Search or doing it by hand—you know it’s a hassle, and inconsistent at best. What you need is a better way to connect two, or three, or ten pieces of information together.

Figure 3-2. Google Desktop Search works on both Windows and Mac. It tries to index and connect files both on your machine and stored in Google Documents and Gmail. It actually builds its own database to create and remember these connections.

What a file system and your hard drive lack, a database excels in: creating relationships between different pieces of information. So you might have a person, and that person has several email addresses, phone numbers, and mailing addresses. Your address book program already handles these sorts of relationships.

But a good database goes further. An email message is related to the sender’s email address, and that email is related back to the person’s name, phone numbers, and other contact information. And of course a map with streets connects those streets with the contact’s street address. And the creator’s name in a file description relates to that person, and his email address, and his phone number…and so on.

In a lot of ways, these relations are really a giant web of connections. And a good database both creates and manages all these relationships. In fact, MySQL and the other databases you most often run into are so keyed into relations that it’s part of the name of this category of databases: relational databases. (For more information on the category of database that PHP fits into, see the box on Objects and Relations in Databases, Oh My!.)

So this means that in addition to telling a database what information you want it to store for you and your programs, you also tell the database how that information is connected to other pieces of information. You not only get to use this web of connections, but you also get to tell the database exactly how the web should be constructed. That’s a lot of power, which is why you have to learn an entirely new language to work with these relational databases.

Installing MySQL on Windows is pretty straightforward. You just need to know one thing: whether your computer is running Windows in 32-bit or a 64-bit version. You can find this out by clicking your Start Menu, right-clicking the Computer item, and then selecting Properties from the pop-up menu. You should see something like Figure 3-3.

Figure 3-3. The machine shown here is a 32-bit system, running Windows 7 Professional. What you see in this window is determined partly by the Windows version you have installed, but also by what your computer is capable of. Both 32-bit and 64-bit systems can run MySQL with no problems.

Look for the line that says “System type.” It should say either “32-bit Operating System” or “64-bit Operating System.” Remember this bit of information, as you’ll need it in just a minute.

Now visit mysql.com in your web browser. You get a page like that shown in Figure 3-4. This page has lots of introductory information about MySQL, which you can either read or skip. Click the big “Downloads (GA)” tab to get right to the software. You get a page that has information about a few different version of MySQL. You want the first one—MySQL Community Server—so click the Download link under that option.

The download page detects that you’re running Windows and gives you several installer options (see Figure 3-5). You want the version that offers you an MSI installer, and matches your system type: 32-bit or 64-bit. After you select the correct version, you’re asked to register on the MySQL website. You can skip this option, so if you’re worried that the MySQL folks might one day use your physical address to stage a government coup, you can skip straight to the download servers.

Figure 3-4. A few years back, MySQL moved from a completely open source project to a company-backed project. The database is still free, but there’s now a lot more of a professional support system behind MySQL. That’s much of what the mysqol. com website offers: professional support and documentation.

Figure 3-5. Just as with PHP, MySQL gives you lots of choices for versions and releases. Generally, the best option is the MSI Installer that matches your system. The Zip archive options aren’t packed up nearly so nicely.

Finally, you get a list of servers from which you can download MySQL. Just select the one closest to you geographically (see Figure 3-6), choose a download location on your PC, and knock back an afternoon protein bar to keep your energy up; there’s plenty of work left to do.

Figure 3-6. It’s a mark of MySQL’s geeky roots that you still have to select a server—or mirror—from which to download. This arrangement is a throw-back to the days when there were certain public servers on which most of the popular software and tools were hosted.

Once your download is complete, you end up with a file called something like mysql-5.5.13-win32.exe. Double-click this file to run the installer. The installation wizard requires you to accept a license agreement, and then lets you choose the setup type. Select Typical, and then let the installation process whir along.

You have to click through the installation of a secondary set of programs, and then the installation finishes up. You get the option to run the MySQL Server Instance Configuration Wizard (shown in Figure 3-7) when installation is complete. Take the chance to get MySQL and your PC playing nicely together.

In the configuration wizard, select the standard configuration. Then, be sure you let MySQL set itself up as a Windows service, which means that Windows can access and control MySQL directly. You should also leave the “Launch the MySQL Server automatically” checkbox turned on, so MySQL starts up whenever you start your computer. You should also turn on the checkbox to add the MySQL bin directory to your Windows path (see Figure 3-8). This option ensures that when you start up a command prompt, you can run MySQL programs.

Next, you need to enter a root password, which is basically a master password. If this were a real database running on a server at Amazon.com or Zappo’s, here’s where you’d come up with some wild, 22-character password that the smartest computer couldn’t crack. Of course, you’re just running MySQL on your machine, so something a little less intimidating is fine; try myqsl_root if you’re stumped. Finally, MySQL is ready to execute your setup. Click the Execute button and let it spin away.

Figure 3-7. MySQL is worth a pretty thick book on its own. You can tweak literally hundreds of options to make it run better, faster, and with less strain on your system. For your purposes, though, you don’t need all these complications: you just want a local database in which you can store information.

Figure 3-8. MySQL comes with several tools that let you start, stop, and interact with its databases. These are only easily available if you add the bin directory in your MySQL installation to your path. You’re a programmer now, and definitely want access to these programs.

Finally, the wizard should close, and your MySQL database is installed. If you click the Start menu, you should also see a new program available, the MySQL Command Line Client, as shown in Figure 3-9.

Figure 3-9. If you ever lose track of the MySQL command line client, you can just open up a command prompt and type mysql. This command opens up the command-line client, as long as you made sure to add the MySQL bin directory to your Windows PATH during installation of MySQL (Figure 3-8).

Open the MySQL Command Line Client, and type your super-secret password. You should get something that looks like Figure 3-10.

That’s it: if you can log into MySQL, you’ve got a running database, and you’re ready to start working with that database, and shoving information into it.

Figure 3-10. The command line program always starts by asking you your password. Password protection is important for this program, since it lets you do everything from creating and deleting structures to messing around with MySQL’s data. It’s like a direct line of access to MySQL, which is exactly what you need for testing out the PHP code you’ll start writing in this chapter.

The MySQL installation process on Mac OS X is similar to the installation on Windows. Visit www.mysql.com, and select the “Downloads (GA)” tab near the top of the page. Then select the “MySQL Community Server” link to get to the downloads. The site detects that you’re on Mac OS X and gives you options like those shown in Figure 3-11.

Scroll down and find the DMG links. These are easy-to-install versions of MySQL that provide a nice setup interface. However, you’re going to have to figure out whether you’ve got a 32-bit or 64-bit system, and that’s a multi-step process on Macs.

First, go to →About This Mac, and then click the More Info button to get a window like Figure 3-12. Look for the Processor Name line.

Figure 3-11. Like the Windows versions, MySQL for Mac gives you plenty of options from which to choose. The developers that work on MySQL tend to favor the Compressed TAR Archive options, since they give you the actual MySQL code itself. Since you’re not planning on working on the actual MySQL code, that’s a lot more than you need.

Figure 3-12. There’s no one-step process for figuring out whether your Macintosh system is 32-bit or 64-bit. That decision is based on your machine’s processor, and you have to know which processors are 32-bit and 64-bit.

Now you can compare your processor to Table 3-1, which will tell you whether your Mac is 32-bit or 64-bit.

Select the DMG download for MySQL that matches your processor. You can then register (or skip it), select a download site, and start your download.

Once the DMG is downloaded, it will open automatically. You should see several files, as shown in Figure 3-13.

Figure 3-13. Most DMGs have a single file and, if you’re lucky, some poorly written instructions. MySQL is a little more heavyweight, though, so in addition to the core installation, you get a preference pane (which you’ll install in a few minutes), a program to handle automatic startup, and a helpful ReadMe.txt file.

Select the main file, which should be named something like mysql-5.5.13-osx10.6-x86_64.pkg. Double-click this file to begin installation. You have to agree to a license and select an install location. Then you need to type in an administrator password for your machine to launch the installation.

Installation doesn’t take very long (see Figure 3-14). Don’t get too excited, though; you have a few more steps. Go back to the DMG, and if it’s not still open, double-click to reopen it (look back to Figure 3-13 if you need to).

Figure 3-14. MySQL is installed not just as a program, but at a system level. It must be able to not just write to your files, but allow access from your Mac’s command line, grab system resources, and a lot more.

Double-click the file named MySQL.prefPane. This installer adds a new control pane to your System Preferences. It also asks you whether you want to install this pane for you alone, or all users. You can probably keep the pane to yourself, unless there’s a line behind you of other database-hungry users.

Once the pane is installed, it opens automatically, as shown in Figure 3-15. Go ahead and turn on the checkbox to have MySQL start up automatically; you get to enter your password one more time. Finally, start up MySQL now to make sure things are working as they should be.

Figure 3-15. The Preferences pane is a handy feature of MySQL on Mac OS X. It lets you start and stop the database, and if you have problems, it gives you a quick place to go figure out what’s wrong: it may be as simple as your MySQL installation isn’t running.

And with that, you’ve got an installed, running database on your Mac. Now, open up a new Terminal window (go to Applications→Utilities→Terminal; if you haven’t already, go ahead and drag the Terminal icon into your dock where it’s easy to access). In the Terminal window, type the following command:

$ /usr/local/mysql/bin/mysql

This command is a bit on the long side, unfortunately. That’s because one thing the installation doesn’t do is set up your path so that you can easily call the MySQL tools and programs. (You’ll probably do most of your MySQL work on your web server, so this isn’t a huge deal, but you can make it so you can simply type the mysql part of that command; see the box on Update Your PATH to Include the MySQL Programs.)

The command you just typed opens up the MySQL command prompt tool, and you should get output like in Figure 3-16.

$ defaults write com.apple.finder Apple-ShowAllFiles TRUE
$ killall Finder

MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH

$ defaults write com.apple.finder Apple-ShowAllFiles TRUE
$ killall Finder

Figure 3-16. There are graphical tools for the Mac that let you work with your database, and you’ll want to check those out. But for getting to the root of a tricky problem, or learning how to work with MySQL from PHP, nothing beats learning the commands that you can use from a MySQL command prompt to directly interact with your database.

Figure 3-17. Most programs that update and work on your system create hidden files, all starting with a dot. So git, a version control system, creates .gitconfig, and DropBox, a popular file-sharing system, creates .dropbox.

If you’re seeing something similar on your Mac, you’ve got a running installation of MySQL, and you’re ready to start working with your database.

Make sure you’ve got MySQL installed and running. On Mac OS X, you can check your Preferences pane (as shown earlier in Figure 3-15), and on Windows, you can go to your Control Panel, click Administrative Services, and then find Local Services. Scroll down until you see MySQL, double-click it, and make sure the status is Started (see Figure 3-18).

Figure 3-18. In Windows, you can find most of the programs that interact with your system in the Local Services section of your Administrative Services control panel. You can start and stop services, look for errors, and set a service to start automatically: all of which the MySQL installation so nicely handled for you.

Fire up your MySQL command line tool, and type this command:

show databases;

You should get a text response from MySQL that looks a bit like this:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| development        |
| eiat_testbed       |
| mysql              |
| nagios             |
| ops_dashboard      |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.25 sec)

You may not have as many databases that come back, or you may have different databases. The point here is that this shows you that MySQL has a number of pre-created databases sitting on your system.

But what was that show command? Well, show does just what you might expect: it shows you everything for a particular keyword, this case, databases. So it’s just a way to ask MySQL to show you all the databases installed on your machine.

On top of that, now you know something really important: MySQL isn’t so much a database as a piece of software that can store and create databases. In this example, show databases; returns 8 rows, so there are eight databases on that system, not just one. Before you’re done, you’ll have created several more databases, all running within MySQL.

For now, tell MySQL you want to work with the mysql database, which you have on your system even if you just installed MySQL. You do that with the use command, like this:

use mysql;

Now, you’re “in” the mysql database. In other words, any commands you give to MySQL are run against just the mysql database.

You’ve already asked MySQL to show you all the databases it has; now tell it to show you all the tables in the database you’re using:

show tables;

You should get a nice long list here:

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

A lot of these table names look pretty weird, but that’s mostly because these are MySQL’s internal tables. As you create new tables and users and set up your database, all that information is stored within another database: the mysql database.

To see some of this information, you have to select the information from a specific table. You can select all the information from the user table. Enter this command at your MySQL command prompt:

mysql> select * from user;

The select command is pretty self-explanatory: it selects information from a table. The asterisk (*) says, “Select everything.” Then, from tells MySQL where to get the information you’re selecting from: in this case, user, which is a table in your database.

Don’t be surprised when you get a pretty confusing stream of information back from this command. In fact, it may look like something out of The Matrix; check out Figure 3-19 for an example.

Figure 3-19. As you get more comfortable with MySQL and PHP, you’ll learn to select just the information you want and clean up this messy response. There are also ways to format the response from MySQL, although you won’t need to worry about formatting much, since you’ll mostly be grabbing information from MySQL in a PHP script, where formatting isn’t a big deal.

The problem here isn’t in anything you typed. It’s just that you told MySQL to select everything from the user table, and in this case, everything is a lot of information. In fact, it’s so much information that it won’t all nicely fit into your command-line window, which is why you got all the strange looking lines in your response.

To tame this beast a bit, you can select just a little information from a table. You do this by replacing the * with just the column names you want, separated by commas:

mysql> select Host, User, Password from user;

You’ll get back just the three columns for which you asked:

mysql> select Host, User, Password from user;
+--------------------------+-------+-----------------------------------------
--+
| Host                     | User  | Password
|
+--------------------------+-------+-----------------------------------------
--+
| localhost                | root  | *62425DC34224DAABF6995B46CDCC63D92B03D7E9
|
+--------------------------+-------+-----------------------------------------
--+
1 row in set (0.00 sec)

This table shows that for your local machine (localhost), you have a single user named root. The password is encrypted, so don’t see anything helpful there, but you can see that MySQL definitely has an entry for you. Since you only asked for three columns, this response is a lot more readable, and actually makes a little sense.

So what’s a column? That’s a single category of information in your table. So in a table that stored users, you might have a first_name and a last_name column.

Now that you’ve got your feet dipped into the MySQL pool, it’s time to get on your web server and start to create your own tables and columns, and fill those tables and columns with your own information.

Now that you’ve got a basic lay of the MySQL landscape, it’s time to get things set up on the database your web server uses. You’ll probably need to use a tool like telnet or ssh to log in to your web server.

Once you’re logged in, you should be able to use the MySQL command-line client, mysql. Almost every hosting provider that supports PHP supports MySQL, and that means that just typing mysql is usually the way to get started.

Unfortunately, you’re likely to get an error like this right out of the gate:

bmclaugh@akila:~$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/
mysql.sock' (2)

This kind of messages usually means that MySQL isn’t installed on your server, or at least that it’s not configured correctly. But that’s probably by intention: most hosting providers will keep their MySQL installation either on a different machine, or at least make it only accessible through a different domain name, like http://mysql.kattare.com. That adds some protection, isolation, and security to the MySQL databases they host, all of which are good things.

Thankfully, having MySQL on a different server doesn’t pose a problem. You can run mysql and give it some extra information to tell it exactly where to connect. The --hostname= option lets you give mysql the hostname of your MySQL database server, and --user= lets you give mysql your own username.

Put all this together on the command line, and you get something like this:

bmclaugh@akila:~$ mysql --host=dc2-mysql-02.kattare.com
                        --user=bmclaugh --password
Enter password:

That last option, --password, tells MySQL to ask you for a password. You could put your password on the command itself, like --password=this_is_not_very_secure, but then your nosy cube-mate would be able to log in to your MySQL server.

Once you enter your password, you should get the standard MySQL welcome screen, shown in Figure 3-20.

Now you’re ready to do something with this new SQL you’ve been learning.

On most MySQL installations that hosting providers give you, you don’t have nearly as much freedom as on your own installation. For example, type in a SQL command you’ve used once before:

myqsl> show databases;

Figure 3-20. Once you’re logged into MySQL, it really doesn’t matter whether you’re on Windows, Mac OS X, or a Linux or Unix machine on a hosting provider’s network. It’s all the same: you just enter in SQL, and get back responses.

The result may be a little surprising. It’s sure not what you saw on your own machine:

myqsl> show databases;

+----------+
| Database |
+----------+
| bmclaugh |
+----------+
1 row in set (0.09 sec)

The result is limited because you’ve got limited privileges on your hosting provider’s server. The company certainly isn’t going to let you log in to its mysql system databases and see what users are in its system’s user table. What you probably do see is a single entry, a database named something like your login name. So if you log in to your system with the username “ljuber,” you might see a database named ljuber, or perhaps db-ljuber or something else similar.

In fact, you’re probably already set up within that specific database. Go ahead and tell MySQL that’s the database within which you want to work:

mysql> use bmclaugh;
Database changed

While you’re acclimating yourself to your new MySQL environment, you also want to begin to get familiar with SQL commands being in all capital letters. So if you get an email from your database buddy and she suggests you use a WHERE clause or tells you your SELECT query is goofy, she’s not actually yelling at you. She’s talking (or really, writing) SQL commands in all uppercase letters, which is pretty typical.

In fact, the commands you’ve seen are more commonly written and typed like this:

mysql> SELECT * FROM user;
...
mysql> SELECT Host, User, Password FROM user;
...
mysql> SELECT User, Password
         FROM users
        WHERE first_name = 'Dirk'
          AND country = 'Germany';

This format creates a nice clear distinction between the SQL keywords like SELECT, FROM, WHERE, and AND, and the column and table names. As you’ve guessed, though, MySQL accepts keywords in upper- or lowercase letters.

When you could get to and USE the mysql database, you had some tables all ready for you to SELECT from: the users table, for example. But now you’re on a database server where you can’t get to those tables. So before you can get back to working on your SELECT skills, you need to create a table.

As you may have already guessed, you can do that with another handy-dandy SQL keyword: CREATE. So what you need to do is create a table. You can then put data in it, get data out, and generally have all kinds of database fun.

Type this command in your MySQL command line:

CREATE TABLE users (

Be sure not to add a semicolon at the end. Then hit Enter, and you’ll see something a little weird:

mysql> CREATE TABLE users (
    ->

What’s going on? Remember, your MySQL commands should end in a semicolon. But here, you left that off. What that tells MySQL is “Hey, I’m writing a command, but I’m not done yet.” In other words, you don’t have to jam a really long line of SQL onto one line in your tool; you can split it up over several lines, and just keep hitting Enter. As long as you don’t type that semicolon, MySQL won’t try to do anything with your command. And that little arrow (->) lets you know that MySQL is waiting for you to keep typing.

So keep typing:

mysql> CREATE TABLE users (
    -> user_id int,
    -> first_name varchar(20),
    -> last_name varchar(30),
    -> email varchar(50),
    -> facebook_url varchar(100),
    -> twitter_handle varchar(20)
    -> );

Hit Enter after this last semicolon, and you get a very unimpressive response:

mysql> CREATE TABLE users (
    -> user_id int,
    -> first_name varchar(20),
    -> last_name varchar(30),
    -> email varchar(50),
    -> facebook_url varchar(100),
    -> twitter_handle varchar(20)
    -> );
Query OK, 0 rows affected (0.18 sec)

This last line is MySQL’s modest way of saying, “I did what you asked.”

You can probably tell at least a bit about what’s going on in your CREATE command:

You’ll learn a ton more about all the different types of columns you can have, but for now, there are just two to worry about: int, which is short for integer, and is just a whole number. So 1, 890, and 239402 are ints, but 1.293 and 3.1456 are not.

The next type is a little less obvious: varchar. varchar stands for variable character, and just means it holds character data—strings—of variable lengths. So a varchar(20) can hold a string of length 1 all the way up to length 20.

The upshot of all these new terms is you’ve told MySQL to create a table with several new columns, one that’s an int (user_id), and several that are varchars of various maximum lengths.

How do you know whether the CREATE command worked? See for yourself using the SHOW command:

mysql> SHOW tables;
+------------------------------------+
| Tables_in_bmclaugh                 |
+------------------------------------+
| users                              |
+------------------------------------+
1 row in set (0.06 sec)

mysql> use
ERROR:
USE must be followed by a database name
mysql>

mysql> SELECT *,
    ->   FROM
    ->
    ->

So you definitely created a table. But what’s actually in the table? To find out, you need to use a new command: DESCRIBE. Try it out on your users table:

mysql> DESCRIBE users;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| user_id        | int(11)      | YES  |     | NULL    |       |
| first_name     | varchar(20)  | YES  |     | NULL    |       |
| last_name      | varchar(30)  | YES  |     | NULL    |       |
| email          | varchar(50)  | YES  |     | NULL    |       |
| facebook_url   | varchar(100) | YES  |     | NULL    |       |
| twitter_handle | varchar(20)  | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

Now you can see that MySQL did just what you told it to: created a table called users with all the columns you specified, using the types you gave it. There’s a lot more information there, too, but you don’t need to worry about that just yet.

What goes up must come down, as the saying goes. For everything MySQL and SQL let you do, there’s a way to undo those things. You’ve created a table, but you can also delete that table. However, delete isn’t the command you want; instead, it’s DROP.

So if you decide you no longer like that users table, or you want to practice that fancy CREATE command again, you can ditch users in a simple line of SQL:

mysql> DROP TABLE users;
Query OK, 0 rows affected (0.10 sec)
Boom! It's gone.

mysql> SHOW tables;
+------------------------------------+
| Tables_in_bmclaugh                 |
+------------------------------------+
0 rows in set (0.06 sec)

How simple is that? But wait…now you have no tables again, and nothing to SELECT from. It’s back to creating tables again. Drop that CREATE statement into your MySQL tool one more time, and create the users table again.

At this point, you’ve created, and dropped, and created the users table again. But it’s still empty, and that’s no good. Time to INSERT some data.

Try entering this command into your command line tool:

mysql> INSERT INTO users
    -> VALUES (1, "Mike", "Greenfield", "[email protected]",
    -> "http://www.facebook.com/profile.php?id=699186223",
    -> "@greenfieldguitars");
Query OK, 1 row affected (0.00 sec)

What a mouthful! Still, this is another case where you can just look at this SQL and figure out what’s going on. You’re inserting information into the users table, and then you’re giving it that information, piece by piece.

You can actually trace each value and connect it to a column in your table. You might want to DESCRIBE your table again:

mysql> DESCRIBE users;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| user_id        | int(11)      | YES  |     | NULL    |       |
| first_name     | varchar(20)  | YES  |     | NULL    |       |
| last_name      | varchar(30)  | YES  |     | NULL    |       |
| email          | varchar(50)  | YES  |     | NULL    |       |
| facebook_url   | varchar(100) | YES  |     | NULL    |       |
| twitter_handle | varchar(20)  | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.29 sec)

The first value, 1, goes to user_id; the second, “Mike”, to first_name; and so on.

And that’s all there is to it: you can insert as much into your table as you want, anytime you want. There are lots of ways to fancy up INSERT, and you’ll learn about most of them as you start to work with INSERT in your PHP.

Finally, you’re back to where you can use good old SELECT. By now, that command should seem like ancient history, since you’ve used DROP and CREATE and INSERT and a few others since that first SELECT * FROM users. But now you’ve got your own users table, so try it out again:

mysql> SELECT * FROM users;
+---------+------------+------------+----------------------------+--
------------------------------------------------+-------------------
-+
| user_Id | first_name | last_name  | email                      |
facebook_url                                     | twitter_handle
  |
+---------+------------+------------+----------------------------+--
------------------------------------------------+-------------------
-+
|       1 | Mike       | Greenfield | [email protected] |
http://www.facebook.com/profile.php?id=699186223 | @greenfieldguitars
|
+---------+------------+------------+----------------------------+-
-------------------------------------------------+-----------------
---+
1 row in set (0.00 sec)

No big surprises here; you got back the row you just inserted. But, just as earlier (SQL Is a Language for Talking to Databases), this output is a bit of a mess. Too many columns make this hard to read.

To simplify things, grab just a few columns; you now how to do that:

mysql> SELECT first_name, last_name, twitter_handle FROM users;
+------------+------------+--------------------+
| first_name | last_name  | twitter_handle     |
+------------+------------+--------------------+
| Mike       | Greenfield | @greenfieldguitars |
+------------+------------+--------------------+
1 row in set (0.00 sec)

That output is a lot more readable. And once you’re writing PHP to talk to MySQL, this formatting won’t be such a problem. PHP doesn’t care about fitting everything into a nice line or two. It’s happy to take a big messy set of results and handle them without any problems.

If you like, take some time to insert a few more rows of users, and play with SELECT. If you want to get really fancy, try using a WHERE clause, like this:

mysql> SELECT facebook_url
    ->   FROM users
    ->  WHERE first_name = 'Mike';
+--------------------------------------------------+
| facebook_url                                     |
+--------------------------------------------------+
| http://www.facebook.com/profile.php?id=699186223 |
+--------------------------------------------------+
1 row in set (0.00 sec)

Don’t worry if you don’t completely understand WHERE yet. Just get a feel for it, play around, and see just how far you can get with all the SQL you’ve already picked up.