PHP and MySQL: The Missing Manual (2011)

Part 1. PHP and MySQL Basics

Chapter 3. MySQL and SQL: Database and Language

Where does this thing go? It’s probably one of the most common questions you ask. Where does this sugar bowl go? Where do these shoes go? Where does this new box of books go? Where do these receipts go? And since that’s such a common question, it shouldn’t surprise you that when you’re building web applications, you’ve got to ask the same thing:

Where does my information go?

The answer, at least for the kinds of web applications you’ve been building with web pages and PHP, is simple: in a database. Yes, a database is another tool to install and another language you’ll need to learn. But, as you’ll see in this chapter, it’s worth it. If you’re writing PHP code, you need a database, too.

What Is a Database?

A database is any tool that lets you store information, grab that information when needed, and organize the information you’re storing. By definition, a metal file cabinet is a type of database. You can toss things into it, pull things back out, and use files and labels to keep your papers organized.

Databases Are Persistent

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.)

UNDER THE HOOD: PERMANENT DATA IS REALLY SEMIPERMANENT

Even though databases store your information, and that storage lasts even when your computer or the database itself starts up and shuts down, your information is still not really permanent. Think about it: even if you write in ink, not pencil, you can still throw away the piece of paper you wrote on. That’s the way databases work: they store information in a form that’s harder to destroy, but that information still can be destroyed.

But databases do store information somewhere, usually on hard drives. If one of those hard drives crashes or becomes defective, your information is lost, no matter how good your database is. Additionally, threats to computers like overheating, or natural disasters, can wipe out your data by destroying the hard drives on which that data exists.

That’s why most databases offer some form of backup and replication. Backup is just creating a copy of your database, so if something goes wrong, you can restore the database from the backup, and get back all (or at least most) of the information that’s been lost.

Replication is when an entire database is duplicated, and possibly that duplicated version is running, too. So in addition to having the main database and potentially a backup, you’ve got an entirely different copy of the database running, as well. With replication, you could have an entire database go down, but all your applications keep running because they can switch to using the replicated version.

Replication is expensive, because you need another server with another copy of your database software running. Still, if you’ve got an application that’s used a lot, and earns money as long as it’s running, replication is an important way to make sure information isn’t lost in a disaster.

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.

NOTE

You’ll learn about how to use PHP to work with files on Cleaning Up Your Code with Multiple Files.

Databases Are All About Structure

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 inFigure 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.

(Good) Databases Are Relational

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

Before you can tackle this new language, though, you’ve got to install a database. As you can tell from the title of this book, you’ll be working with the MySQL database, which is one of the most common databases used in web applications. That’s because it’s easy to get, easy to install, and easy to use.

NOTE

As with most things in life, ease of use comes with some tradeoffs. There are some databases that cost a lot of money and are really complicated to use, like Oracle. But those databases typically offer features that programs like MySQL don’t: higher-end tools for maintenance, and a whole slew of professional support options that go beyond what you get with MySQL.

Don’t worry, though. Almost every single command, technique, and tool you’ll learn for working with MySQL will work with any relational database, so even if you end up at a company or in a situation where Oracle (or an IBM product, or PostgreSQL, or something else entirely) is in use, you’ll have no problems getting your PHP working with a database other than MySQL.

ALTERNATE REALITIES: OBJECTS AND RELATIONS IN DATABASES, OH MY!

For decades, the relational database has been the de facto standard for high-end applications, whether those applications are run on the Web or on an internal company network. These databases—often called RDBMS, or relational database management systems—are the best understood, and most data naturally fits into an RDBMS model. Not only that, but there are more stable and professional RDBMSes than any other type of database.

However, there are some competitors to RDBMSes these days. Most of these are object-oriented database management systems, or OODBMS for short. Although the OODBMS have been around since the 70s, it’s really just in the last 10 years that these have gained popularity.

An RDBMS stores information in tables, rows, and columns. For example, you might have a table of users that has columns for their first name, last name, and email. Anything that’s stored in the RDBMS involves some kind of mapping, so the information in your PHP script has to be mapped to particular tables and columns. You’d say, for example, the information in$_REQUEST[‘first_name’] needs to be stored in the Users table, and then in the first_name column. This mapping two-step isn’t a big deal, but it is an extra step in working with relational databases.

In an OODBMS, you’d create an object in your code, which takes the place of a table with columns. So you might create a new User object, and assign its first name the value in $_REQUEST[‘first_name’]. Then, when you want to store that user’s information, you just hand the OODBMS your entire User object. In other words, the database figures out how to deal with an object instead of needing you to tell it where individual pieces of information go.

Of course, with an OODBMS, this means you have to have lots of objects in your code, so you’re going to end up writing some code whether you’re working with an RDBMS or an OODBMS. The RDBMS model, which is what MySQL uses, is far more common in web applications than OODBMS, so it’s definitely what you want to focus on learning.

MySQL on Windows

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.

NOTE

If you have a Mac, flip to MySQL on Mac OS X.

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.

NOTE

You’re probably starting to see why most of the programmers you may have met are impatient, a bit jittery, and drink a lot of coffee. There’s a lot of waiting around when it comes to installing software, and a lot more waiting when it comes to running your programs and making sure they behave the way they’re supposed to.

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.

MySQL on Mac OS X

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.

TIP

If you’re on Windows, turn back to MySQL on Windows.

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.

Table 3-1. Fortunately, you don’t have to worry about tons of options. Macs have one choice (32-bit or 64-bit) for each processor.

NOTE

Apple is constantly updating the Macintosh hardware choices. If you can’t find your processor name in Table 3-1, visit http://support.apple.com/kb/HT3696, which usually has an updated list of processor names and whether they’re 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.

TIP

If you’re on your own machine, this password is likely the password you normally log in with. Macs with only a single user set that user up as an administrator. Otherwise, bake some cookies and use them to bribe the computer’s owner to let you turn his Mac OS X computer into a PHP and MySQL powerhouse.

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.

POWER USERS’ CLINIC: UPDATE YOUR PATH TO INCLUDE THE MYSQL PROGRAMS

It’s a bit disappointing that after you went to all the trouble of downloading MySQL and installing it on a Mac—including a handy Preferences pane—you still can’t just type mysql into a Terminal window and be off to the races. Still, if you’re not afraid of a little work, you can fix this problem yourself.

The secret to all these programs that run—and don’t run—in your Terminal is your computer’s PATH. That’s a special variable (just like the variables you create in PHP) that tells your computer where to look when you enter a command. So when you type mysql, if your PATH includes /usr/local/mysql/bin, your computer looks in that directory, sees a program called mysql, and runs it. Perfect!

But what about when your PATH doesn’t include a directory you want it to? You can update the PATH, but it involves editing a file that’s normally hidden. First, go back to Terminal and enter these two commands:

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

$ killall Finder

The first line here tells the Finder—the program that shows you directories on a Mac—to show hidden files, like the one you need to edit. The second line restarts Finder and puts this change into action. Now open up a Finder window and go to your home directory. You’ll see a bit of a weird view of your normal directory window; it probably looks something like Figure 3-17. There will be tons of files that are light gray, and seem faded, or nearly invisible. These are the files normally hidden from your view, and you may notice that most of them begin with a dot (.), which is why they’re hidden.

Scroll until you find a file called .profile, and open up that file in a text editor like Mac OS X’s TextEdit. If you’ve never worked with PATHs before, you may not have this file at all, and that’s okay, too. Just open a new TextEdit file.

You want to add two lines to this file:

MYSQL_HOME=/usr/local/mysql

export PATH=$MYSQL_HOME/bin:$PATH

If you’re creating a new file, just make these the first lines. If you already have a .profile, add these lines at the very bottom of whatever else is in the file.

The first line creates a new variable called MYSQL_HOME, and sets it to where you installed MySQL. This way, if you ever change your MySQL installation location, you simply update this variable, just as you updated the $facebook_url variable in your PHP script (Replace Characters in Text with str_replace()). The second line then sets the PATH variable to be the current PATH, but it adds the bin directory under MYSQL_HOME to the beginning of that path. The export keyword tells Mac OS X to make sure this updated PATH variable is available to all the programs on your machine.

Finally, save your file. If you’re creating a new file, be careful to name it correctly, beginning the file name with a dot (.), and make sure it doesn’t get a file extension. (If you accidentally save the file with an extension, simply remove that extension in the Finder.)

When you’re done, you should have a file in your home directory called .profile. It should be grayed out, too, because it’s hidden. Now you can open up a new Terminal, and type in mysql. You should see the MySQL command line program open right up.

Finally, before you hang up your new system-editing ninja skills, set Finder to hide all those files again:

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

$ killall Finder

You can always unhide them if you need access later.

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.

Running Your First SQL Query

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).

NOTE

If you’re already working on a web server, MySQL is probably both pre-installed and pre-started, so you’re ready to go.

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;

WARNING

Be sure you end your line with a semicolon, or you’ll get unexpected results. All your MySQL commands end with a semicolon, just like most of your PHP commands.

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.

NOTE

If your nose is bleeding from the rush of new terms, don’t worry. You’ll be working with tables, columns, and these MySQL statements over and over and over again as you build your PHP programs. You’ll have all this new MySQL lingo under control in no time.

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.

SQL Is a Language for Talking to Databases

What you’ve been doing so far is using a program called MySQL, and you’ve been talking to that program using SQL, the Structured Query Language. And you’ve already written a couple of SQL queries:

mysql> select * from user;

...

mysql> select Host, User, Password from user;

...

Both of those commands are SQL queries, or just SQL. The “Structured” in SQL comes from the idea that you’re accessing a relational database, something with a lot of structure, and you’re using a language that itself is very structured. You’ll soon see that SQL is very easy to learn, mostly because it’s very predictable. That’s why you can look at a command like this and figure out what it does:

mysql> select User, Password

from users

where first_name = 'Dirk'

and country = 'Germany';

Even though you’ve never seen the where keyword, it’s pretty obvious: this returns only the User and Password column, from the users table, where the user’s first_name field is “Dirk” and the country field is “Germany.”

FREQUENTLY ASKED QUESTION: WHY DO I NEED TO INSTALL MYSQL ON MY MACHINE?

You saw it already in the first few chapters on PHP, and now you’re about to see it again: most of your programming is meant to be run on a web server. You may pay a monthly fee for hosting to a place like kattare.com for your own domain; you may own your own server that’s connected to the Internet; or you may deploy your code to your company’s servers, housed in a room that’s kept too cold for normal human beings and requires a key card to get through the door. In all these cases, though, your code ends up somewhere other than your own desktop or laptop.

But if that’s the case—and it usually is—why go to the trouble to install PHP and MySQL on your own machine? Truth be told, you could ask a lot of PHP developers and they’d admit that they don’t even have PHP (let alone MySQL) on their own machines. They live their programming lives through telnet and ssh sessions, writing code on a machine somewhere out on the Web.

Although your code will rarely ultimately run from your own machine, there are some good reasons to install your entire development setup on your own machine. First, you’re not always in a place where you can connect to the Internet. You might be on a plane, in the back of a taxi, or lost in West Texas with nothing but an old leather-covered compass and a MacBook Pro. In all these cases, if you’ve got PHP and MySQL on your laptop, you can code away, testing your code against a real database, and never miss a beat.

Second, it’s common to write a lot of code, run it, find out you messed up something (or a lot of somethings), rewrite code, and try again, and again, and again. The same is true when you start accessing a database, too. While you could do this on the server on which your code will ultimately live, that’s a lot of time spent living through a network connection, using that machine’s resources, and potentially adding and deleting and adding data to a database. It’s much better to work on your own machine, and then at certain milestones, upload all your working code to your server.

And finally, you learn a lot by installing these programs from scratch. You get a better handle not only on the structure of your own machine, but also on how these programs work. If someone is getting a particular error, you might recognize that same error as something you got when a Windows service wasn’t running, or the MySQL instance on a Mac OS X machine didn’t have the right table permissions set up. Your computer can help you learn more about the tools you use, and that’s always a good thing.

You can run the examples in this book on your own machine and on your web server, and learn by watching what happens. If you’re working on your own machine, make sure you can get to your machine’s code with a web browser. Otherwise, upload your code every time to make sure things are working correctly.

WARNING

The pronunciation of SQL is more hotly contested than most presidential elections. Some folks say “sequel” while others insist on “S-Q-L,” saying each letter individually. While you probably want to stick with the folks around you are using—it’s tough being blue in a red state—both are perfectly fine.

You could buy a SQL book and start memorizing all the keywords, but it’s a much better idea to simply begin buildings your own tables, and learn as you go. To do that, though, you need to get connected to the database you’ll be letting all your PHP programs talk to.

Logging In to Your Web Server’s Database

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.

TIP

If you’ve never used telnet or ssh before, Google either program’s name, and you’ll find a ton of resources. You may also want to call whoever hosts your domain, and ask them how you can best access your server. Many web providers now have a graphical version of SSH you can use right from the provider’s online control panel. Most good hosting providers also have detailed online instructions to help you get logged in and started.

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.

NOTE

If running mysql doesn’t work, you might also try mysql –hostname=localhost. Some MySQL installations are configured to only answer to localhost, rather than what’s called the local socket. That adds a bit of security to a MySQL installation, but isn’t something you need to worry much about at this point. Just make sure you can get mysql running, one way or another.

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.

NOTE

You’ll almost certainly have a username other than admin or root for your domain provider’s MySQL installation. You can ask them to give that to you when you find out about telnet or ssh access. Or, if you want to try something out on your own, start with the username and password you use for logging into your web server. Be cautious, though: good database systems will have different usernames and passwords than the web servers that talk to them.

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.

USE a Database

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

WARNING

On some systems, you’re automatically set up to use your user’s database when you log in to MySQL. Still, the use command won’t give you any problems if you tell it to use the current database, so it’s always a good idea to begin your MySQL sessions with use [your-database-name].

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.

NOTE

Although you don’t have to use capital letters in MySQL for keywords like SELECT and WHERE, it creates a nice self-documenting effect. But in reality, lots of programmers get tired of all caps and just go straight for the lowercase letters.

Making Tables with CREATE

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:

§ CREATE tells MySQL you want to create a new structure in the database.

§ TABLE tells MySQL what kind of structure. In this case, you want a table.

§ users is the name of the table you’re creating.

§ The opening parenthesis—(—tells MySQL you’re about to describe the table to create, one line at a time.

§ Each line has a column name—like user_id—and a type—like int or varchar(20).

§ When you’re done describing the table, you use a closing parenthesis—)—to let MySQL know, and then end the whole enchilada with a semicolon.

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.

NOTE

MySQL is just as happy to accept integer as int. In fact, they’re identical in MySQL.

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)

FREQUENTLY ASKED QUESTION: HOW DO I FIX A TYPO?

Yes, even for PHP and MySQL wizards, typos are a problem. In fact, since programmers tend to type way too fast, typos are a real source of frustration in MySQL.

In some cases, MySQL simply gives you an error message and lets you try again:

mysql> use

ERROR:

USE must be followed by a database name

mysql>

No big deal. Other times, though, you’ll make a mistake in the middle of a command, and even worse, hit Enter:

mysql> SELECT *,

-> FROM

->

->

There’s an extraneous comma after the * in your SELECT line here. But MySQL is just giving you extra -> prompts every time you press Enter.

The problem is that in MySQL’s eyes, you haven’t ended the SQL command. So it isn’t processing your command—including your mistake—and isn’t giving you a chance to start over.

When you get into a situation like this, your best bet is to enter a semicolon (;), and then press Enter. The semicolon ends your current command—however broken that command may be—and tells MySQL to process that command. You’ll usually get an error message, but at least you’ve got control again and can make fixes.

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)

NOTE

You can also use DESC (or desc) for DESCRIBE. So DESCRIBE users; is a perfectly acceptable SQL command, too.

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.

DESIGN TIME: THE SIZE OF YOUR COLUMNS REALLY DOES MATTER

When most people are creating tables, they spend a lot of time thinking about what they want to store in their database, and very little time thinking about things like how big the maximum length of a varchar field can get. So you’ll see lots of tables that have 10 or 20 varchar(100) columns, even though those columns hold totally different pieces of information.

But it’s better to stop and think about these things when you’re designing your tables. Make your columns as long as they need to be—but not longer. Yes, it may seem safe to stick with overly long lengths, but then you’re not doing a very good job of making your database look like the information it’s going to store.

If you’re storing a first name, there’s really no way the maximum length of that first name is as long as, for example, a Facebook URL. A really, really long first name might be 15 characters (and that would be really long!). By contrast, you can barely fit www.facebook.com into 20 characters. So it makes sense for your columns to have different maximum lengths.

But column sizing is about good design, not making your database hum. Your database uses only enough space for the information it holds, so you don’t get penalized by wasted disk space or bad performance if all your varchar fields are super-long. What you do get, though, is a database that looks sloppy, and makes it appear that you didn’t spend much time thinking about your information.

Take the time to do good design now, and it will pay off later. Make your varchar columns as long as they need to be, and maybe even a little bit longer, but always remember what information will go in those columns.

Deleting Tables with DROP

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.

NOTE

On many systems, you can hit the Up arrow and you’ll get the last command you ran. Hit Up a few times, and it will cycle back through your command history. This move is a great way to quickly reuse a command you’ve already run.

INSERT a Few Rows

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", "mike@greenfieldguitars.com",

-> "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.

SELECT for the Grand Finale

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 | mike@greenfieldguitars.com |

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.

POWER USERS’ CLINIC: SQL OR MYSQL? THEY’RE NOT THE SAME

It’s one thing to know what SQL stands for, and how to install MySQL. But it’s something else altogether to know the difference between SQL and MySQL. Ask around at your local water cooler: You’d be surprised how many beginning programmers are not sure what the difference is between SQL the language, and MySQL the database program.

SQL is in fact a language. It’s something that exists separately from MySQL or any other database, like PostgreSQL or Oracle. That means that SQL can change, or be updated, without your database automatically changing. In fact, the way it usually works is that SQL gets a new keyword or instruction, and then all the database programs release new versions to support that new keyword. Of course, SQL has been around for a long time, so this sort of thing doesn’t happen a lot.

MySQL is a database program. It lets you create and work with databases, and those databases accept SQL commands. There are other database programs that don’t accept SQL, and that doesn’t make them any more or less SQL. In fact, MySQL could stop accepting SQL commands, and still be a database program (although the name wouldn’t make much sense anymore).

If you can keep the difference between SQL and MySQL in your head, you’re ahead of the game. That’s because when you work with your PHP, you’re connecting to a MySQL database, but you’re writing SQL commands and queries. As a result, you can change to another database, and almost all of your SQL will work, as long as that database accepts SQL. That’s the beauty of separating SQL from the database that you use, in this case MySQL. You can change one—moving to PostgreSQL or Oracle—without having to rewrite all your code.

Now, notice that the previous paragraph says almost all your SQL will keep working. Each database adds its own twists to how it implements the SQL standard. And most databases add some database-specific features to “add value.” (You can read that as “to sell their product over another product.”) So you can run into some problems moving from one database to another. But your understanding of SQL helps there, too. You’ll be able to diagnose any issues, and quickly solve them.

So learn SQL, use MySQL, and end up with code that works on any SQL database.