Owen’s PHP form works well. Too well...

Owen’s email script was fine when he was only getting a few responses, but now he’s getting lots of emails, far more than he can manage.

He’s accidentally deleted some without reading them. And some are getting stuffed in his spam folder, which he never checks. In fact, an email he’d be very interested in seeing is hidden away in his spam folder right this moment... Owen needs a way to store all the messages so he can look at them when he has time and easily find ones related to Fang.

MySQL excels at storing data

Owen really needs a way to store the alien abduction report data in a safe place other than his email Inbox. What he needs is a database, which is kinda like a fancy, ultra-organized electronic file cabinet. Since the information in a database is extremely organized, you can pull out precisely the information you need when you need it.

Databases are managed by a special program called a database server, in our case a MySQL database server. You communicate with a database server in a language it can understand, which in our case is SQL. A database server typically runs alongside a web server on the same server computer, working together in concert reading and writing data, and delivering web pages.

MySQL stores data inside of database tables.

Note

The “SQL” in MySQL stands for Structured Query Language.

MySQL databases are organized into tables, which store information as rows and columns of related data. Most web applications use one or more tables inside a single database, sort of like different file folders within a file cabinet.

SQL is the query language used to communicate with a MySQL database.

With alien abduction data safely stored in a MySQL database, Owen can analyze the reports from everyone who answered “yes” to the Fang question at his convenience. He just needs to use a little SQL code to talk to the database server.

Owen needs a MySQL database

So it’s decided: MySQL databases are good, and Owen needs one to store alien abduction data. He can then modify the report.php script to store data in the table instead of emailing it to himself. The table will keep the data safe and sound as it pours in from abductees, giving Owen time to sift through it and isolate potential Fang sightings. But first things first... a database!

Creating a MySQL database requires a MySQL database server and a special software tool. The reason is because, unlike a web server, a database server has to be communicated with using SQL commands.

Creating MySQL databases and tables requires communicating with a MySQL database server.

You must have a MySQL database server installed before turning the page.

It’s impossible to help Owen without one! If you already have a MySQL database server installed and working, read on. If not, turn to Appendix B and follow the instructions for getting it installed. If you’re using a web hosting service that offers MySQL, go ahead and ask them to install it. Several pieces of information are required to access a MySQL database server. You’ll need them again later, so now is a good time to figure out what they are. Check off each one after you write it down.

With your MySQL database server information in hand, all that’s left is confirming that the server is up and running. Check one of the boxes below to confirm that you can successfully access your MySQL server.

Create a MySQL database and table

Some MySQL installations already include a database. If yours doesn’t, you’ll need to create one using the CREATE DATABASE SQL command in the MySQL terminal. But first you need to open the MySQL terminal in a command-line window—just typing mysql will often work. You’ll know you’ve successfully entered the terminal when the command prompt changes to mysql>.

To create the new alien abduction database, type CREATE DATABASE aliendatabase; like this:

Before you can create the table inside the database, you need to make sure you’ve got our new database selected. Enter the command USE aliendatabase;

The SQL code to create a table is a little more involved since it has to spell out exactly what kind of data’s being stored. Let’s take a look at the SQL command before entering it into the terminal:

To actually create the new table, type the big CREATE TABLE command into the MySQL terminal. (You can find the code for the command on the web at www.headfirstlabs.com/books/hfphp.) After successfully entering this command, you’ll have a shiny new aliens_abduction table.

Your MySQL installation may offer the phpMyAdmin web-based tool, which lets you access your databases and tables graphically. You can use the phpMyAdmin user interface to click your way through the creation of a database and table, or enter SQL commands directly just as if you’re in the MySQL terminal. Click the SQL tab in phpMyAdmin to access a text box that acts like the MySQL terminal.

So the SQL tab of the phpMyAdmin application provides a way to issue SQL commands just as if you were using the MySQL terminal.

You use the SQL INSERT statement to insert data into a table.

The SQL language provides all kinds of cool statements for interacting with databases. One of the more commonly used statements is INSERT, which does the work of storing data in a table.

Take a look at the statement below to see how the INSERT works. Keep in mind that this statement isn’t an actual SQL statement, it’s a template of a statement to show you the general format of INSERT.

One of the most important things to note in this statement is that the values in the second set of parentheses have to be in the same order as the database column names. This is how the INSERT statement matches values to columns when it inserts the data.

The INSERT statement in action

Here’s how an INSERT statement can be used to store alien abduction data in Owen’s new aliens_abduction table.

Watch it!

Order matters!

The values to be inserted must be listed in exactly the same order as the column names.

Use SELECT to get table data

Inserting data into a table is handy and all, but it’s hard not to feel a certain sense of unease at the fact that you haven’t confirmed that the data actually made its way into the table. It’s kind of like depositing money into a savings account but never being able to get a balance. The SELECT statement is how you “get the balance” of a table in a database. Or more accurately, SELECT allows you to request columns of data from a table.

The SQL SELECT statement retrieves columns of data from a table.

The columns supplied to a SELECT statement must be separated by commas. Regardless of how many columns a table has, only data in the columns specified in SELECT is returned. This SELECT statement grabs all of the first and last names of alien abductees from the aliens_abduction table:

To check an INSERT, you need a quick way to look at all of the data in a table, not just a few columns. The SELECT statement offers a shortcut for just this thing:

How many rows of data does your table have in it? ______

It’s true, each insertion into a MySQL database requires an INSERT statement.

And this is where communicating with a MySQL database purely through SQL commands gets tedious. Sure there are lots of benefits gained by storing Owen’s data in a database, as opposed to emails in his Inbox, but managing the data manually by issuing SQL statements in a MySQL tool is not a workable solution.

Brain Power

How do you think Owen’s MySQL data insertion problem can be solved?

Let PHP handle the tedious SQL stuff

The solution to Owen’s problem lies not in avoiding SQL but in automating SQL with the help of PHP. PHP makes it possible to issue SQL statements in script code that runs on the server, so you don’t need to use a MySQL tool at all. This means Owen’s HTML form can call a PHP script to handle inserting data into the database whenever it’s submitted—no emails, no SQL tools, no hassle!

PHP lets data drive Owen’s web form

PHP improves Owen’s alien abduction web form by letting a script send the form data directly to a database, instead of sending it to Owen’s email address and Owen entering it manually. Let’s take a closer look at exactly how the application works now that PHP is in the picture.

Connect to your database from PHP

Before a PHP script can insert or retrieve data from a MySQL database, it must connect to the database. Connecting to a MySQL database from PHP is similar in many ways to accessing a database from a MySQL tool, and it requires the same pieces of information. Remember the three checkboxes you filled out earlier in the chapter? Here they are again, along with a new one for the name of the database—go ahead and write them down one more time.

My MySQL server location (IP address or hostname): _________
Note
Your web hosting service or webmaster may tell you this, or if your web server and MySQL database server are running on the same machine, you can use the word “localhost”.
My database user name: _________
My database password: _________
My database name: _________
Note
The name of the database you created earlier, which is aliendatabase. If for some reason you named your database something else or decided to use a database that was already created, use that name instead.

The database server host location, username, password, and database name are all required in order to establish a connection to a MySQL database from a PHP script. Once that connection is made, the script can carry out SQL commands just as if you were entering them manually in a MySQL tool.

Insert data with a PHP script

Issuing a MySQL query from PHP code first requires you to establish a connection with the database. Then you build the query as a PHP string. The query isn’t actually carried out until you pass along the query string to the database server. And finally, when you’re finished querying the database, you close the connection. All of these tasks are carried out through PHP script code. Here’s an example that inserts a new row of alien abduction data:

Brain Power

What do you think each of these PHP functions is doing in the script?

Use PHP functions to talk to the database

There are three main PHP functions used to communicate with a MySQL database: mysqli_connect(), mysqli_query(), and mysqli_close(). If you see a pattern it’s no accident—all of the modern PHP functions that interact with MySQL begin with mysqli_.

Using these three functions typically involves a predictable sequence of steps.

Connect to a database with the mysqli_connect() function.
Provide the server location, username, and password to get permission to interact with the MySQL database server. Also specify the database name since this is a connection to a specific database.
Create an SQL query and store it as a string in a PHP variable.
To communicate with the database server, you have to use SQL commands. For example, an INSERT statement is needed to add data to the aliens_abduction table. There’s nothing special about the variable name we chose, but a straightforward name like $query works fine.
Issue the query with the mysqli_query() function.
Use the $query variable with the mysqli_query() function to talk to the MySQL database server and add data to the aliens_abduction table. You have to tell mysqli_query() both the name of the connection you created back in Step 1 and the name of the variable that holds your query from Step 2.
Close the database connection with the mysqli_close() function.
Finally, mysqli_close() tells the MySQL database server that you are finished communicating with it.

Let’s take a closer look at each one of these PHP database functions, starting with mysqli_connect()...

Get connected with mysqli_connect()

For our PHP script to be able to create a connection to the database with the mysqli_connect() function, you’ll need a few pieces of information that you’re starting to get very familiar with. Yes, it’s the same information you used earlier when working with the MySQL terminal, plus the name of the database.

	Your username and password
Who	You’ll need your own username and password for your own database server. These will either be set up by you or given to you by your web hosting company when MySQL is first installed. If you set up your own MySQL, follow the instructions to give yourself a secure username and password.
	The name of your database
What	In our example, we’ve named the database `aliendatabase`. Yours will be whatever name you decided to give it when you set it up earlier, or if your web hosting company created your database for you, you’ll be using that name.
	The location of the database (a domain name, an IP address or `localhost`)
Where	In our example, we’re using the location of Owen’s (fictional) database. You need to use the location of your own MySQL server. Often, this is `localhost` if the database server is on the same machine as your web server. Your web hosting company will be able to tell you this. It may also be an IP address or a domain name like Owen’s, such as `yourserver.yourisp.com`.

The location, username, password, and name of the MySQL database in the mysqli_connect() function must all have quotes around them.

The mysqli_connect() function treats the location, username, password, and database name as strings, so you must quote them.

The result of calling the function is a database connection and a PHP variable that you can use to interact with the database. The variable is named $dbc in the example, but you can name it anything you like.

This is where the PHP die() function comes in handy.

The PHP die() function terminates a PHP script and provides feedback about code that failed. While it won’t reveal precisely what went wrong, die() tells us that something’s up and that we need to fix it. If something’s wrong with one of the four connection variables for mysqli_connect(), or if the database server can’t be located, the die() function will stop the rest of the PHP script from running and show the error message in parentheses.

Yes! Once you’ve made a database connection with mysqli_connect(), you can issue SQL queries directly from PHP.

Nearly everything you can do in the MySQL terminal you can do in PHP code with the database connection you’ve now made. It’s this connection that establishes a line of communication between a PHP script and a MySQL database. For example, now that Owen has a connection to his database, he can start inserting data into the aliens_abduction table with the mysqli_query() function and some SQL query code.

The mysqli_query() function needs an SQL query stored in a PHP string ($query) in order to carry out the insertion of alien abduction data.

Build the INSERT query in PHP

SQL queries in PHP are represented as strings, and it’s customary to store a query in a string before passing it along to the mysqli_query() function. Since SQL queries can be fairly long, it’s often necessary to construct a query string from smaller strings that span multiple lines of code. Owen’s INSERT query is a good example of this:

With the INSERT query stored in a string, you’re ready to pass it along to the mysqli_query() function and actually carry out the insertion.

Query the MySQL database with PHP

The mysqli_query() function needs two pieces of information to carry out a query: a database connection and an SQL query string.

The database connection required by the mysqli_query() function was returned to you by the mysqli_connect() function. Just in case that’s a bit fuzzy, here’s the code that established that connection:

So you have a database connection ($dbc) and an SQL query ($query). All that’s missing is passing them to the mysqli_query() function.

An SQL query is a request written in SQL code that is sent to the database server.

This code shows that calling the mysqli_query() function isn’t just a one-way communication. The function talks back to you by returning a piece of information that’s stored in the $result variable. But no actual data is returned from the INSERT query—the $result variable just stores whether or not the query issued by mysqli_query() was successful.

The mysqli_query() function requires a database connection and a query string in order to carry out an SQL query.

Close your connection with mysqli_–close()

Since we’re only interested in executing the single INSERT query, the database interaction is over, at least as far as the script is concerned. And when you’re done with a database connection, you should close it. Database connections will close by themselves when the user navigates away from the page but, just like closing a door, it’s a good habit to close them when you’re finished. The PHP mysqli_close() function closes a MySQL database connection.

It’s a good habit to close a MySQL database connection when you’re finished with it.

In the case of Owen’s script, we need to pass mysqli_close() the actual database connection, which is stored in the $dbc variable.

Database servers only have a certain number of connections available at a time, so they must be preserved whenever possible.

And when you close one connection, it frees that connection up so that a new one can be created. If you are on a shared database, you might only have five connections allocated to you, for example. And as you create new database-driven applications, you’ll want to keep your supply of available connections open as much as you can.

This is a big problem. The INSERT query needs to be inserting the form data, not static strings.

The query we’ve built consists of hard coded strings, as opposed to being driven from text data that was entered into the alien abduction form. In order for the script to work with the form, we need to feed the data from the form fields into the query string.

Brain Power

What PHP code can help us get the values from Owen’s form into the INSERT query?

$_–POST provides the form data

The good news is that the report.php script already has the form data stored away in variables thanks to the $_POST superglobal. Remember this PHP code?

So you already have the form data in hand, you just need to incorporate it into the alien abduction INSERT statement. But you need to make a small change first. Now that you’re no longer emailing the form data, you don’t need the $name variable. You do still need the first and last name of the user so that they can be added to the database—but you need the names in separate variables.

Owen needs help sifting through his data

The new and improved report.php script is doing its job and automating the process of adding alien abduction reports to the database. Owen can just sit back and let the reports roll in... except that there’s a new problem. More data isn’t exactly making it any easier to hone in on alien abduction reports involving a potential Fang sighting.

Owen needs a way to find specific data, such as alien abductions where Fang was spotted.

You know what column of the database contains the information in question: fang_spotted. This column contains either yes or no depending on whether the abductee reported that they saw Fang. So what you need is a way to select only the reports in the aliens_abduction table that have a value of yes in the fang_spotted column.

You know that the following SQL query returns all of the data in the table:

SELECT * FROM aliens_abduction

The SQL SELECT statement lets you tack on a clause to control the data returned by the query. It’s called WHERE, and you tell it exactly how you want to filter the query results. In Owen’s case, this means only selecting alien abduction reports where the fang_spotted column equals yes.

Owen’s on his way to finding Fang

Thanks to PHP and its functions that interface to MySQL, Owen’s MySQL database server receives the alien abduction data from an HTML form and stores it in a database table. The data waits there safely in the table until Owen gets a chance to sift through it. And when he’s ready, a simple SELECT query is all it takes to isolate abduction reports that potentially involve Fang.

Who Does What?

Even though you haven’t seen it all put together yet, match each HTML, PHP, and MySQL component to what you think it does.

aliendatabase	This is the SQL code the PHP script passes to the MySQL server.
aliens_abduction table	This runs PHP scripts and returns HTML pages to browsers, often communicating with a database along the way.
report.html	The name of the database that contains the `aliens_abduction` table.
report.php	The HTML form uses this request method to send the data in the form to a PHP script.
POST	This is where the data from the `report.html` form will eventually end up being stored.
web server	This is where Owen collects data from the user.
MySQL database server	This PHP function closes a connection to the MySQL server.
Submit button	This is the name of Owen’s PHP script that processes the data users enter into his `report.html` form.
query	This PHP function sends a query to the MySQL server.
mysqli_connect()	This HTML element is used by visitors to the site when they finish filling out the form.
mysqli_close()	This is another name for the software that runs MySQL and all the databases and tables it contains.
mysqli_query()	This optional PHP function tells the database server which database to use.
mysqli_select_db()	This opens a connection between the PHP script and the MySQL server so they can communicate.

Q:	Q: I’m not sure I understand the difference between a database and a table. Don’t they both just store data?
A:	A: Yes. Tables serve as a way to divide up the data in a database into related groups so that you don’t just have one huge mass of data. It’s sort of like the difference between throwing a bunch of shoes into a huge box, as opposed to first placing each pair in a smaller box—the big box is the database, the smaller shoeboxes are the tables. So data is stored in tables, and tables are stored in databases.
Q:	Q: What exactly is the MySQL terminal? How do I find it on my computer?
A:	A: The MySQL terminal is a technique for accessing a MySQL database server through a command-line interface. In many cases the MySQL terminal is not a unique program, but instead a connection you establish using the command line from a “generic” terminal program, such as the terminal application in Mac OS X. How you access the MySQL terminal varies widely depending on what operating system you are using and whether the MySQL server is local or remote (located somewhere other than your computer). Appendix B has more details about how to go about accessing the MySQL terminal.
Q:	Q: What about phpMyAdmin? Where can I find that?
A:	A: Unlike the MySQL terminal, phpMyAdmin is a web-based application that allows access to a MySQL database. It is actually a PHP application, which is why you always access it from a web server, as opposed to installing it as a local client application. Many web hosting companies offer phpMyAdmin as part of their standard MySQL hosting plan, so it may already be installed for you. If not, you can download and install phpMyAdmin yourself. It is available for free download from `www.phpmyadmin.net`. Just remember that it must be installed on a web server and configured to have access to your MySQL databases, just like any other PHP and MySQL application.
Q:	Q: I have both the MySQL terminal and phpMyAdmin available. Which one should I use to access my database?
A:	A: It’s totally a personal preference. The upside to phpMyAdmin is that you can explore your databases and tables visually without having to enter SQL commands. That can be very handy once you get comfortable with SQL and don’t want to manually enter commands for every little thing. However, for now it’s a good idea to focus on really understanding how to interact with your MySQL data using SQL commands, in which case either tool works just fine.

Q:	Q: Why is an `INSERT` into a database called a query? Doesn’t “query” mean we’re asking the database for something?
A:	A: Yes, “query” does mean you’re asking for something...you’re asking the database to do something. In MySQL database applications, the word “query” is quite general, referring to any SQL command you perform on a database, including both storing and retrieving data.
Q:	Q: Why isn’t the `INSERT` statement just created as one big string?
A:	A: Keep in mind that the `INSERT` statement is stored as one big string, even though it is created from multiple smaller strings. Ideally, the `INSERT` statement would be coded as a single string. But like many SQL statements, the `INSERT` statement is quite long and doesn’t fit on a “normal” line of code. So it’s easier to read the query string if it’s coded as smaller strings that are glued together with periods.
Q:	Q: Is it really necessary to list the column names when doing an `INSERT`?
A:	A: No. You can leave off the column names in the `INSERT` statement. In which case, you must provide values for all of the columns in the table in the same order that they appear in the table structure. Knowing this, it’s generally safer and more convenient to specify the column names.

Q:	Q: Couldn’t you just put all the SQL code directly in the `mysqli_query()` function in place of the `$query` variable?
A:	A: You could, but it gets messy. It’s just a bit easier to manage your code when you store your queries in variables, and then use those variables in the `mysqli_query()` function.
Q:	Q: Should the code that issues the `INSERT` query be doing anything with the result?
A:	A: Perhaps, yes. So far we’ve been using `die()` to terminate a script and send a message to the browser if something goes wrong. Eventually you may want to provide more information to the user when a query’s unsuccessful, in which case you can use the result of the query to determine the query’s success.

Q:	Q: Do I have to create all those variables to store the `$_POST` data? Can’t I just reference the `$_POST` data directly into the `$query` string?
A:	A: Yes, you can. There’s nothing stopping you from putting `$_POST` directly in a query. However, it’s a good coding habit to isolate form data before doing anything with it. This is because it’s fairly common to process form data to some degree before inserting it into a database. For example, there are clever ways for hackers to try and hijack your queries by entering dangerous form data. You’ll learn how to thwart such attempts in Chapter 6. To keep things simple, this chapter doesn’t do any processing on form data, but that doesn’t mean you shouldn’t go ahead and get in the habit of storing form data in your own variables first before sticking it in a query.
Q:	Q: OK, so does it matter where you use single quotes versus double quotes? Can I use single quotes around the whole query and double quotes around each variable?
A:	A: Yes, it matters. And no, you can’t use single quotes around the whole query with double quotes around the variables. The reason is because PHP treats strings differently depending on whether they appear inside single quotes or double quotes. The difference between the two is that single quotes represent exactly the text contained within them, while some additional processing takes place on the text within double quotes. This processing results in a variable inside of double quotes getting processed and its value placed in the string in lieu of the variable name. This is quite handy, and is why double quotes are generally preferred for building SQL query strings.
Q:	Q: Couldn’t you just build query strings by concatenating the variables with the SQL code?
A:	A: Yes, and if you went the concatenation route, you could certainly use single quotes instead of double quotes. But query strings tend to be messy as it is, so anything you can do to make them more readable is a good thing—embedding variables directly in a double-quoted string instead of concatenating them with single quotes definitely makes query strings easier to understand.