PHP & MySQL: The Missing Manual, 2nd Edition

Chapter 4. MySQL and SQL: Database and Language

Where is this supposed to go?

It’s a question you ask every day. Where do these shoes go? Where does this new box of books go? Where do these receipts go? Because that’s such a common question, it shouldn’t surprise you too much that when you’re building web applications, you need to ask the same thing:

Where does my information go?

For the kinds of web applications you’ve been building with web pages and PHP, the answer to this question is simple: in a database. But what do you get out of a database that makes it worth the effort of installing another tool and learning another language? And, why does everyone seem to agree that if you’re writing PHP code, you need a database, too? Sit tight, because this chapter is about to reveal all.

What Is a Database?

A database is just a repository in which you can store information, add some layer of organization to the stored information, and grab that information when it’s needed. In a literal sense, a file cabinet is a database. You can throw things into it, pull those things back out, and even use files and labels to keep your files organized.

Databases Are Persistent

You’ve already seen that PHP gives you arrays (The $_REQUEST Variable Is an Array) that serve as a sort of programmer’s file cabinet. An array might function as a database in a simplistic sense, but it won’t serve your needs for long. For one thing, arrays and their contents in PHP are lost every time your program stops and starts again. That’s not very helpful.

A good database provides long-term storage for your information. If 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 address. Do you think your users would come back to your site if they repeatedly had to type everything in again? Not a chance.

Therefore, a good database needs to store information more permanently. In programmer jargon, this is called persisting your information. (At times, though, even permanent information can be lost; see the box that follows for advice on backing up your information in such cases.)

UNDER THE HOOD: Permanent Data Is Really Semi-Permanent

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

At some point, databases have to 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 it exists.

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

Replication is when an entire database is duplicated (the duplicated version might be running simultaneously). This means that in addition to having the main database and possibly a backup, you have an entirely different copy of the database running, as well. With replication, an entire database could fail, but all your applications keep running because they can switch to the replicated version.

Replication is expensive because you basically need another server with another copy of your database software running. Still, if you use an application extensively and it earns money as long as it’s running, replication is a really important way to ensure that information isn’t lost in a disaster.

If you think about it, you’re constantly working with something like this on your computer: a system that stores your information over a long term. It’s your hard drive and file system. The files on your computer are basically pieces of information; such as addresses, emails, your finances, or maybe what level you’ve made it to in Angry Birds. You can shut down your computer and start it back up, or even upgrade to a new computer, and all those files with all your information will still load up.

In other words, a file system is really a sort of database. In fact, lots of databases actually use files much like your computer does to persist information. So, why doesn’t PHP just store information in files? It actually has an entire set of tools for working with files, including creating, writing, and reading files. Isn’t that enough?

Note

You learn about how to use PHP to work with files in Chapter 5.

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? Your email program might not know that person’s email address. And, if you go to that person’s card in your address book program, that address book program might not be connected to your email program.

Even if you actually find the email address, you might need to reference some documents related to the email message. Where are those documents? In another folder somewhere, probably in some highly-organized structure about which you’ve long forgotten.

That’s why your computer gives you one or more ways to search for information. In Mac OS X, you can use Spotlight (see Figure 4-1) or a program like Quicksilver (http://quicksilver.en.softonic.com/mac). If you have Windows, you can download Google’s Desktop Search (http://www.google.com/quicksearchbox, see Figure 4-2). These programs find all occurrences of a certain word or topic across your entire system.

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

In fact, these search programs attempt to do what databases do by nature: locate and organize information. 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, three, or ten pieces of information together.

Figure 4-2. Google Desktop Search works in both Windows and Mac OS. It indexes and connects files on both your computer and in the cloud in Google Documents and Gmail. It actually builds its own database to make and remember these connections.

Good Databases Are Relational

There is one task for which a file system and your hard drive are lacking, but a database excels: creating relationships between different pieces of information. For example, you might have a person, and that person has several email addresses, phone numbers, and mailing addresses. This isn’t anything new; your address book program already handles these sorts of relationships.

But a good database goes further. An email message is related to the email address of the sender, and that email is related back to the person’s name and phone numbers and mailing address. A map with streets connects those streets to the streets used in a person’s mailing address. The creator’s name in a file description relates to that person, and their email, and their phone, and so on.

In a lot of ways, these relationships are really a giant web of connections. A good database both creates and manages all these relationships. In fact, relationships are so integral to MySQL, FileMaker, Oracle, and other big-time databases that they’re called relational databases. (For a technical journey into how these databases operate, see the box on Objects and Relations in Databases, Oh My!.)

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

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 run on the Web or on an internal company network. These database programs—often called an RDBMS, which stands for relational database management system—are the best understood, and most data naturally fits into an RDBMS model. Not only that, but there are more stable and professional RDBMS’s than any other type of database.

However, there are some competitors to the RDBMS model these days. Most of these are object-oriented database management systems, or OODBMS for short. Although the OODBMS has been around since the 1970s, it’s really just in the past 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. This means that anything that’s stored in the RDBMS involves some kind of mapping. This means that 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 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. (You don’t need to worry about the details of objects and how they work for now.) So, you might create a new User object, and assign it’s 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 you specifying 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. Still, the RDBMS is far, far more common in web applications than the OODBMS, which is why it’s definitely the one you want to focus on learning.

Installing MySQL

Before you can tackle the new language of databases, you’ve got to get a database installed on your computer. In this book, you’ll be working with the MySQL database, which is one of the most common databases used in web applications. The reason for this is because it’s easy to get, easy to install, and easy to use.

Note

Like most things in life, ease-of-use comes with some tradeoffs. There are some database programs that cost a lot of money and are really complicated to use, such as Oracle. But these programs 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 can 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.

If you installed MAMP on your Mac or WampServer on your Windows-based PC (see Chapter 1, PHP on the Windows-Based Computers (WampServer Installation)), then you already have a local installation of MySQL. Pretty sweet, right? (And, if you didn’t—you masochist, you!—check out Appendix B for detailed, step-by-step instructions on installing MySQL without MAMP or WampServer.)

Just as Chapter 1 deals with a local installation of PHP, this chapter starts out by focusing on a local installation of MySQL. Of course, just as your PHP work in Chapter 1 progressed into working with a remote installation of PHP in Chapter 2, the same applies here, so if you don’t want to mess with WampServer or MAMP, you can jump on your hosting provider, fire up the mysql program, and get to typing. Of course, this assumes that your hosting provider lets you have shell access and lets you run the mysql console program—neither of which is a sure thing. If those become an issue, you can always resort to the already-installed MAMP/WampServer version on your local computer.

The mysql Console Program: Your New Best Friend

Regardless of how you install MySQL, your first step toward database mastery is to begin using the mysql console program. Although MySQL is a database, mysql is a program that lets you interact with that database from a command line. Every installation of MySQL comes with the mysql tool; you just have to know how to get at it.

But first you have to start the MySQL service. Otherwise, you’ll get errors because the mysql program has nothing to which it can connect. For WampServer, on the right side of the taskbar, click the green “W” icon and choose Put Online or Start All Services and read on. For MAMP, start the program, select Start Servers, and then go to Run the mysql Tool on MAMP.

Warning

Many budding web developers have had their careers crashed against the rocks of their MySQL server not being started. You can bang your head against the MySQL console all day and get nowhere if you don’t have MySQL started up on your computer.

Run the mysql Tool on WampServer

WampServer installs mysql along with the MySQL database. However, it doesn’t set up your PATH to access that program, so you’ll have to do a little digging around.

Find the MySQL Command-Line Program

Once you’ve started up MySQL, change into the wamp/ directory on the drive on which you installed WampServer. For example, if you put WampServer on the c:\ drive, go to a command prompt (Start→Run and then type command on Windows 7 and earlier; Windows key+R on Windows 8) and then type the following:

C:\Users\bdm0509> cd c:\wamp\bin\mysql\

Now, you’ll have to do a little investigation, because things change based upon the version of MySQL your copy of WampServer installed. Do a directory listing:

C:\wamp\bin\mysql>dir
 Volume in drive C has no label.
 Volume Serial Number is 7C78-FE01

 Directory of C:\wamp\bin\mysql

08/01/2012  02:32 PM    <DIR>          .
08/01/2012  02:32 PM    <DIR>          ..
08/01/2012  02:32 PM    <DIR>          mysql5.5.24
               0 File(s)              0 bytes
               3 Dir(s)  52,739,547,136 bytes free

You should see just one directory, specific to the version of MySQL installed by WampServer. Change into that directory. For MySQL 5.5.24, use the following:

C:\wamp\bin\mysql> cd mysql5.5.24

Now, you can go into yet another bin directory and finally run the mysql command:

C:\wamp\bin\mysql\mysql5.5.24> cd bin

C:\wamp\bin\mysql\mysql5.5.24\bin>mysql
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

Give mysql the Right User and Password

You have the right program, but the error message tells you that things aren’t working yet. That’s because you haven’t told mysql what user to use for logging in. You must specify the user with the -u option and then tell it to log in as “root”. Here’s how it looks:

C:\wamp\bin\mysql\mysql5.5.24\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

And there you have it! Somewhat surprising, you don’t need to provide a password to WampServer. Of course, because this is all running on your own local installation, security isn’t the concern it will be when you start connecting to a MySQL database on your web host out on the Internet. For now, you’re ready to start interacting with your database.

Run the mysql Tool on MAMP

If you’re on Mac OS X, MAMP came with a mysql tool, available through the Terminal. It’s stored in /Applications/MAMP/Library/bin, so you can run it like this:

$ /Applications/MAMP/Library/bin/mysql

Set Up mysql for Your User Profile

You can make this easier with a few quick edits to your profile and setup:

1. Create a directory called bin in your home directory:

   mkdir ~/bin

2. Go to that directory:

   cd ~/bin

3. Create a symbolic link to the mysql program in that directory:

   ln -s /Applications/MAMP/Library/bin/mysql mysql

4. Add your new ~/bin directory to your path. Edit your ~/.bash_profile:

   vi ~/.bash_profile

5. Find or add a line to update your PATH variable. For example:

   export PATH=$PATH:~/bin

6. Now save your .bash_profile, and restart Terminal.

Note

If you’re comfortable with the Mac OS X command line and see things here that don’t apply to your system—such as bash needing to be replaced by a custom shell you’ve installed—then feel free to make those changes. You’re probably a few steps ahead already, anyway, so if you know what to do, go on and make the changes for your system.

If you can power through this setup, all you have to do in the future is start a new Terminal and type the following:

$ mysql

If you don’t want to go through that rigamarole, just type the full path to mysql every time:

$ /Applications/MAMP/Library/bin/mysql

Give mysql the Right User and Password

For MAMP installations, just as with WampServer installations, using the mysql tool log in to MySQL as the root user. You specify that by using the -u option and then the username root, like this:

$ mysql -u root

Running this command as is doesn’t quite give you what you were hoping for. In fact, you’ll probably see an error similar to this:

$ /Applications/MAMP/Library/bin/mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:
NO)

That’s because users have passwords, and you haven’t supplied one. On top of that, for reasons too confusing to mention, the mysql program doesn’t ask you for a password, unless you instruct it to. Add the -p option, and you’ll be prompted for a password:

$ /Applications/MAMP/Library/bin/mysql -uroot -p
Enter password:

To find your root user password, go to the MAMP start page. That’s the web page that fires up every time you start MAMP. If MAMP is already running, you can click “Open start page.” You’ll see something like Figure 4-3, and there, clear as can be, is your password.

Figure 4-3. The MAMP start page is your first and best source for all things PHP, MySQL, and web server on your local installation of these programs. In this case, it displays the MySQL root password, which you’ll need…a lot!

A root user password of “root” isn’t particularly secure, but again, you’re on your local computer, not the NASA user store. Now, you can give that password to the mysql prompt. You should see something like this in return:

$ /Applications/MAMP/Library/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6
Server version: 5.5.9 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Once you're logged in, you can type exit to quit:
mysql> exit
Bye
$

That’s all there is to it! Now you’re ready to start talking to MySQL.

Run Your First SQL Query

MySQL has installed a number of pre-created databases on your system. To see them, all you have to do is ask. Fire up the mysql command line tool again, 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 must 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 might not have as many databases that come back, or you might have different databases. That’s OK.

The show command does just what you might expect: it shows you everything for a particular keyword; in this case, databases. To sum it up, show databases is just a way you can ask MySQL to show you all the databases installed on your machine.

On top of that, now you know something really important: MySQL really isn’t so much a database, but a piece of software that can store and create databases. In this example, there are eight rows returned as a result of running the show databases command, which means there are eight databases on the system, not just one. Before you’re done, you’ll have created several 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’ve only installed MySQL. You do that with the use command, like so:

use mysql;

Now, you’re in the mysql database. This means that any commands you give to MySQL are run against just the mysql database.

At the beginning of this section, you asked MySQL to show you all the databases it has; now tell it to show you all the tables in the database you’re currently using:

show tables;

You should get a nice long list, as illustrated 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 appear odd, but that’s mostly because these are internal tables used by MySQL. As you create new tables and users and set up your database, all of that information is stored within another database: the mysql database.

To see some of this information, you must use the select command to access information from a specific table—for example, the user table. Type this command at your MySQL command prompt:

mysql> select * from user;

The asterisk (*) means “select everything.” Then, from specifies to MySQL where to get the information: in this example, user, which is a table in your database.

Don’t be surprised when you get a confusing stream of information back. In fact, it might look like something out of the Matrix; check out Figure 4-4 for an example.

Figure 4-4. As you become more comfortable with MySQL and PHP, you’ll learn to select just the information you want and clean up this messy response, which was the result of using the from user command. There are also ways to format the response from MySQL, although you won’t need to worry about formatting much, because 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 instructed 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 client, 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 be replacing the * in the command with the specific column names you want, separated by commas:

mysql> select Host, User, Password from user;

You 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 example shows that for your local computer (localhost), you have a single user named “root.” The password is encrypted, so it isn’t very helpful, but you can see that MySQL definitely has an entry for you. Because you only asked for three columns, this response is a lot more readable and actually makes a little sense.

So, what’s a column? A column is a single category of information in your table. For example, in a table that stores users, you might have a first_name and a last_name column.

Note

If you’re starting to get a little dizzy or 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. Just get what you can now, and you’ll have all this new MySQL lingo under control in no time.

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

FREQUENTLY ASKED QUESTION: Going Local

My website is going to run on a server out there on the Internet. Why should I install MySQL on my own desktop?

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 might pay a monthly fee for hosting to a service like kattare.com for your own domain, you might own your own server that is connected to the Internet, or you might 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 even make it 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, why go through the trouble of installing PHP and MySQL on your own computer? 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 devices. Their programming lives are lived through telnet and sshsessions, writing code on a distant server, somewhere out on the Web.

Although your code rarely will ultimately run from your own computer, there are some really good reasons to install your entire development setup on it. 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, try again, and again, and again. The same is true when you start accessing a database. Although you could do this on the server on which your code will ultimately reside, that’s a lot of time spent on a network connection, using that machine’s resources, and potentially adding and deleting and adding data to a database. It’s a lot simpler to work on your own computer, and then at certain milestones, upload all your working code to your server.

And finally, you learn a ton by installing these programs from scratch. You get a better handle not just on your own device, but 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 computer didn’t have the right table permissions set up. Your installation is a way to 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 computer and on your web server. Just make sure that if you’re working on your own machine you can either get to its code with a web browser or you upload your code every time you’re ready to make sure things are working correctly. That way, you can follow along with all the examples. Beyond that, it’s up to you where you develop your code, test it, and run it.

SQL Is a Language for Talking to Databases

So far you’ve been 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 expressed more accurately, 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 the following and probably 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 obvious what it does: 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.”

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. Although you probably want to stick what with the folks around you are using, both are perfectly fine. (By the way, this book goes with the “sequel” pronunciation.)

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

Logging In to Your Web Server’s Database

Now that you’ve got a basic lay of the land for how MySQL behaves, 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.

Note

If you’ve never used telnet or ssh before, you should Google either program’s name; you’ll find a ton of resources. You might also want to call whoever hosts your domain and ask how you can best access your server. Many web providers now have a graphical version of SSH that you can use right from their control panel. Most good hosting providers also have detailed online instructions to help you get logged in and started, usually applicable to both Windows and Mac OS X.

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

Unfortunately, you’re likely to get an error like the following, 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 usually means that MySQL isn’t installed on your server, or at least that it’s not been configured correctly. But that’s probably by intention: most hosting providers keep their MySQL installation either on a different machine, or they at least limit accessibility by using a different domain name, like 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 --host=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 ensure that you can get mysql running, one way or another.

No matter where MySQL is installed, your task is simple. Run mysql and instruct it exactly where to connect. The --host= option lets you give mysql the hostname of your MySQL database server, and --user= lets you give it your own user name.

Note

You’ll almost certainly have a user name other than “admin” or “root” for your domain provider’s MySQL installation. You can ask what it is when you ask about telnet or ssh access. Or, if you want to try something out on your own, start with the user name and password you use for logging in to your web server itself. Be cautious, though: good database systems will have different user names 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, instructs 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 that slightly nosy cube-mate would be able to log in to your MySQL server. And, if you’re wondering what happened to the -u and -p options for mysql, you might want to check out the box that follows.

POWER USERS’ CLINIC: You Say Potato, I Say P

Back on Run the mysql Tool on MAMP, you used the -u option for a username, and the -p option to instruct MySQL to prompt you for a password. And, as you might guess, there’s a -h option you can use for specifying the host; it works like --host.

In fact, there’s no difference between using -u brett and --user=brett. The same is true for --password and -p, and --host and -h. There’s no subtle variation; each pair is identical.

Why two options?

Well, it’s really about brevity and convenience. You’ve probably heard it before, and it remains true: programmers are lazy typists. Given the choice between typing six characters (--user) and two (-u), programmers will choose the smaller amount every time. So the -p and -u and -h options are sort of a programmer-friendly shorthand.

There’s no right or wrong way to do it, which is a good thing. For clarity, this book uses the longer, easier-to-read versions most of the time. But now that you know the shortcuts, feel free to use them anytime you like.

Once you type your password, you should see the standard MySQL welcome screen, as demonstrated in Figure 4-5.

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

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

Selecting a Database with USE

On most MySQL installations that hosting providers give you, you don’t have nearly as much freedom as you do on your own installation. For example, on a remote server, suppose that you type the SQL show command you used on Run Your First SQL Query:

myqsl> show databases;

If you ran this earlier on your own computer, you probably saw a lot of databases listed here. But now, on your hosting provider, you’ll probably only see one:

myqsl> show databases;

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

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

In fact, you’re probably already set up within the database that’s named after you. Go ahead and inform MySQL that’s the database in 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 point it to 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 get used to seeing and typing SQL commands in all capital letters. So if you get an email from your database buddy and she suggests that you use a WHERE clause or tells you that your SELECT query is goofy, she’s not actually yelling at you. She’s saying (or more accurately, writing) SQL commands in all uppercase letters, which is the way most database jockeys do it.

In fact, the commands you’ve seen thus far 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 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 either uppercase or lowercase letters. They all work the same way.

Note

In this book, an all-capitals word like SELECT means the same thing as select written in code font.

Again, though, you don’t have to use capital letters in MySQL for keywords like SELECT and WHERE. Although it makes the code easier to decipher, lots of programmers get tired of all-caps and just go straight for the lowercase letters.

Using CREATE to Make Tables

When you could get to and USE the mysql database, you had some tables ready for you to SELECT from: the users table, for example. However, now you’re on a database server from which 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 might have already guessed, you can do that with another handy-dandy SQL keyword: CREATE. The objective is to create a table, put data in it, get data out, and generally have all kinds of database fun.

Type this command into your MySQL command line:

CREATE TABLE users (

This time, don’t add the usual semicolon at the end. When you press Enter, you’ll see something a little weird:

mysql> CREATE TABLE users (
    ->

As you know, MySQL commands should end in a semicolon, so when you leave it off, you’re telling MySQL, “Hey, I’m writing a command, but I’m not done yet.” What this demonstrates is that, you don’t have to jam a lot of SQL onto one line; you can split it up over several lines by pressing 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 continue typing.

So be obliging! Keep typing the following lines, each of which sets up a different column of information in your table:

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

Press 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 very modest way of saying, “I did what you asked.” If you get an error message instead, see the following box for advice on handling typos.

FREQUENTLY ASKED QUESTION: How to Fix a SQL Typo

I got an error message because I mistyped something in one line of my command. Now what do I do?

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

In some cases, MySQL will simply display an error and let 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, press 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. What do you do?

The problem is that from the perspective of MySQL, 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. This ends your current command—however broken that command might be—and instructs MySQL to process that command. This will usually cause an error, but then you’re back in control and can make fixes.

Without even thinking very hard, you probably know at least a bit about what’s going on in your CREATE command:

1. CREATE informs MySQL you want to create a new structure in the database.

2. TABLE specifies to MySQL what kind of structure. In this case, you want a table.

3. “users” is the name of the table you’re creating.

4. The opening parenthesis (() indicates to MySQL that you’re about to describe the table to create, one line at a time.

5. Each line has a column name, such as user_id, and a type, such as int or varchar(20).

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

You’ll learn a ton more about all the different types of columns as you go, but for now, there are just two to worry about. The first is int, which is short for integer. An integer, as you recall from math class, is any whole number; 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, ’it considers them identical.

The second type to which you need to pay some attention is a little less obvious: varchar. The varchar type stands for variable character, which means that it holds character data—strings—of variable lengths. Referring back to our example, a varchar(20) can hold a string as short as the length of 0 all the way up to a length of 20 characters. For advice on deciding on how big to make your columns, see the box below.

DESIGN TIME: The Size of Your Columns Really Does Matter

When most people are creating their 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 just come up with crazy, overly long lengths, but then you’re not really doing a very good job of making your database really 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!). But you can barely fit “www.facebook.com” into a 20-character column. So your columns should have different maximum lengths.

But this is about good design, not making your database hum. Your database only uses space for the information it holds; 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, making you look like 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.

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

Did this command work? Well, look for yourself by using the SHOW command:

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

No doubt about it, you definitely created a table. But, what’s actually in the table? For that, you need 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. Thus, DESC users; is a perfectly acceptable SQL command, too.

Now, you can see that MySQL did what you commanded it to: It 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.

Using DROP to Delete Tables

What goes up must come down, or so 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 now you need to delete that table. However, DELETE isn’t the command you want; instead, it’s DROP.

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

mysql> DROP TABLE users;
Query OK, 0 rows affected (0.10 sec)

Boom! It’s gone. But, just to be sure, confirm it:.

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 from which to SELECT. It’s back to creating tables again. Type that CREATE SQL statement into your MySQL tool one more time and create the users table again.

Note

On many systems, you can press the up arrow key and you’ll see the last command you ran. Press the up arrow key a few times, and it will cycle back through your command history. This 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. But it’s still empty, and that’s no good. What do you do? Easy: INSERT some data.

Try entering this command into your command-line tool:

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

What a mouthful! Still, you can probably 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 (VALUES), 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, is assigned to user_id; the second, “Mike”, to first_name; and so on.

And really, 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 PHP.

Using 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, given that 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 | [e-mail] |
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. However, just like earlier, the screen is a bit of a mess. Too many columns make the results hard to read.

To simplify things, grab just a few columns. For example, let’s assume that you don’t really need to see Mike’s entire Facebook page URL right now. From the code on Run Your First SQL Query, you know how to select specific columns of information:

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’s 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.

If you’d like, take some time to insert a few more rows of users and then 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)

As you can see, WHERE lets you choose a specific person or record of information. You’ll see that again on Avoid Changing User Input Whenever Possible. Try creating tables with more columns, selecting different columns, choosing records with WHERE, and see 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 what the difference is between SQL and MySQL. In fact, ask around at your local water cooler. You’d be surprised how many novice 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 program, 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 very often anymore.

MySQL is a database program. It lets you work with and administrate databases, and you do that with SQL. In other words, MySQL is really just a tool that lets you use SQL. That makes the name—MySQL—either terribly helpful or terribly confusing. Either way, in this book, you’ll be executing SQL commands against your MySQL database.

If you can keep the difference between SQL and MySQL in your mind, you’re going to be ahead of the game. That’s because when you work with your PHP, you’ll be connecting to a MySQL database, but you’ll be writing SQL commands and queries. Why does that matter? Because you can change to another database, and almost all of your SQL will work, as long as your new 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 almost all of 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, because you’ll be able to diagnose any issues and quickly solve them.

The takeaway here is to learn SQL, use MySQL, and end up with code that works on just about any SQL database.