PHP & MySQL: Novice to Ninja, 5th Edition (2012)

Chapter 2. Introducing MySQL

In Chapter 1, we installed and set up two software programs: the Apache web server with PHP, and the MySQL database server. If you followed my recommendation, you would have set them up using an all-in-one package like XAMPP or MAMP, but don’t let that diminish your sense of accomplishment! As I explained in that chapter, PHP is a server-side scripting language that lets you insert instructions into your web pages that your web server software (in most cases, Apache) will execute before it sends those pages to browsers that request them. In a brief example, I showed how it was possible to insert the current date into a web page every time it was requested. Now, that’s all well and good, but it really gets interesting when a database is added to the mix. In this chapter, we’ll learn what a database is, and how to work with your own MySQL databases using Structured Query Language.

An Introduction to Databases

A database server (in our case, MySQL) is a program that can store large amounts of information in an organized format that’s easily accessible through programming languages like PHP. For example, you could tell PHP to look in the database for a list of jokes that you’d like to appear on your website. In this example, the jokes would be stored entirely in the database. The advantage of this approach is twofold: First, instead of writing an HTML page for each joke, you could write a single PHP script that was designed to fetch any joke from the database and display it by generating an HTML page for it on the fly. Second, adding a joke to your website would be a simple matter of inserting the joke into the database. The PHP code would take care of the rest, automatically displaying the new joke along with the others when it fetched the list from the database. Let’s run with this example as we look at how data is stored in a database. A database is composed of one or more tables , each of which contains a list of items, or things. For our joke database, we’d probably start with a table called joke that would contain a list of jokes. Each table in a database has one or more columns , or fields. Each column holds a certain piece of information about each item in the table. In our example, our joke table might have one column for the text of the jokes, and another for the dates on which the jokes were added to the database. Each joke stored in this way would be said to be a row or entry in the table. These rows and columns form a table that looks like Figure 2.1.

Figure 2.1. A typical database table containing a list of jokes

Notice that, in addition to columns for the joke text (joketext) and the date of the joke (jokedate), I’ve included a column named id. As a matter of good design, a database table should always provide a means by which we can identify each of its rows uniquely. Since it’s possible that two identical jokes could be entered on the same date, we can’t rely upon the joketext and jokedate columns to tell all the jokes apart. The function of the id column, therefore, is to assign a unique number to each joke so that we have an easy way to refer to them and to keep track of which joke is which. We’ll take a closer look at database design issues like this in Chapter 5. To review, the table in Figure 2.1 is a three-column table with two rows, or entries. Each row in the table contains three fields, one for each column in the table: the joke’s ID, its text, and the date of the joke. With this basic terminology under your belt, you’re ready to dive into using MySQL.

Using phpMyAdmin to Run SQL Queries

Just as a web server is designed to respond to requests from a client (a web browser), the MySQL database server responds to requests from client programs. Later in this book, we’ll write our own MySQL client programs in the form of PHP scripts, but for now we can use a client program that comes bundled with both XAMPP and MAMP: phpMyAdmin. phpMyAdmin is itself a sophisticated web application written in PHP. Besides being included in XAMPP and MAMP, phpMyAdmin is provided by most commercial web hosts who offer PHP and MySQL as a tool for developers to manage their websites’ MySQL databases. Much like PHP and MySQL, phpMyAdmin’s ubiquity makes it an attractive tool for beginners to learn and use.

Note: Don’t have phpMyAdmin?

If you opted to follow the manual setup instructions in Appendix A rather than use the all-in-one package offered by XAMPP or MAMP to set up your web server, you probably don’t have phpMyAdmin installed on your server. The good news is that you can download and install it from the phpMyAdmin website, where instructions are provided.

If you’re using XAMPP on Windows, you can access phpMyAdmin by clicking the Admin… button next to MySql (sic) in the XAMPP Control Panel window, as shown in Figure 2.2.

Figure 2.2. Click the Admin… button to open phpMyAdmin

To access phpMyAdmin using MAMP on Mac OS X, click the Open start page button in the MAMP window. Then click the phpMyAdmin tab at the top of the screen, as shown in Figure 2.3.

Figure 2.3. You can access phpMyAdmin from MAMP’s start page

Either way, you should now have phpMyAdmin open in your default web browser, which should look like Figure 2.4. As of this writing, XAMPP includes the more recent (and better-looking) version 3.4 of phpMyAdmin, so I’ll be showing screenshots of that. If you’re using the older version 3.3, it won’t look quite as nice, but it should work just the same.

Figure 2.4. If you can see this, you have phpMyAdmin

If you go clicking around phpMyAdmin, you’ll discover all the tools you need to manage every aspect of your MySQL server and the data it contains. For now, I’m going to ignore all of those features and focus on a particular one: the SQL query window. See the row of buttons just beneath the phpMyAdmin logo? Clicking the second icon, indicated in Figure 2.5, opens the SQL query window shown in Figure 2.6.

Figure 2.5. Click the second button …

Figure 2.6. … to open the SQL query window

Into that big, empty text box you can type commands to ask your database server questions or make it perform tasks. Let’s try a few simple commands to take a look around your MySQL server. The MySQL server can actually keep track of more than one database. This allows a web host to set up a single MySQL server for use by several of its subscribers, for example. So, your first step after connecting to the server should be to choose a database with which to work. First, let’s retrieve a list of databases on the current server. Type this command into the SQL query window, then click Go:

SHOW DATABASES

You might think at first that nothing has happened, but you should now see the results in the main phpMyAdmin window, as shown in Figure 2.7.

Figure 2.7. The query results are displayed in the main phpMyAdmin window

Your list of databases might be as long as the one shown in Figure 2.7, or if you’re running MAMP it may only contain two critical databases. XAMPP uses additional databases to store configuration of its own, whereas MAMP is designed to avoid cluttering up your MySQL server with its own data. Either way, you will have databases named information_schema and mysql. The MySQL server uses the first database, named information_schema, to keep track of all the other databases on the server. Unless you’re doing some very advanced stuff, you’ll probably leave this database alone. The second database, mysql, is special too. MySQL uses it to keep track of users, their passwords, and what they’re allowed to do. We’ll steer clear of this for now, but we’ll revisit it in Chapter 10 when we discuss MySQL administration. A third database, named test , is a sample database that’s included with MySQL out of the box (again, MAMP does away with this database so you can start clean). If you see it in the list, you can delete the test database because you’ll be creating your own database in a moment. Deleting stuff in MySQL is called “dropping” it, and the command for doing so is appropriately named:

DROP DATABASE test

If you type this command into the SQL query window and click Go, phpMyAdmin will probably display an error message: "DROP DATABASE" statements are disabled. This message indicates that a safety feature built into phpMyAdmin is preventing you from running dangerous-looking queries like this one. If you want to be able to drop databases (and this is probably a good ability to have, given the amount of experimentation I’m going to encourage you to do in this book), there is a way to do so tucked away in phpMyAdmin. In the main phpMyAdmin window, click theDatabases tab (the leftmost tab at the top of the main window area). You’ll be presented with a list of databases on the server, with a checkbox next to each. Check the one you want to delete (test in this case); then click the Drop button at the bottom-right of the list as shown in Figure 2.8.

Figure 2.8. The ability to drop a database in phpMyAdmin is well hidden

phpMyAdmin presents one last prompt to make sure you mean to obliterate the database. If you confirm this, MySQL will obediently delete the database, and phpMyAdmin will display a message to verify it was successful. Note that there are other potentially hazardous commands you can send to MySQL in addition to DROP DATABASE, but phpMyAdmin won’t always protect you if you make a mistake. You have to be very careful to type your commands correctly in the SQL query window, otherwise you can destroy your entire database—along with all the information it contains—with a single command!

Structured Query Language

The commands we’ll use to direct MySQL throughout the rest of this book are part of a standard called Structured Query Language, or SQL (pronounced as either “sequel” or “ess-cue-ell”—take your pick). Commands in SQL are also referred to as queries; I’ll use these two terms interchangeably. SQL is the standard language for interacting with most databases, so, even if you move from MySQL to a database like Microsoft SQL Server in the future, you’ll find that the majority of commands are identical. It’s important that you understand the distinction between SQL and MySQL. MySQL is the database server software that you’re using. SQL is the language that you use to interact with that database.

Tip: Learn SQL in Depth

In this book, I’ll teach you the essentials of SQL that every PHP developer needs to know. If you decide to make a career out of building database driven websites, it pays to know some of the more advanced details of SQL, especially when it comes to making your sites run as quickly and smoothly as possible. To dive deeper into SQL, I highly recommend the book Simply SQL by Rudy Limeback.

Creating a Database

When the time comes to deploy your first database driven website on the Web, you’re likely to find that your web host or IT department has already created a MySQL database to use. Since you’re in charge of your own MySQL server, however, you’ll need to create your own database to use in developing your site. It’s just as easy to create a database as it is to delete one. Open the SQL query window again, and type this command:

CREATE DATABASE ijdb

I chose to name the database ijdb, for Internet Joke Database,^[4] because that fits with the example I gave at the beginning of this chapter: a website that displays a database of jokes. Feel free to give the database any name you like, though.

Note: Case Sensitivity in SQL Queries

Most MySQL commands are not case-sensitive, which means you can type CREATE DATABASE, create database, or even CrEaTe DaTaBaSe, and it will know what you mean. Database names and table names, however, are case-sensitive when the MySQL server is running on an operating system with a case-sensitive file system (such as Linux or Mac OS X, depending on your system configuration). Additionally, table, column, and other names must be spelled exactly the same when they’re used more than once in the same query. For consistency, this book will respect the accepted convention of typing database commands in all capitals, and database entities (databases, tables, columns, and so on) in all lowercase.

Now that you have a database, you need to tell phpMyAdmin that you want to use it. You’ve probably noticed by now that the left-hand sidebar in the main phpMyAdmin window contains a list of all the databases on your MySQL server. When you clicked Go to run your CREATE DATABASEcommand (you did click Go, didn’t you?), this sidebar updated to show your new database’s name in a drop-down menu, as shown in Figure 2.9.

Figure 2.9. phpMyAdmin autoselects your new database for you

It’s nice of phpMyAdmin to autoselect your new database for you, but you’ll need to know how to select it yourself. Click the home button (the first in the row of icons beneath the phpMyAdmin logo) to go back to the home page of phpMyAdmin. The sidebar will once again display a list of all databases on your server. To select a database to work with, just click its name in the sidebar. With your database selected, click the Query window button again to open a new SQL query window. This query window is slightly different from the last one: the caption for the text box now says Run SQL query/queries on database ijdb. Commands typed into this query window will run on your new database, instead of your MySQL server as a whole.

Figure 2.10. You must open a new query window to work with this database

You’re now ready to use your database. Since a database is empty until you add tables to it, our first order of business is to create a table that will hold your jokes (now might be a good time to think of some!).

Creating a Table

The SQL commands we’ve encountered so far have been reasonably simple, but as tables are so flexible, it takes a more complicated command to create them. The basic form of the command is as follows:

CREATE TABLE table_name (

column1Name

column1Type

column1Details,

column2Name

column2Type

column2Details,

…

) DEFAULT CHARACTER SET charset ENGINE=InnoDB

Let’s continue with the joke table I showed you in Figure 2.1. You’ll recall that it had three columns: id (a number), joketext (the text of the joke), and jokedate (the date on which the joke was entered). This is the command to create that table:

CREATE TABLE joke (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

joketext TEXT,

jokedate DATE NOT NULL

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB

Looks scary, huh? Let’s break it down:

CREATE TABLE joke (

This first line is fairly simple; it says that we want to create a new table named joke. The opening parenthesis (() marks the beginning of the list of columns in the table.

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

This second line says that we want a column called id that contains an integer (INT); that is, a whole number. The rest of this line deals with special details for the column:

1. First, when creating a row in this table, this column cannot be left blank (NOT NULL).

2. Next, if we don’t specify a value for this column when we add a new entry to the table, we want MySQL to automatically pick a value that’s one more than the highest value in the table so far (AUTO_INCREMENT).

3. Finally, this column is to act as a unique identifier for the entries in the table, so all values in this column must be unique (PRIMARY KEY).

joketext TEXT,

This third line is super simple; it says that we want a column called joketext, which will contain text (TEXT).

jokedate DATE NOT NULL

This fourth line defines our last column, called jokedate; this will contain a date (DATE) that cannot be left blank (NOT NULL).

) DEFAULT CHARACTER SET utf8

The closing parenthesis ()) marks the end of the list of columns in the table. DEFAULT CHARACTER SET utf8 tells MySQL that you’ll be storing UTF-8 encoded text in this table. UTF-8 is the most common encoding used for web content, so you should employ it in all your database tables that you intend to use on the Web.

ENGINE=InnoDB

This tells MySQL which storage engine to use to create this table. Think of a storage engine as a file format. When building a website, you’ll typically choose to use the JPEG format for the photos on your site, but stick with the PNG format for the images that make up your site design. Both formats are supported by browsers, but they each have strengths and weaknesses. Likewise, MySQL supports multiple formats for database tables. The InnoDB format is by far the best choice for website databases like the one we’ll build in this book. The older MyISAM format is the default, however, so we must tell MySQL that we want it to create an InnoDB table.

Note that we assigned a specific data type to each column we created. id will contain integers, joketext will contain text, and jokedate will contain dates. MySQL requires you to specify in advance a data type for each column. This helps to keep your data organized, and allows you to compare the values within a column in powerful ways, as we’ll see later. For a list of MySQL data types, see Appendix D. Now, if you type the above command correctly and click Go, the main phpMyAdmin window will confirm that the query executed successfully, and your first table will be created. If you made a typing mistake, phpMyAdmin will tell you there was a problem with the query you typed, and will try to indicate where it had trouble understanding what you meant. Let’s have a look at your new table to make sure it was created properly. Type the following command into the SQL query window, and click Go:

SHOW TABLES

phpMyAdmin should display the output shown in Figure 2.11.

Figure 2.11. phpMyAdmin lists the tables in the currently selected database

This is a list of all the tables in your database (which we named ijdb). The list contains only one table: the joke table you just created. So far, everything seems fine. Let’s take a closer look at the joke table itself using a DESCRIBE query:

DESCRIBE joke

As you can see in Figure 2.12, there are three columns (or fields) in this table, which appear as the three rows in this table of results. The details are a little cryptic, but if you look at them closely, you should be able to figure out what they mean. It’s nothing to be worried about, though. You have better things to do, like adding some jokes to your table!

Figure 2.12. phpMyAdmin lists the columns in the joke table as rows

We need to look at just one more task before we do that, though: deleting a table. This task is as frighteningly easy as deleting a database with a DROP DATABASE command—except that phpMyAdmin won’t protect you here. Don’t run this command with your joke table, unless you actually do want to be rid of it! If you really want to try it, be prepared to re-create your joke table from scratch:

DROP TABLE tableName

Inserting Data into a Table

Your database is created and your table is built; all that’s left is to put some jokes into the database. The command that inserts data into a database is called, appropriately enough, INSERT . This command can take two basic forms:

INSERT INTO tableName SET

column1Name = column1Value,

column2Name = column2Value,

…

INSERT INTO tableName

(column1Name, column2Name, …)

VALUES (column1Value, column2Value, …)

So, to add a joke to our table, we can use either of these commands:

INSERT INTO joke SET

joketext = "Why did the chicken cross the road? To get to the other

↵ side!",

jokedate = "2012-04-01"

INSERT INTO joke

(joketext, jokedate) VALUES (

"Why did the chicken cross the road? To get to the other side!",

"2012-04-01")

Note that in both forms of the INSERT command, the order in which you list the columns must match the order in which you list the values. Otherwise, the order of the columns isn’t important. Go ahead and swap the order of the column and value pairs and try the query. As you typed the query, you’ll have noticed that we used double quotes (") to mark where the text of the joke started and ended. A piece of text enclosed in quotes this way is called a text string, and this is how you represent most data values in SQL. For instance, the dates are typed as text strings, too, in the form "YYYY-MM-DD". If you prefer, you can type text strings surrounded with single quotes (') instead of double quotes:

INSERT INTO joke SET

joketext = 'Why did the chicken cross the road? To get to the other

↵ side!',

jokedate = '2012-04-01'

You might be wondering what happens when there are quotes used within the joke’s text. Well, if the text contains single quotes, you would surround it with double quotes. Conversely, if the text contains double quotes, surround it with single quotes. If the text you want to include in your query contains both single and double quotes, you’ll have to escape the conflicting characters within your text string. You escape a character in SQL by adding a backslash (\) immediately before it. This tells MySQL to ignore any “special meaning” this character might have. In the case of single or double quotes, it tells MySQL not to interpret the character as the end of the text string. To make this as clear as possible, here’s an example of an INSERT command for a joke containing both single and double quotes:

INSERT INTO joke

(joketext, jokedate) VALUES (

'Knock-knock! Who\'s there? Boo! "Boo" who? Don\'t cry; it\'s only a

↵ joke!',

"2012-04-01")

As you can see, I’ve marked the start and end of the text string for the joke text using single quotes. I’ve therefore had to escape the three single quotes (the apostrophes) within the string by putting backslashes before them. MySQL would see these backslashes and know to treat the single quotes as characters within the string, rather than end-of-string markers. If you’re especially clever, you might now be wondering how to include actual backslashes in SQL text strings. The answer is to type a double-backslash (\\), which MySQL will treat as a single backslash in the string of text. Now that you know how to add entries to a table, let’s see how we can view those entries.

Viewing Stored Data

The command that we use to view data stored in database tables, SELECT , is the most complicated command in the SQL language. The reason for this complexity is that the chief strength of a database is its flexibility in data retrieval. At this early point in our experience with databases, we need only focus on fairly simple lists of results, so let’s consider the simpler forms of the SELECT command here. This command will list everything that’s stored in the joke table:

SELECT * FROM joke

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

Figure 2.13. phpMyAdmin lists the full contents of the joke table

If you were doing serious work on such a database, you might be tempted to stop and read all the hilarious jokes in the database at this point. To save yourself the distraction, you might want to tell MySQL to omit the joketext column. The command for doing this is as follows:

SELECT id, jokedate FROM joke

This time, instead of telling it to “select everything,” we told it precisely which columns we wanted to see. The result should look like Figure 2.14.

Figure 2.14. You can select only what you need

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

SELECT id, LEFT(joketext, 20), jokedate FROM joke

The results are shown in Figure 2.15.

Figure 2.15. The LEFT function trims the text to a specified length

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

SELECT COUNT(*) FROM joke

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

Figure 2.16. The COUNT function counts the rows

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

SELECT COUNT(*) FROM joke WHERE jokedate >= "2012-01-01"

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

SELECT joketext FROM joke WHERE joketext LIKE "%chicken%"

This query displays the full text of all jokes containing the text “chicken” in their joketext column. The LIKE keyword tells MySQL that the named column must match the given pattern.^[5] In this case, the pattern we’ve used is "%chicken%". The % signs indicate that the text “chicken” may be preceded and/or followed by any string of text. Conditions may also be combined in the WHERE clause to further restrict results. For example, to display knock-knock jokes from April 2012 only, you could use the following query:

SELECT joketext FROM joke WHERE

joketext LIKE "%knock%" AND

jokedate >= "2012-04-01" AND

jokedate < "2012-05-01"

Enter a few more jokes into the table (for example, the “Knock-Knock” joke mentioned earlier) and experiment with SELECT queries (for ideas, see Chapter 4). You can do a lot with the SELECT command, so I’d encourage you to become quite familiar with it. We’ll look at some of its more advanced features later, when we need them.

Modifying Stored Data

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

UPDATE tableName SET

colName = newValue, …

WHERE conditions

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

UPDATE joke SET jokedate = "2013-04-01" WHERE id = "1"

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

UPDATE joke SET jokedate = "2010-04-01"

WHERE joketext LIKE "%chicken%"

Deleting Stored Data

Deleting entries in SQL is dangerously easy, which you’ve probably noticed is a recurring theme. Here’s the command syntax:

DELETE FROM tableName WHERE conditions

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

DELETE FROM joke WHERE joketext LIKE "%chicken%"

Warning: Careful with That Enter Key!

Believe it or not, the WHERE clause in the DELETE command is optional. Consequently, you should be very careful when typing this command! If you leave the WHERE clause out, the DELETE command will then apply to all entries in the table. The following command will empty thejoke table in one fell swoop:

DELETE FROM joke

Scary, huh?

Let PHP Do the Typing

There’s a lot more to the MySQL database server software and SQL than the handful of basic commands I’ve presented here, but these commands are by far the most commonly used. At this stage, you might be thinking that databases seem a little cumbersome. SQL can be tricky to type, as its commands tend to be long and verbose compared to other computer languages. You’re probably dreading the thought of typing in a complete library of jokes in the form of INSERT commands. Don’t sweat it! As we proceed through this book, you’ll be surprised at how few SQL queries you actually type by hand. Generally, you’ll be writing PHP scripts that type your SQL for you. For example, if you want to be able to insert a bunch of jokes into your database, you’ll typically create a PHP script for adding jokes that includes the necessary INSERT query, with a placeholder for the joke text. You can then run that PHP script whenever you have jokes to add. The PHP script prompts you to enter your joke, then issues the appropriate INSERT query to your MySQL server. For now, however, it’s important to gain a good feel for typing SQL by hand. It will give you a strong sense of the inner workings of MySQL databases, and will make you appreciate all the more the work that PHP will save you! To date, we’ve only worked with a single table, but to realize the true power of a relational database, you’ll need to learn how to use multiple tables together to represent potentially complex relationships between the items stored in your database. I’ll cover all this and more in Chapter 5, in which I’ll discuss database design principles and show off some more advanced examples. In the meantime, we’ve accomplished our objective, and you can comfortably interact with MySQL using the phpMyAdmin query window. In Chapter 3, the fun continues as we delve into the PHP language, and use it to create several dynamically generated web pages. If you like, you can practice with MySQL a little before you move on by creating a decent-sized joke table (for our purposes, five should be enough). This library of jokes will come in handy when you reach Chapter 4.