PHP and MySQL: The Missing Manual (2011)
Part 2. Dynamic Web Pages
Chapter 6. Generating Dynamic Web Pages
You’ve been building up quite a robust set of tools. You have PHP scripts to receive requests from your HTML forms. You have MySQL to store information from your users. You have regular expressions to massage information into just the formats you need, and some basic flow controls in PHP like if and for to let you build scripts that make decisions based on what information your users give you.
But at the end of the day, your goal in learning PHP and MySQL is probably to make dynamic and interesting web applications, and you’ve not done much of that yet. You’ve got a few interesting forms, but even those are pretty simple: take in some information and print it back out; accept a SQL query (and do that quite imperfectly).
Thankfully, you’ve got everything you need to start building pages that are built dynamically using your user’s information and full-fledged web applications. You can get information from your users, store it in a database, and even do some basic data manipulation. It’s time to put it all together into the web pages that folks expect: a place to enter their information, a place to see their information, and in most cases, a place to look at all related user information.
Revisiting a User’s Information
On Access Request Parameters Directly, you built a form that took in a user’s basic social media profile: a Twitter handle, a Facebook URL, and some basic contact information. That form is shown in Figure 6-1: it’s simple and easy to use.
Figure 6-1. It turns out that you can design forms that interact and submit to PHP scripts the same way you create any other web page: you use HTML and hopefully CSS to create a clean, easy-to-understand page. Then get users to visit your page, fill out fields, and click buttons. It’s the behind-the-scenes work that brings PHP and MySQL into the picture.
And here’s where there’s a surprising amount of work: getting from a simple form on the Web to a script that interacts with a database. You need to figure out, design, and create tables, interact with those tables, potentially deal with errors from your database, and the list goes on. Best get started!
NOTE
If you haven’t already, copy over the HTML web form you created on Access Request Parameters Directly to the directory you’re working in. You can leave the file named as it is, but you may want to rename it as create_user.html. For reasons you’ll see soon, this little change can really pay off as your site gets more complex.
There’s really no reason to change this form. However, the script that accepts this information is pretty lame. It does nothing more than a little text manipulation and regurgitation (see Figure 6-2). That’s where the work is: making the script do something with the user’s information.
Figure 6-2. This HTML is generated by your old getFormInfo. php script. It’s pretty uninspiring, and you can definitely do a lot better. Even more troublesome, this information is never stored anywhere. Once your users move on, their information is lost.
Planning Your Database Tables
Building web applications is a lot like working a tricky maze: sometimes the hardest part is figuring out where to start. Usually a web form needs a script to which it can submit data. But that script needs a table into which it can insert information. But where’s the table? In a database you need to create or set up for web access. And then there’s the table itself: it needs structure. And that’s the way almost every form of every application goes: what starts out as a page that users see often ends up at a back-end structure that’s invisible to everyone but you, the programmer.
It’s always easiest to simply start with the information you want to store. You’ve actually already done some of this, when you created your entry form (look back at Figure 6-1). So here’s basically what you’re collecting from your users right now:
§ First Name
§ Last Name
§ E-Mail Address
§ Facebook URL
§ Twitter Handle
It really is that simple: you just need these five bits of information. And each of these really are about a single “thing”—a user. So the simple conclusion is you need a table to store users, and each user has a first name, last name, email address, a Facebook URL, and a Twitter handle.
Now just translate this into a SQL CREATE statement, something you should have no problem doing:
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)
);
WARNING
You may not want to dive into your MySQL command-line tool or your web form and run this command just yet. There are some important additions to be made before it’s ready for prime time.
You may remember this SQL from Making Tables with CREATE, but that was ages ago, when you had but a fragile understanding of databases. Now you know exactly what’s going to be dropped into this table: information from the web form you’ve already got.
UP TO SPEED: ONE OF THESE THINGS IS LIKE THE OTHER
When you start talking with database people, you quickly encounter a lot of interchangeable terms.
A table has rows, and each entry in that table is a row. But you’ll also hear a row called an entry in the table, as well as a record. These terms all mean the same thing, and even though it may be technically better to say a table has rows rather than entries or records, in real life people use these terms interchangeably.
In the same vein, the fields in a table like first_name or last_name are also called columns. And in those fields (or columns), you have values, or information, or for the technically stodgy, data. Lots of different terms, all with identical meaning.
You can help matters by not mixing and matching terms. Rows and columns go together; so do records and fields. A table that has rows has columns; a table with records has fields.
Above all, remember that, as with any other bit of language, context is king. It’s more important you know what’s an int and what’s not than to be sure you say row instead of record. And not to confuse a complete entry with the individual parts of that entry. So a single entry, record, or row in the users table has multiple fields, columns, or pieces of information. Get that right, and you can solve the rest by listening carefully and asking the right questions.
Good Database Tables Have id Columns
There’s one little detail worth noting, though: the table’s user_id field. Think about the most common thing you do with databases. It’s not creating new entries or user profiles; it’s looking up and accessing that information.
Next, consider how do you search for information? You can look things up by a last name, and then find matching entries; or you can search by an email or Twitter handle, which are supposed to be unique for each user. In fact, you’ve probably often been asked to create a unique username (often at great pain; there aren’t many “normal” names left on Twitter, except for m97f-ss0).
Databases are no different: they need something for which to look. And more than that, databases work well only when they can identify every individual row in a table by a unique piece of information. But there’s more than that for a database: databases function better with numbers that text. The absolute preferred type of unique identifier—or ID—for a row in a table is a unique number.
That, then, is what user_id is about. It’s a special value for each row that is unique, and that is numerical. It identifies each user as separate from all others, and lets your database do its thing quickly and well.
Auto-Increment Is Your Friend
There’s a bit of a problem lurking in the SQL bushes here, though. If the point of the user_id field is to provide a unique identifier for each user, whose job is it to keep up with that unique ID? How do all the scripts (and there will be more than one or two before you’re done with any large web application) make sure that no two users are entered into the users table with the same user_id?
This problem isn’t trivial, because if you lose the ability to identify a user uniquely, things can go south fast. On the other hand, nobody wants to spend hours writing number generators for every table, or every web application they write.
The solution is not in your code, but in your database. Most databases, MySQL included, give you the ability to use auto increment. You specify this value on a field, and every time you add a row to that table, the field automatically creates a new number, incremented from the previous row you added. So if one script adds a new user and MySQL sets the user_id to 1029, and another script then adds a new user, MySQL simply adds one, gets 1030, and sets that up as the ID of the new user.
You can add this to your table CREATE statement like this:
CREATE TABLE users (
user_id int AUTO_INCREMENT,
first_name varchar(20),
last_name varchar(30),
email varchar(50),
facebook_url varchar(100),
twitter_handle varchar(20)
);
Much better. Now you don’t have to worry about IDs. In fact, you don’t have to do anything special to let MySQL know to fill in the user_id column. Every time you add a new row, MySQL also adds a new value to user_id.
IDs and Primary Keys are Good Bedfellows
Now that you’ve got user_id auto incrementing, you’ve actually done something else subtly in MySQL: you’ve defined user_id as the primary key in the users table. The primary key is a database term for that special, unique value that each table has.
NOTE
In some rather special cases, you might create a primary key out of multiple columns. That’s somewhat unusual, though.
Primary keys are important because databases typically create an index on a table’s primary key. An index is a database-level mechanism by which a database can find rows based on that index quickly. So if you have the user_id column indexed, you can find a row with a user_id of 2048 much faster than looking for a row with that same user_id, but on a table where user_id is not indexed.
An indexed field is like having a highly organized set of values that allow for quick searching. An unindexed field can still be searched, but then your database has to go through each value, one by one, until it finds the exact value you’re searching for. It’s the difference between looking for a book in a good library and looking for one in your great-great-grandfather’s cluttered attic.
When you told MySQL to AUTO_INCREMENT user_id, you identified that field as special. In fact, MySQL won’t let you set more than one field to AUTO_INCREMENT, because it’s assuming you put that on a field to use it as a primary key.
But here’s where you have to remember something—something that you might expect MySQL to do for you, but it won’t. You have to tell MySQL that you want user_id to be the primary key:
CREATE TABLE users (
user_id int AUTO_INCREMENT PRIMARY KEY,
first_name varchar(20),
last_name varchar(30),
email varchar(50),
facebook_url varchar(100),
twitter_handle varchar(20)
);
This line makes explicit what is implicit with AUTO_INCREMENT: user_id uniquely identifies each user entry in your table. In fact, if you don’t add this line, MySQL gives you an error. So suppose you have this SQL, without the PRIMARY KEY:
CREATE TABLE users (
user_id int AUTO_INCREMENT,
first_name varchar(20),
last_name varchar(30),
email varchar(50),
facebook_url varchar(100),
twitter_handle varchar(20)
);
If you were to run this query, MySQL will give you a weird error; Figure 6-3 shows that error in the phpMyAdmin console.
Figure 6-3. phpMyAdmin is a great tool for running queries. You can avoid spending lots of time in a text-based tool, you can browse your tables visually, and best of all, most Web hosting companies that provide MySQL and database services offer phpMyAdmin. That means if you learn it on one host, you can probably use the same tool on another host.
This error—the rather infamous #1075 if you’ve been around MySQL for long—tells you that since you’ve got an AUTO_INCREMENT column, you need to mark it with PRIMARY KEY. It would be nice if MySQL would take care of that for you, but alas, it’s up to you. So add back inPRIMARY KEY, and you’re almost ready to create this table for real, minus the errors.
Adding Constraints to Your Database
The purpose of a field like user_id is to allow for easy searching. Adding AUTO_INCREMENT (and setting the field as a primary key) helps, but there’s something subtle that also happens behind the scenes when you create an AUTO_INCREMENT column: you’re saying, “No matter what, this column will have a value.” That’s because MySQL is filling in that value.
But there are other fields that you also almost always want to fill in. First name and last name fields, for example. And you should probably require an email address, too. Users won’t always have Twitter handles and Facebook URLs, so you can leave those off, but the rest is mandatory.
You could just let your PHP scripts and web pages deal with requiring this information. But is that really safe? What if someone else forgets to add validation on a web page? What if you forget, writing code on a coffee-high one day, typing away at 2 AM? Whenever you can validate, it’s always a good idea to do so.
Once again, MySQL gives you what you need. You can require a value on a field by telling MySQL that field can’t be null, which is just programmer-talk for “not a value”:
CREATE TABLE users (
user_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name varchar(20) NOT NULL,
last_name varchar(30) NOT NULL,
email varchar(50) NOT NULL,
facebook_url varchar(100),
twitter_handle varchar(20)
);
NOTE
Even though MySQL handles auto incrementing and inserting values into user_id, it’s still a good idea to make it NOT NULL. That makes it clear that the value in that column is required, regardless of how MySQL or any other code actually fills that value.
As with AUTO_INCREMENT, this change is quick, easy, and goes a long way to protecting the integrity of your information (or, to be more accurate, your user’s information!).
DESIGN TIME: TO NULL OR NOT TO NULL
Although the users table makes figuring out which columns should be NOT NULL fairly easy, that’s not always the case. In fact, even with users, there’s ambiguity: are you sure you want to require an email address? It’s possible that someone might not have one (although why email-less folks are surfing the Internet is a mystery), or you may have users concerned with you spamming them, and they don’t want to enter an email address. Are you sure you want to require that as part of a user’s information?
It might surprise you, but making a column NOT NULL is one of the most important decisions you’ll make with regard to an individual table. This is particularly true if you decide not to make a column NOT NULL. Every record added might have a null value there, and if you decide down the line, “Oops, I really did need that value,” you’re stuck for all the old entries that don’t have it. You can’t ever un-ring that bell.
However, don’t get too trigger-happy with NOT NULL, thinking that it’s just safer to use it frequently and be sure you’ve got more data, rather than less. Users can get pretty upset when they have to fill out 28 fields just to use your site. Even mega-sites like Facebook and Twitter require only minimal information: usually a name, email, username, and password.
In general, the rule of thumb is to require only what you absolutely have to; but to absolutely require that information. That’s a tongue twister, but a useful one. Think long and hard, make a decision, and then realize that you’ll always upset someone. Your goal is to please most of your users most of the time; if you can pull that off while still getting the information from them you need, you’re well on your way to Web stardom and Internet fame (whatever that’s worth!).
And one last subtle bit of help: you’re working at the table level with NOT NULL, not the application level. In other words, you’re essentially saying, “This column can’t be null if (and only if) there’s an entry in this table.” So you may decide that users don’t have to enter an address, but if they enter an address, street, city, and country are required. Thinking along these lines—what data is essential for this particular table, rather than your entire app—will help you lock down your database with good, useful data, and still not go crazy with NOT NULL.
You should have a pretty useful SQL statement, so go ahead and create your users table. Log into MySQL using your command-line tool, the web form you built earlier, or another web tool like phpMyAdmin, and create the table. You’re about to need it.
WARNING
You may need to DROP a previous version of the table. You can simply use DROP TABLE users; if you get an error trying to create the table. That should clear out any existing version of the table you’ve got. And be sure you’re in the right database when you run your CREATE statement.
Saving a User’s Information
You’ve had a table before, and now you’ve got a version of the users table that’s a little sturdier, with AUTO_INCREMENT and validation of values in a few key fields. And your web form grabs exactly the information you need to stuff into that table. All that’s left is tying these things together with PHP, and you have almost everything you need for that, too.
You can start with a new script, or use your old version of getFormInfo.php as a starting point. Either way, your first task is to get the user’s entered information and do a little text manipulation to get the values just like you want:
<?php
$first_name = trim($_REQUEST['first_name']);
$last_name = trim($_REQUEST['last_name']);
$email = trim($_REQUEST['email']);
$facebook_url = str_replace("facebook.org", "facebook.com", trim($_
REQUEST['facebook_url']));
$position = strpos($facebook_url, "facebook.com");
if ($position === false) {
$facebook_url = "http://www.facebook.com/" . $facebook_url;
}
$twitter_handle = trim($_REQUEST['twitter_handle']);
$twitter_url = "http://www.twitter.com/";
$position = strpos($twitter_handle, "@");
if ($position === false) {
$twitter_url = $twitter_url . $twitter_handle;
} else {
$twitter_url = $twitter_url . substr($twitter_handle, $position + 1);
}
?>
Call this script create_user.php, and save it in your scripts/ directory, either in your site root or under your ch06/ examples directory. You should also update the action in your create_user.html’s form to submit to this newly named script.
You’ve written this code before, and because you haven’t changed your form, it still works. Now you just need to update it so it stores this information in your new users table.
NOTE
For some extra credit, see if you can convert this script, create_user.php, to use regular expressions instead of the strpos function to update these variables. If you think you’ve got things into great shape using regular expressions, tweet a link to your code to @missingmanuals and see what cool swag you might win.
Building Your SQL Query
First, you can use your existing database connection script to make connecting easy:
<?php
require '../../scripts/database_connection.php';
// Get the user's information from the request into variables
?>
WARNING
You may have some echo statements left in database_connection.php from an earlier version of the examples. If you do, remove them now so they won’t disrupt the seamless experience you’ll be giving your users.
With a database connection ready for use, you’ve got to turn all that information into the INSERT statement you want, so you can drop the information into your database.
Rather than just diving into your code, though, start with a sample statement. For example, pick a set of random values (maybe your own), and build the SQL to do what you want:
INSERT INTO users (first_name,
last_name,
email,
facebook_url,
twitter_handle)
VALUES ("Brett",
"McLaughlin",
"brett.m@me.com",
"http://www.facebook.com/bdmclaughlin",
"@bdmclaughlin");
NOTE
You can use your MySQL tools to test this SQL until it works, and is formatted just right.
This statement now becomes a sort of template: you basically want this statement, but you need to replace your sample values with your user’s request information. Since you’ve already got those values, this task isn’t too hard:
$insert_sql = "INSERT INTO users (first_name, last_name, " .
"email, facebook_url, twitter_handle) " .
"VALUES ('{$first_name}', '{$last_name}', '{$email}', " .
"'{$facebook_url}', '{$twitter_handle}');";
NOTE
You may not need to break this code up into as many separate lines, connected by the periods, as was necessary to fit it on this page.
In this example, you’re creating a new string that has the SQL query. You can then pass that query to mysql_query, and run against your database. The one gotcha here is that you must make sure each value you send to the database and that will go into a text field in the users table is surrounded by quotes. Using single quotes lets you use double quotes around the entire query, and to use curly braces ({ }) to drop your variables right into the query string (Printing Out Your SQL Results).
Inserting a User
Now it’s time add a user to your database, and it’s the easiest (and often the most fun) line of SQL-invoking PHP to write:
<?php
// Handle user request
$insert_sql = "INSERT INTO users (first_name, last_name, email, facebook_url,
twitter_handle) " .
"VALUES ('{$first_name}', '{$last_name}', '{$email}', " .
"'{$facebook_url}', '{$twitter_handle}');";
// Insert the user into the database
mysql_query($insert_sql);
?>
Great! You’re not quite done, though. You’ve got to account for the possibility of an error. For example, what if you forgot to add the users table first? What if you have a users table, but without a facebook_url column, or a misnamed or misspelled column?
There’s really a lot of work to do when it comes to error reporting, so for now, take a really simple (and probably way too simple) approach:
<?php
// Handle user request
$insert_sql = "INSERT INTO users (first_name, last_name, email, facebook_url,
twitter_handle) " .
"VALUES ('{$first_name}', '{$last_name}', '{$email}', " .
"'{$facebook_url}', '{$twitter_handle}');";
// Insert the user into the database
mysql_query($insert_sql)
or die(mysql_error());
?>
Using the die method (Connecting to Your Database (Again)) isn’t ideal, but it’s at least functional, and at least gives you some kind of report on error.
At this point, you can try out your page, albeit a little clumsily. So visit your web page, and fill out some sample values, as in Figure 6-4.
Figure 6-4. By now, you’re probably getting pretty tired of entering users. That’s good—you’re finally almost to the point where once you enter a user, that user is saved in the database. In fact, that happens here; now you just need a way to show that something happened, and deal with errors when they occur.
Now submit your page, and your new code will run. It will construct a SQL statement using your values, connect to the database, and insert the data using mysql_query. Hopefully, your die statement won’t get run.
Assuming you don’t get an error, you’ll get almost nothing back. That’s rather disappointing, but something did happen—especially if you didn’t get an error message.
NOTE
If you still have the HTML section of getFormInfo.php copied into create_user.php, you might get back some output from your form submission.
To find out what happened in your database, fire up a SQL tool and enter this query:
SELECT user_id, first_name, last_name
FROM users;
You should get back something like this:
+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
| 1 | C. J. | Wilson |
+---------+------------+-----------+
1 row in set (0.00 sec)
If you’re using phpMyAdmin, you can browse to your users table and check out any data that might be inside of it, as shown in Figure 6-5.
Figure 6-5. You can see here that the entry in users has not only the data pulled from the web form, but also an auto-generated (and auto incremented) id: in this case, 1. As you have more and more users, that number will continue to increment, although you can’t count on it being sequential.
POWER USERS’ CLINIC: NAME FOLLOWS FUNCTION
When you’ve got only a few web pages here and there, names are really not that big of a deal. Whether you name a page getFormInfo.html or create_user.html is pretty close to irrelevant; you can see all your files in a single directory listing or window of your FTP client.
But in even medium-sized web apps, you’ll have a lot more files than that. In fact, if you start to do the testing you should be doing, you can easily have hundreds of files. At that point, your names really need to be meaningful.
But there’s more to meaning than just description. Many of your forms and scripts will pertain directly to a single table in your database, and in fact doing one specific thing with regard to that table, like creating a user via the users table. In these cases, you make it really easy on yourself and others who’ll work on your code by naming your files according to function. So while your form may get a user’s social networking information, it ultimately creates a user, and create_user.php is a descriptive, simple, clear name.
On top of all that, you’ll soon be learning about CRUD: the idea that for any type of information, like a user, you CReate, Update, and Delete that information. Mapping your HTML pages and scripts to those basic actions (create_user, update_user, and so on) really helps you see what you have…and what you don’t.
A First Pass at Confirmation
At this point, you’ve got a user (or as many as you created via your web form), but your user—the person using your web application—sees nothing but a blank screen. Time provide some feedback so your users know what’s going on after they complete the form.
As a starting point, you can go back to the code from your older script, getFormInfo.php, from Access Request Parameters Directly:
<?php
// Get the user's information from the request array
// Connect to the database and insert the user
?>
<html>
<head>
<link href="../../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">Example 6-1</div>
<div id="content">
<p>Here's a record of what information you submitted:</p>
<p>
Name: <?php echo $first_name . " " . $last_name; ?><br />
E-Mail Address: <?php echo $email; ?><br />
<a href="<?php echo $facebook_url; ?>">Your Facebook page</a>
<br />
<a href="<?php echo $twitter_url; ?>">Check out your Twitter feed</a>
<br />
</p>
</div>
<div id="footer"></div>
</body>
</html>
This page is better than nothing, but you can see some things to fix right away. First, you’re not printing the user’s Twitter handle; you’re printing out the URL to that handle. While that’s handier for clicking, it doesn’t represent what was entered into the database. So you’ve got a tough choice:
§ You can print what was entered into the database, the value in $twitter_handle. That’s what was inserted, but it doesn’t have much value in a web page, and it’s simply letting your users know what’s in your database. Do your users really care about your database structure?
§ You can print the URL, which is better for clicking, but doesn’t directly connect to what’s in the database. It’s a modification of the database value, which is OK, but might not be appropriate right on the heels of a form that’s explicitly focused upon adding a user to the database.
Now, this point is rather trivial when you consider that it’s only a Twitter handle. But the same issue comes up with whether you show the first and last names, or combine them together as this code does now:
Name: <?php echo $first_name . " " . $last_name; ?><br />
You can see the greater issue here: what exactly do you show your users? Do you show them the literal values as they’re stored in the database, or do you massage your values so they’re a little more readable for humans?
Users Are Users, Not Programmers
As the previous example suggests, you always want to show your users things that make sense to them. Very rarely will your users care about the columns in your database, or the value of the primary key, or whether you store their Twitter handle with or without the @ sign. Focus on what your users want to see, not what your database literally contains.
But there’s something else going on here: what’s the source for the information you’re showing? Implied in this idea of showing a user what makes sense to them is the idea that you, the wise programmer, take information from the database, work with it to get it into the right format, and then show that massaged information to the user.
But in this first pass at a confirmation, are you showing what’s in the database? Not at all; you’re just spitting back out what the user gave you. What if something did happen when that information was inserted into your database table? You’d never know it. By showing the user their own information, you could be masking what really got dropped into the database.
So what do you do? You want to show users something that makes sense to them, but you also want to show those values based on the database, rather than just regurgitating a form, since that doesn’t show any problems in the database.
Well, hopefully, you do both! Suppose you had a way to pull the user’s information from the database, perhaps using a SQL SELECT, and then based upon that information—information from the database, problems or not—construct something the user can see and read and that makes sense.
One solution would be, after inserting the user, to reload that same information, for example like this:
<?php
// Get the user's information from the request array
// Connect to the database and insert the user
$get_user_query = "SELECT * FROM USERS WHERE ..."
mysql_query($get_user_query);
// Load this information and ready it for display in the HTML output
?>
<!-- HTML output -->
WARNING
The $get_user_query in this code is intentionally incomplete. Those three dots won’t really work; you’d need to include a WHERE piece that locates the user that was just added.
This code gets you the user from the database, and it still lets you modify those values as needed for good human-readable display. You still have to figure out how to find the particular user that just got inserted, but that’s something you’ll learn how to handle later in this chapter.
With this code, you do a bunch of text manipulation on the request information, and then you need to process it again with the response from the database. But is that the best way to go about it?
Not really. Think about your application as a whole: Is there any other place you might want to display a user? Absolutely—every good application has a place where a user can check out her own profile. To provide that functionality, you would need to take the code in the back part ofcreate_user.php and then copy it into a show_user.php script later. But that’s not good; remember, you really, really, really don’t want the same code in more than one place. That’s actually why you’ve got the very cool database_connection.php script you can use over and over.
What you really need is another script, one that shows user information. That way, you can simply throw users from create_user.php, which creates users, to this new script, and let it figure out what to do in terms of a response. So leave create_user.php incomplete for now, and you can come back and fix it up later.
Show Me the User
So here’s the task: you need a page that shows a user’s information, in a way that makes sense to the user. So this page is going to pull information from the users table, but it’s not a form; there’s no need (at least, not yet) to do anything but display information.
Now, you could dive right into your PHP, but most of the work here isn’t code; it’s getting a good user profile page built. So save yourself some PHP madness, and start with HTML.
And as a bonus, most web servers are configured to take a request for a file ending in .php and create HTML output, which is handed to a user’s browser. So you can create HTML, drop it into a file ending in .php, and when you start adding actual PHP, you’re ready to go. Your web server will send the HTML in that file to a requesting web browser, and your user’s (or you) see HTML output.
Mocking Up a User Profile Page
Figure 6-6 shows a pretty solid-looking profile page. It shows the basics of each user’s contact information, as well as some helpful additions: a short bio and a picture of the user.
Figure 6-6. Sometimes the best PHP doesn’t begin with PHP at all. Creating HTML pages is work, and often involves lots of tweaking, not to mention all the rules in your CSS you’ll need to create. By starting with a plain old HTML page, you can get the look and feel of things just right. Then, when you’re ready to start writing your PHP, you don’t have much HTML work left; you can just drop in your database values in the right spots, and know your page will turn out great.
Here’s the HTML for this page; with CSS, it stays pretty simple.
<html>
<head>
<link href="../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Profile</div>
<div id="content">
<div class="user_profile">
<h1>C. J. Wilson</h1>
<p>
<img src="images/cj_wilson.jpg" class="user_pic" />
C. J. Wilson is the Texas Rangers ace pitcher. After years of
relief pitching, C. J. debuted as a Rangers starter in 2010, and
went on to become the staff's ace in 2011. He's a lefty, known
for his strong opinions, those thick ropy necklaces, and a wicked
set of stuff. </p>
<p>C. J. is into auto racing as much as baseball, and would rather
be on safari in South Africa that sitting around do nothing at home.
He's also been known to do some pretty wacky bits to get to rub
shoulders with Carlton Cuse and Damon Lindelof, the writers
and masterminds behind the hit TV show LOST.</p>
<p class="contact_info">Get in touch with C. J.:</p>
<ul>
<li>...by emailing him at
<a href="wilson@texasrangers.com">wilson@texasrangers.com</a></li>
<li>...by
<a href="http://www.facebook.com/pages/CJ-Wilson/127083957307281">
checking them out on Facebook</a></li>
<li>...by <a href="http://www.twitter.com/str8edgeracer">following
them on Twitter</a></li>
</ul>
</div>
</div>
<div id="footer"></div>
</body>
</html>
NOTE
The bio and picture here are new, and not things you should already have in your users table. They’re just nice touches for a user’s profile page. Just a name and a few links for email and Twitter was pretty sparse.
Don’t worry, though. You’ll be adding a profile picture and bio to your database soon, and then this page really will be something your app can display.
Now, even though this page is pretty straightforward, it’s really even simpler than this. Go ahead and imagine (or type) this page not with placeholder text, but with variables in the place of the dummy text. So wherever the user’s first name goes, simply envision $first_name, and then$last_name, $email, and so on. The result is pretty clean:
<html>
<head>
<link href="../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Profile</div>
<div id="content">
<div class="user_profile">
<h1>$first_name $last_name</h1>
<p><img src="$user_image" class="user_pic" />
$bio</p>
<p class="contact_info">Get in touch with $first_name:</p>
<ul>
<li>...by emailing them at
<a href="$email">$email</a></li>
<li>...by
<a href="$facebook_url">checking them out on Facebook</a></li>
<li>...by <a href="$twitter_url">following them on Twitter</a></li>
</ul>
</div>
</div>
<div id="footer"></div>
</body>
</html>
WARNING
This example is helpful to think through, but it’s not valid HTML or PHP. So don’t try and view this code in a browser…you won’t get anything useful, and it certainly won’t work as a PHP script. Still, you should see that almost everything on the page really just represents information in the database, all in a very user-friendly format.
This example shows why it’s a good idea to focus on your HTML first, rather than diving right into PHP. When you start designing your page, you’ll often think of things you really need—like a bio ($bio) and a picture ($user_image), neither of which your users table has yet.
So with this simple mockup, you’ve figured out several important things:
1. You’re missing some key information in your users table. You really need a bio, which is just a long chunk of text, and a way to load an image of the user.
2. Once you update your table, you’ve got to update your create_user.html and create_user.php form and code to let users enter that information, and then save the new information to your database.
3. Finally—and this is great news—with those changes, you can build a pretty nice looking user profile page.
So what do you do first? Well, the database is usually the centerpiece of things, so you need to update your users table.
Changing a Table’s Structure with ALTER
There are two pieces of information missing from users: a bio, and an image. For now, don’t worry about the image yet. That takes a little bit of work, and you can always drop a placeholder in and come back to that. But the bio…that’s pretty easy.
First, you need to change your table’s structure by adding a column. That’s not hard at all; the SQL ALTER command lets you do just that:
ALTER TABLE users
ADD bio varchar(1000);
WARNING
Be sure you type ALTER and not ALTAR; the first is a SQL command, and the second is a place to make sacrifices. Either way, ALTAR will definitely not get your table in the shape you want.
This command really is as simple as it looks. You give SQL the table to ALTER, and then tell it what alteration you want. In this case, you want to add a column, so you use ADD, give it the new column name, and a type.
Of course, there are implications here: is it okay for a user to leave a biography blank, or should the bio column be NOT NULL? (It’s probably okay if it’s left blank, so NOT NULL really isn’t required.) How in the world does information get into this column for new users? (Well, you need to update your create_user HTML web form, and the script that does the database work. That’s up next.) Can you alter a table any time you want? Yes—databases are nothing if not flexible.
FREQUENTLY ASKED QUESTION: WHAT HAPPENS TO THE OLD ROWS IN A TABLE WHEN A NEW COLUMN IS ADDED?
Although it’s easy to add a column to a database with ALTER, and it’s often relatively painless to update your forms to let your users get information into those columns (and show the results, if you’ve already got a show_user script), there’s something left that can be a pain: dealing with old data that suddenly has a new column.
Take the users table, and imagine it didn’t have one or two recent entries, but thousands of users from the last five years. Now, with your alteration, every one of those users has a glaring empty spot: their bio. Most databases happily leave the column blank, meaning you’ll get NULL every time you try and pull something out of the new bio column.
Getting NULL isn’t a big deal in this case. You could probably call user bios a “new feature,” throw together a press release, and spin the oversight as a brand-new version, improved and usable by a whole new generation of bio-loving potential users. Existing users can log in and add a bio, which they couldn’t do before.
Adding a new required column to a database can be awkward. Imagine the owners of a website deciding that using an email address as a username isn’t a great idea. They probably altered their tables, adding a username column, but had to make it NOT NULL. After all, what’s the point of a username without requiring that it exist, or you have users without one?
So now you have a legitimate problem: you now have tons of rows that are missing required data. So what do you do? Well, you can simply lock those users out, and the next time they try and access your site, build a mechanism that forces them to select a username. That’s pretty typical, and even expected in these security-conscious days of the Web. But what if that’s not tenable? Then you’re allowing all those rows to be in an invalid state until a user logs in.
If that’s a problem—and it often is—you may have to insert some sort of placeholder data into your table, like NEEDS_USERNAME, and simply query a user to see if that’s his username value when he comes back to your site. It’s not the most elegant solution, but it keeps your data valid. And that’s ultimately the big issue with using ALTER: you potentially end up with data in an invalid state for some amount of time, or you have to insert “fake” data for a time to keep things running, although you know that data can’t ultimately stay put. Neither solution is great, so you’ll simply have to choose the lesser of these two evils. (Or come up with something else altogether, and let us know by tweeting us at @missingmanuals. We’d love to hear what you come up with.)
NOTE
You’ll deal with the image later. There’s a lot to be said about image handling, and where to store images, so you can plan on having an image—and leaving a spot for it in your web page and script—and just know that you’ll add that in later.
Building Your Script: First Pass
So with the new bio column in users, and an HTML mockup, you’re ready to get down to the business of PHP. Go ahead and create a new script, and call it show_user.php. This script goes along nicely with create_user.php, and you can already imagine you’ll later add scripts likedelete_user.php and update_user.php to complete the package.
To begin, you actually don’t need any PHP in this script at all. Instead, just drop in your HTML. Then, as you did earlier, you can replace all the places where there will be data from the database with PHP variable names. Here’s what you should end up with:
<html>
<head>
<link href="../../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Profile</div>
<div id="content">
<div class="user_profile">
<h1>$first_name $last_name</h1>
<p><img src="$user_image" class="user_pic" />
$bio</p>
<p class="contact_info">Get in touch with $first_name:</p>
<ul>
<li>...by emailing them at
<a href="$email">$email</a></li>
<li>...by
<a href="$facebook_url">checking them out on Facebook</a></li>
<li>...by <a href="$twitter_url">following them on Twitter</a></li>
</ul>
</div>
</div>
<div id="footer"></div>
</body>
</html>
NOTE
Remember, save this with a .php extension, in your scripts/ directory. That might be ch06/scripts/ if you’re using the book’s structure, or just your website’s scripts/ directory, if you’re putting all your PHP from all the chapters in a single place.
Now there are definitely some things here that are a bit odd:
§ Where’s the PHP? There’s no <?php or ?> yet, and certainly no code.
§ Those variables are definitely PHP, not HTML. An HTML page won’t know what to do with them.
§ Where does the database interaction occur? There’s no SQL, no SELECT from the database, or anything.
§ Which user is to be loaded? How does the script know which user to load?
These are all the right questions to be asking, so if you came up with a few of these, you’re really getting your head around the big issues in PHP and web programming.
First, as to where the <?php and ?> tags are: they’re coming later, but that’s really incidental. You can give a file the .php extension, and still put nothing but HTML within that file. In fact, type the URL to your script into your browser, and see what happens; Figure 6-7 is about right.
Figure 6-7. Starting with the HTML another good testing technique. At this stage, you don’t have any real values in, so you can focus on how your page looks. Once you add PHP code, it’s easy to get focused on your database interaction and formatting the actual strings and values in your variables. When you prototype before you get to your database interaction, you can make sure things look just right, and then just tweak things one more time once you drop in a real value for each variable.
WARNING
If you get a page without any styling, you may need to update the link element in your page’s head section. By moving this into your scripts/ directory, the CSS is in a different relative location than when this was just an HTML page under your web root, or your ch06/ examples.
At this point, in show_user.php, there’s nothing but HTML inside the file, so your web server supplies that HTML to a user’s web browser. The result is a nice-looking web page. Of course, there’s still a handful of issues to deal with, like those variable names that are coming across as plain old text.
That’s pretty easy, though. You can simply surround each variable with <?php and ?>, which tells the browser, “Hey, treat this little bit as PHP.” Then, you’ll have to add echo because you want to output the value of the variable.
<html>
<head>
<link href="../../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Profile</div>
<div id="content">
<div class="user_profile">
<h1><?php echo "{$first_name} {$last_name}"; ?></h1>
<p><img src="<?php echo $user_image; ?>" class="user_pic" />
<?php echo $bio; ?></p>
<p class="contact_info">
Get in touch with <?php echo $first_name; ?>:
</p>
<ul>
<li>...by emailing them at
<a href="<?php echo $email; ?>"><?php echo $email; ?></a></li>
<li>...by
<a href="<?php echo $facebook_url; ?>">checking them out
on Facebook</a></li>
<li>...by <a href="<?php echo $twitter_url; ?>">following them
on Twitter</a></li>
</ul>
</div>
</div>
<div id="footer"></div>
</body>
</html>
There’s still a pretty obvious issue here: these variables have no values. You haven’t defined them, and if you try and access this page now, you’ll get very strange results (see Figure 6-8). But you’re slowly moving toward a useful script.
The biggest problem here is you really don’t know if this code works. Are there typos? Are there problems in the minimal PHP you have? It’s a pain to move on to your database code when you’re not sure that—even if you have the right values from the database—it’ll work properly.
Figure 6-8. Just looking at this web page, it’s a little hard to tell what’s going on. Look closely, though: every time your PHP ran, it echoed out the value of a variable that didn’t exist. PHP is a little loose here, and simply throws out nothing. That’s bad grammar, but it’s just what PHP does: echoes a “nothing string,” which is just whitespace. Once you put values in those variables, things will look much better.
One easy way to test before getting much further is to simply create a small section of PHP before the HTML. In the <?php section, assign to each variable the sort of value you’d get from the database.
<?php
$first_name = "C. J.";
$last_name = "Wilson";
$user_image = "/not/yet/implemented.jpg";
$bio = "C. J. Wilson is the Texas Rangers ace pitcher. After years of
relief pitching, C. J. debuted as a Rangers starter in 2010, and
went on to become the staff's ace in 2011. He's a lefty, known
for his strong opinions, those thick ropy necklaces, and a wicked
set of stuff. </p>
<p>C. J. is into auto racing as much as baseball, and would rather
be on safari in South Africa that sitting around do nothing at home.
He's also been known to do some pretty wacky bits to get to rub
shoulders with Carlton Cuse and Damon Lindelof, the writers
and masterminds behind the hit TV show LOST.";
$email = "wilson@texasrangers.com";
$facebook_url = "http://www.facebook.com/pages/CJ-Wilson/127083957307281";
$twitter_url = "http://www.twitter.com/str8edgeracer";
?>
<html>
<!-- All your HTML and inline PHP -->
</html>
Now you can actually visit your page, and see some useful results, as you see in Figure 6-9. You can see that your code is actually working, and now all you have left is to fill those variables with real values, and then figure out which user to look up in the first place.
Figure 6-9. This page is still as much prototype and mock-up as it is working code. All the same, this is a good way to work up to a full script: piece by piece, making sure each step works independently of everything else that is going to be added later.
SELECT a User From Your Database
You’ve got your variables, and you’ve got your HTML. Now you need to get your user. That’s pretty easy, as you’ve already used SELECT a few times:
SELECT *
FROM users;
In fact, you can run that command now on your database, and get back all the rows you have:
+---------+------------+-----------+----------------+--------------
-------------------------------------------+----------------+------+
| user_id | first_name | last_name | email | facebook_url
| twitter_handle | bio |
+---------+------------+-----------+----------------+--------------
-------------------------------------------+----------------+------+
| 1 | C. J. | Wilson | brett.m@me.com | http://www.facebook.com/
pages/CJ-Wilson/127083957307281 | @str8edgeracer
| NULL |
+---------+------------+-----------+----------------+-------------------
--------------------------------------+----------------+------+
1 row in set (0.03 sec)
NOTE
This output is intentionally left as a bit of a mess, because it’s probably just what you see, too. The output of your SELECT is all the rows in the table, which won’t fit in a normal command-line prompt, let alone the width of this page.
In this case, there’s just a single user. So once you get this user, you can pull out the first_name and last_name values, the email value, and so on, and stuff them in $first_name, $last_name, and the rest of your variables.
But there’s still one big question: how do you know which user to get? Obviously, in the table output here, there’s just a single user. But what about when your new app is a hit and you have hundreds, or thousands, or even hundreds of thousands, of users? You need to select just one of thoseusers for show_user.php to display…and how do you figure out that one?
To find out, think about how people will get to show_user.php. Here are a few:
§ They get sent to this page after they’ve created a new user with create_user.html and create_user.php.
§ They log in to your application and click a link like “My Profile” or “Update My Information.”
§ They select a particular user from a list of users, maybe all the users in the system, or all their friends, or all the users they’re watching or following.
What’s common in all these situations? Nobody ever goes to show_user.php directly by typing in a URL. In each case, they’re selecting a user, or creating a user, or logging in as a user, and then some link is taking them to show_user.php.
As a result, in every reasonable situation, your code sends the user to show_user.php, and so your code is in control. If you need to, say, send some information to show_user.php, you can do it. All you do is send the unique ID of the user that show_user.php should load from the database, and display it.
So revisit those same scenarios again:
§ A user is created by create_user.php, and the ID of that new user is handed off, along with your application user, to show_user.php.
§ Clicking “My Profile” or “Update My Information” passes along the current logged-in user’s ID to show_user.php.
§ Selecting a user from a list—regardless of what’s in that list—results in a link to show_user.php being followed, and the selected user’s ID being passed to show_user.php at the same time.
In each case, your show_user.php script can get the passed-in ID, then look up the user by the passed-in ID, and finally display that user.
The beauty of this solution is not just that it’s possible, because you have control over all the ways your users might get to show_user.php. It’s also perfect because you can pass in the ID of the user to show as part of the request, and you’ve already pulled things out of the request before, using$_REQUEST.
Add this line to show_user.php now:
<?php
$user_id = $_REQUEST['user_id'];
// Code to assign values to the page variables
?>
<html>
<!-- All your HTML and inline PHP -->
</html>
Nothing new here; the only thing that’s different from what you’ve done before is that you’re pulling a request parameter with a new name: user_id.
Now you can finally add a WHERE clause to SELECT:
SELECT *
FROM users
WHERE user_id = $user_id;
So far, you’ve seen a few WHERE clauses (like the one on USE a Database), and they do just what you might expect: narrow a set of results based upon a match, or some other restriction. In this case, you’re saying, “Give me everything (*) from the users table, but only for the records (rows) that have a user_id of the value in $user_id.”
So if your sample user has a user_id of 1, and $user_id is 1, you’ll get that sample user. If you don’t have any rows that have a user_id of 1, then you’ll get nothing back from the SELECT. And here’s what’s really cool: you made user_id a primary key (IDs and Primary Keys are Good Bedfellows) with PRIMARY KEY, which means that you’ll never have more than one result returned. So you don’t have to see how many values are returned, or do anything special to handle one row or multiple rows. You’ll either get nothing back because there was no match, or you’ll get just a single row back.
Put all this together, and you can make some really important additions to show_user.php:
<?php
require '../../scripts/database_connection.php';
// Get the user ID of the user to show
$user_id = $_REQUEST['user_id'];
// Build the SELECT statement
$select_query = "SELECT * FROM users WHERE user_id = " . $user_id;
// Run the query
$result = mysql_query($select_query);
// Assign values to variables
?>
<html>
<!-- All your HTML and inline PHP -->
</html>
This now connects to your database, builds the SELECT statement from the passed-in user_id request parameter, and runs the query. And all that’s left is the one entirely new piece to this script: running through the actual result from a query, and getting information from that result.
Pulling Values From a SQL Query Result
You’ve got a $result variable, but what is that? You probably remember that it’s a resource, a special type of variable that holds a reference to more information. So you can pass that resource to other PHP functions, and use it to get more information.
In the case of a SELECT query, what you really want are all the actual rows the query returned, and then for each row, you want the different values. That’s exactly what you can use a resource for, so you’re all set to finish off show_user.php and start accepting requests.
You begin by making sure that $result has a value; this is equivalent to making sure that $result is not false, which is returned when there’s a problem with your SQL:
// Run the query
$result = mysql_query($select_query);
if ($result) {
// Get the query result rows using $result
} else {
die("Error locating user with ID {$user_id}");
}
This if also (marginally) handles errors. If $result is false, something went wrong, which presumably means the user you were searching for using $user_id doesn’t exist, or there was a problem finding that user. This code as it stands doesn’t format the error very nicely, and in fact gives you little information about what actually caused the problem. That’s okay, though; you’ll fix up your error handling pretty soon, so this if is a decent short-term solution.
Now, you need a new PHP function: mysql_fetch_array. This function takes in a resource from a previously run SQL query. That’s exactly what you have in $result:
if ($result) {
$row = mysql_fetch_array($result);
// Break up the row into its different fields and assign to variables
} else {
die("Error locating user with ID {$user_id}");
}
Here’s where things get a little odd. Notice that your script, using the code above, stores the result from mysql_fetch_array in $row. That means mysql_fetch_array returns a single row from your SQL query—and that’s correct.
But the actual name of the function suggests something else: that an array is returned (it’s mysql_fetch_array, not mysql_fetch_row, isn’t it?). So which is it? It’s both. mysql_fetch_array does return an array; but it returns an array for a single row of the query associated with the result you pass into it.
So for mysql_fetch_array($result), you’re going to get back a single row of results, but that the way that row is returned is in the form of an array.
NOTE
In case you’re wondering, you certainly can get every row of results returned from a query, not just the first result row. You simply keep calling mysql_fetch_array, over and over, and it keeps returning the next row from the results. Eventually, mysql_fetch_array will return false, which means there are no more results.
Before long, you’ll use mysql_fetch_array like this yourself, and it’ll all make perfect sense. For now, know that every time you call this function, you’ll get one row of results (or false if there are no rows left to return), and that row is an array of values.
Arrays are no problem for you, so getting back an array in $row is good news. In fact, $row is just like another array you know, the $_REQUEST array (The $_REQUEST Variable). And just like $_REQUEST, you have not just a list of values, but values that are keyed based on a name.
So when a request came in with a parameter named “first_name”, you pulled the value for that parameter with $_REQUEST[‘first_name’]. The exact same principle applies to $row. You can give it the name of a column returned in your SQL query, and you’ll get the value for that column, in the specific row you’re examining.
So once you’ve got $row, you can just grab all the columns you want, and stuff them into some variables:
// Run the query
$result = mysql_query($select_query);
if ($result) {
$row = mysql_fetch_array($result);
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$bio = $row['bio'];
$email = $row['email'];
$facebook_url = $row['facebook_url'];
$twitter_handle = $row['twitter_handle'];
// Turn $twitter_handle into a URL
$twitter_url = "http://www.twitter.com/" .
substr($twitter_handle, $position + 1);
// To be added later
$user_image = "/not/yet/implemented.jpg";
} else {
die("Error locating user with ID {$user_id}");
}
NOTE
At the end of this if, you should add in the preceding code that creates a URL for the Twitter handle. It’s the same code you used on Trimming and Replacing Text to build this URL, although back then you weren’t getting the user’s Twitter handle from a database.
Also add the code that fills in $user_image with a dummy value until you come back later to fix the user’s image for real. You could also use a stock image for when there’s no picture, like this:
$user_image = "../../images/missing_user.png";
There’s a sample of an image like this in the downloadable examples for this chapter (page xvii) if you want to go this route for now.
At this point, you’ve got a fully functional script! In fact, other than figuring out how to use the $result resource with mysql_fetch_array, all this is pretty normal stuff and shouldn’t be any problem for you at all.
Getting a User ID into show_user.php
At this point, you have to get a user ID into your script, so it can use that ID to look up a user, get his information, and display it. But before you start hacking all your other scripts together, it would be really nice to make sure show_user.php works. Who wants to spend a bunch of time on other scripts if there’s a problem somewhere in show_user.php? That’s no good.
Fortunately, there’s an easy way to test your script. The $_REQUEST array has all the information passed into your script through its request—including extra information passed through the request URL itself. Now, remember, this isn’t the ordinary way you’d either get information intoshow_user.php, or even access show_user.php in the first place. Instead, scripts like create_user.php or maybe a “My Profile” button would get your users to this script.
But for now, you’re just testing. So why not go directly to the page, with a URL like yellowtagmedia.com/phpMM/ch06/scripts/show_user.php? And as long as you’re there, you can feed that script request data with request parameters on the URL itself. You can simply add these to the URL, after a ?. So the format is basically this:
[protocol]://[domain-name]/[location-of-file]?[request-paramaters]
So you might say mysite.com/scripts/show_user.php?first_name=Lance. Now you can grab $_REQUEST[‘first_name’], and you’d get back “Lance.” You can stack these up, too; just separate the parameters with &. So you can go further and do mysite.com/scripts/show_user.php?first_name=Lance&last_name=McCollum.
NOTE
More formally, the file name (show_user.php) is the path. The information after that (?first_name=Lance&last_name=McCollum) is the query string.
You can see what to do next. Add the user ID of the user you created much earlier (or one of the users, if you inserted more than one), and try out show_user.php, with a URL like yellowtagmedia.com/phpMM/ch06/scripts/show_user.php?user_id=1.
You should get something back like Figure 6-10, which is a validation of all the work you’ve put into SQL and show_user.php.
Figure 6-10. There’s always room for error when a form or web page sends information to another page or a script. Anytime you can test just a single script in isolation, you’re going a long way toward removing potential errors and hard-to-find bugs that only pop up when two web pages talk to each other, as compared to when each is operating alone.
WARNING
Request parameters are case-sensitive, as is your PHP. So asking for $_REQUEST[‘user_id’] will not match a request parameter named USER_ID or user_Id. Be careful your uppercase and lowercase letters all match up.
At this point, you’ve done just about everything you can to make sure show_user.php is going to behave. It’s missing some information, like the user’s pic and bio, but you can deal with the picture later, and the bio through updating create_user.php. Other than that, it’s time to leaveshow_user.php alone and revisit the script that needs to send users to this one.
NOTE
There is probably one more thing you could do: manually INSERT a user with a bio into your users table, and then try out show_user.php again. You might want to do that now, and really verify that show_user.php is just like you want it. You’ll test that same bit of functionality in a little while once you update create_user.php, but there’s really no such thing as too much testing.
DESIGN TIME: IS A SCRIPTS/ DIRECTORY A GOOD IDEA?
Storing all your scripts in a directory (or, technically, a subdirectory) called scripts/ is a practice that largely dates back to older programming languages like Perl and CGI (something called the Common Gateway Interface, a way of calling external programs like server-side scripts). In those days, you’d have a really firm separation between client-side programs or views, and server-side programs. So a script never really did anything that resulted directly in a web page being displayed; they were just programs called by other processes.
But PHP really blurs the line between what’s a script and what’s a viewable page. The show_user.php script is actually a lot more HTML than it is PHP, and it’s going to be common for a user to actually hit show_user.php directly. In other words, PHP is more than just a way to write scripts to which your forms submit behind the scenes. There will be lots of times where users click a link to a PHP page, rather than an HTML page, or even type in a PHP script’s URL in their browser.
In fact, there are some pretty popular pieces of software that essentially handle all HTML within PHP. Wordpress (online at wordpress.org and wordpress.com) is a hugely popular blogging and content management system that is built on PHP. In that system, the actual home page of your site is index.php, not index.html.
So at that point, does a scripts/ directory make sense? No, it really doesn’t. Your users don’t care if they’re getting a page from an HTML file or a PHP script, as long as it looks and acts the way they expect. And adding a scripts/ directory actually increases what your users have to know about your system, rather than making things more transparent.
So beginning in Chapter 7, this change will kick into gear. It’s good that you’ve been thinking about the difference between what you’ve been doing as a web page creator with HTML, CSS, and JavaScript, and your new PHP skills. But now that you’ve moved beyond just submitting forms to PHP, it’s time to blur the lines even further, and let many of your PHP scripts live alongside your HTML.
Redirection and Revisitation of Creating Users
All the changes you’ve made so far are great, but you’re not done. You have a new bio column, but no place for users to enter that information when they sign up. You need create_user.php to deal with that information when it comes in from your sign-up form. And then there’s getting a user from the sign-up form to show_user.php—and passing along the newly created user’s ID as well. It seems like a lot, but with what you know, it’ll be a breeze.
Updating Your User Signup Form
The first change is one of the easiest. Open up your original create_user.html page, and add a new form field so that your users can easily enter in a biography. Leave plenty of space: have you seen how much information people write about themselves on Facebook these days?
<html>
<head>
<link href="../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Signup</div>
<div id="content">
<h1>Join the Missing Manual (Digital) Social Club</h1>
<p>Please enter your online connections below:</p>
<form action="scripts/create_user.php" method="POST">
<fieldset>
<label for="first_name">First Name:</label>
<input type="text" name="first_name" size="20" /><br />
<label for="last_name">Last Name:</label>
<input type="text" name="last_name" size="20" /><br />
<label for="email">E-Mail Address:</label>
<input type="text" name="email" size="50" /><br />
<label for="facebook_url">Facebook URL:</label>
<input type="text" name="facebook_url" size="50" /><br />
<label for="twitter_handle">Twitter Handle:</label>
<input type="text" name="twitter_handle" size="20" /><br />
<label for="bio">Bio:</label>
<textarea name="bio" cols="40" rows="10"></textarea>
</fieldset>
<br />
<fieldset class="center">
<input type="submit" value="Join the Club" />
<input type="reset" value="Clear and Restart" />
</fieldset>
</form>
</div>
<div id="footer"></div>
</body>
</html>
While you’re at it, you may as well let your users pick an image to use for their profile. You won’t write any code in create_user.php to handle this, but it’s coming soon, and doing it now will save you a trip back to create_user.html when you’re ready to add images.
<html>
<!-- head section -->
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Signup</div>
<div id="content">
<h1>Join the Missing Manual (Digital) Social Club</h1>
<p>Please enter your online connections below:</p>
<form action="scripts/create_user.php" method="POST"
enctype="multipart/form-data">
<fieldset>
<!-- Other fields -->
<label for="user_pic">Upload a picture:</label>
<input type="file" name="user_pic" size="30" />
</fieldset>
<!-- Buttons for submission and resetting the form -->
</body>
</html>
This HTML may look a little strange if you’ve never set things up to handle uploads before. You have to change the form tag a bit, as now you’re actually uploading a file to a server, from your user’s machine. So add the new enctype attribute with the value multipart/form-data. That addition lets any scripts receiving this form’s input to expect more than just the values in the input fields, like the name of the file. A form like this also submits the data associated with those fields; in this case, that’s the file that the user selects to upload.
Then you add a new input, of type file, which lets the user browse their hard drive, select a file, and upload it. This turns out to be almost boilerplate code, though, meaning that it’s not specific to this use. Every time you let your users upload a file, you need to make this set of changes.
NOTE
If you want to start thinking ahead, the million-dollar question is, “Where do you store this image?” You must let the user upload the image; that’s required for your scripts and code to work with it. But do you save the image on your server’s file system, and reference it using a field in your users table? Or do you actually store the image itself in your database? Hot opinions are held here, and you’ll develop one of your own in just a few chapters.
Save your changes here, and try hitting your form in a browser. You should get something like Figure 6-11; your updated form.
Figure 6-11. Nothing about your form looks different when you add a new file input element or change your form to submit multipart data. Behind the scenes, though, your form now is sending not just the name that ends up in each form field, but anything that’s connected to that name—like the file that’s actually selected when your user clicks the Browse button.
Go ahead and fill out values if you like, although without changes to create_user.php, you’ll get a new user without their bio.
Updating Your User Creation Script
You can guess what you need to do to create_user.php. Simply grab the new bio request variable, add it to your INSERT statement, and you’re good to go:
<?php
require '../../scripts/database_connection.php';
$first_name = trim($_REQUEST['first_name']);
$last_name = trim($_REQUEST['last_name']);
$email = trim($_REQUEST['email']);
$bio = trim($_REQUEST['bio']);
// And other request variables follow...
$insert_sql =
"INSERT INTO users (first_name, last_name, email, bio,
facebook_url, twitter_handle)
" .
"VALUES ('{$first_name}', '{$last_name}',
'{$email}', '{$bio}' " .
"'{$facebook_url}', '{$twitter_handle}');";
// Insert the user into the database
mysql_query($insert_sql);
?>
That’s it. Now you can submit your new form, and get a new column—bio—with values happily dropped into your database.
WARNING
Be sure you’ve run the ALTER TABLE statement that adds the bio column to your users table before trying this out.
You can try this out, by filling out create_user.html, and hitting Submit. Then try this SELECT statement:
SELECT first_name, last_name, bio
FROM users;
Your result should speak for itself:
| first_name | last_name | bio
| C. J. | Wilson |NULL |
| Peter | Gabriel |Peter co-founded the group Genesis in
1966 at school. He went on to make 7 albums with Genesis. He left
in 1975, but returned to music a year later, since when he has made
11 solo albums. His film soundtrack works include Birdy (1984); The Last Temp-
tation of Christ (1989); Rabbit Proof Fence (2002).
NOTE
Your result will be different, based on what bio you entered for your test user. You can also see that old users—in this case, the C. J. Wilson entry—has NULL for the bio, since that user was created before there even was a bio column.
Next, you need to redirect your user over to the show_user.php script, and then somehow get the ID of the user you just created into that script as well.
The first of these is easy:
<?php
// Everything else you've already done
// Insert the user into the database
mysql_query($insert_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php");
exit();
?>
The header function literally sends a raw HTTP (hypertext transfer protocol) header to your user’s browser. You don’t need to know particularly much about HTTP; just that it’s the “language” of web traffic. (That HTTP is the same http:// you put in front of most of your URLs in your browser’s address bar.) So you’re directly manipulating the location of your user’s page.
In this case, you’re changing the location from the current one to a new one: the show_user.php script. There are a few things that are critical to get this working correctly, though:
1. You must call header before any other output in your script. You can’t echo out anything, you can’t print out an <html> tag, or anything else. header goes first, or problems arise.
2. The location reference must be a URL, either relative or absolute. So you could put http://www.google.com as the location, or ../../scripts/database_connection.php, or in this case, a script in the same directory as this one, show_user.php.
These are simple rules but they’re also really important ones. Get them right, or expect header to fail miserably.
All that’s left now is that pesky user ID. You already know that mysql_query, which executes your INSERT, returns a resource, not a user ID. And the whole idea here was not to SELECT the user from the database, but leave that to show_user.php.
Making show_user.php get the user ID requires something that’s one step removed from your current PHP knowledge: an incredibly handy PHP function called mysql_insert_id. You don’t see this sort of function very often unless you’re looking for, say, a function to get the ID of the last rowINSERTed into a database table with an AUTO_INCREMENT column.
Yes, that’s the definition of mysql_insert_id. It’s built exactly to do what you want to do: get an ID, without any additional SELECTs or work.
While you can pass into mysql_insert_id a resource, it automatically uses the last opened resource, which is perfect. Just add this after your INSERT is called via myql_query, and it’ll automatically reference the resource returned from that call.
It returns ID of the user you want. You can even tag that onto the URL, just as when you were typing your URL manually:
<?php
// Everything else you've already done
// Insert the user into the database
mysql_query($insert_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . mysql_insert_id());
?>
That’s it. Add this to create_user.php, and you’re ready to try things out.
NOTE
You may be tempted to try something like this:
("Location: show_user.php?user_id={mysql_insert_id()}");
Unfortunately, that won’t work. While PHP is happy to insert variable values for variable names in curly braces, like this…
("Location: show_user.php?user_id={$user_id}");
…it won’t do the same for function calls.
Visit your user creation form and fill out some data. Submit the data, and you should be rewarded by not the output of create_user.php, but show_user.php, loading the user that was just created. Figure 6-12 shows what should be a fist-pumping moment.
Rounding Things Out with Regular Expressions (Again)
Your script is almost perfect. What’s the remaining problem? Well, that output looks pretty awful with all that text running together. Is there some way you can add in a little formatting?
What probably happened in the example shown in Figure 6-12 is that the user hit a bunch of Enters between lines. But those don’t show up in HTML. What you really need is a quick and easy way to replace those Enter key presses with HTML <p></p> tags.
What you need is a way to find certain characters—certain very specific characters—and replace them with certain other characters. You know how to do that, because you know that those Enters show up as \r or \n or some combination of the two, and you can use regular expressions to find and replace them.
Figure 6-12. Think about all that’s really going on here. Your user types and clicks, but behind the scenes, that form gets submitted to your script on the server. That script inserts data into a database. Then it tells the user’s browser to go visit another script, which asks the database for everything about a particular user. Finally, your user gets to see all this…instants later. This is a far cry from just HTML, CSS, and JavaScript. Welcome to web programming!
Using preg_match, update show_user.php to change Enter presses into HTML <p> tags:
<?php
// Database connection code
// SELECT the correct user
if ($result) {
$row = mysql_fetch_array($result);
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$bio = preg_replace("/[\r\n]+/", "</p><p>", $row['bio']);
$email = $row['email'];
$facebook_url = $row['facebook_url'];
$twitter_handle = $row['twitter_handle'];
// Build the Twitter URL
}
?>
// HTML output
NOTE
Be sure you use [\r\n]+, and not [\r\n]*. The * would match no occurrence, and you’d end up with </p><p> between every character in the user’s bio. Not so good…the + makes sure that \r or \n (or both) appear at least once before replacing them with </p><p>.
You can see why regular expressions are so powerful. You didn’t need lots of looping and searching, and you don’t have to figure out if the user entered \r or \n or \r\n based on their platform. You just plug in the right regular expression, and you’re off to the races.
All this put together should give you a version of show_user.php like this:
<?php
require '../../scripts/app_config.php';
require '../../scripts/database_connection.php';
// Get the user ID of the user to show
$user_id = $_REQUEST['user_id'];
// Build the SELECT statement
$select_query = "SELECT * FROM users WHERE user_id = " . $user_id;
// Run the query
$result = mysql_query($select_query);
if ($result) {
$row = mysql_fetch_array($result);
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$bio = preg_replace("/[\r\n]+/", "</p><p>", $row['bio']);
$email = $row['email'];
$facebook_url = $row['facebook_url'];
$twitter_handle = $row['twitter_handle'];
// Turn $twitter_handle into a URL
$twitter_url = "http://www.twitter.com/" .
substr($twitter_handle, $position + 1);
// To be added later
$user_image = "../../images/missing_user.png";
} else {
die("Error locating user with ID {$user_id}");
}
?>
<html>
<head>
<link href="../../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Profile</div>
<div id="content">
<div class="user_profile">
<h1><?php echo "{$first_name} {$last_name}"; ?></h1>
<p><img src="<?php echo $user_image; ?>" class="user_pic" />
<?php echo $bio; ?></p>
<p class="contact_info">
Get in touch with <?php echo $first_name; ?>:
</p>
<ul>
<li>...by emailing them at
<a href="<?php echo $email; ?>"><?php echo $email; ?></a></li>
<li>...by
<a href="<?php echo $facebook_url; ?>">checking them out
on Facebook</a></li>
<li>...by <a href="<?php echo $twitter_url; ?>">following them
on Twitter</a></li>
</ul>
</div>
</div>
<div id="footer"></div>
</body>
</html>
Try things out, and you’ll finally see not just your user’s information, but a nicely formatted biography, as in Figure 6-13.
Figure 6-13. Here’s another classic case how to be considerate to your users. Is it functionally correct to pull the user bio from the database and show it? Sure. Is it functionally correct to not insert weird HTML into the bio when you store it? Again, sure. But when you display that value, users don’t care what’s in your database. They care that it looks good.
FREQUENTLY ASKED QUESTION: DO MY FIELD NAMES, VARIABLE NAMES, AND TABLE COLUMN NAMES HAVE TO MATCH?
You may have noticed that there’s a continuous line from the name of a field in your HTML in create_user.html to your script, create_user.php, into other scripts like show_user.php, and then into your database table itself. So first_name is consistent in your HTML, PHP, and MySQL (and therefore your SQL, too). That’s not required; you can call a field firstName and call a variableuser_firstName and call a column first_name, and as long as you keep things straight, all your code will work just fine. So no, your names don’t all have to match.
But maybe there’s a better question: should your names match up across your HTML, PHP, and MySQL? Matching names give you consistency; you never have to think, “Now I know what I called that in my PHP, but what was the database column name again?”
Here’s the flipside, though: there are some fairly standardized conventions for naming variables in different programming languages and database structures. The Java language favors less underscores, and more capitalization. So firstName would be preferred over first_name; the same is true in C++, although PHP and languages like Ruby prefer underscores over capitalization. SQL definitely favors underscores.
What this boils down to is a sort of conditional rule of thumb: if you can be consistent without messing up the conventions of the language within which you’re programming, do it! Your code will easier to read, from the outermost HTML page to the innermost database table. Since PHP is one of the languages that likes underscores, use them, and keep things simple and consistent across your different pieces of your application.