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 was probably to make dynamic and interesting web applications. Unfortunately, you’ve not done much of that yet. You do have a few interesting forms, but even those are simple: take in some information; print it back out; accept a SQL query (and do that quite imperfectly). So, where are the web applications? Heck, where are the pages that are built dynamically using your user’s information?
Thankfully, you have everything you need to start building these kinds of web pages. You can get information from your users, store it in a database, and even do some basic data manipulation., All you need to do now is put it all together and create the basic web pages that most users expect: a place to enter their information, a place to look at their information, and in most cases, a place to look at all the related user’s information.
Revisiting a User’s Information
you built a form in which users can enter their basic social media profile: a Twitter handle a Facebook URL, and some basic contact information. As shown in Figure 7-1, it’s a perfectly good form: simple and easy to use.
There’s really no reason to change this form. However, the script that accepts its information is pretty lame. It does nothing more than manipulate some text and then send that text back (see Figure 7-2). It doesn’t even save the form’s information for later use. That’s where the work is: making the script do with the user’s information.
Getting from a simple form on the Web to a script that interacts with a database involves a surprising amount of work. You need to figure out, design, and create tables, interact with those tables, potentially deal with errors from your database, and so on
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. That script needs a table into which it can insert and store information. But, where’s the table? In a MySQL database, you need to create or set up tables for web access. Of course, the table itself needs structure. 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 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 7-1).Here’s basically what you’re collecting from your users right now
• First name
• Email address
• Facebook URL
• Twitter handle
• Last name
Each of these items are individual components that when combined describe a single “entity”-a user. What you need, therefore, is a table to store users, and for each user, you need to store a first name, last name, e-mail address, a Facebook URL, and a Twitter handle.
All you need to do now is to translate this into a SQL CREATE statement
CREATE TABLE users (
You might remember this SQL from ,but that was ages ago, when you had but a fragile understanding of databases. Now, you know exactly what is going to be dropped into this table: information from the web form that you already have.
Good Database Tables Have 10 Columns
Take a look at the first column created for this table: the user _id field. What exactly is that? Well. think about the most common thing you’ll do with databases. Is it ereating new entries in the table? Probably not. Honestly, if you think about how often you create a user ID or profile on the Web versus the number of times you log in to a site, you log in many, many more times .
In other words, you’re creating information once for every ten, or twenty, or maybe one hundred times that you’re ace that information. That’s a case where you’re looking up information; you’re searching for a user (usually yourself)
Of course, that then begs the question: How do you search for something? You can look things up by a last name and then find matching entries. Or, you can search by an email address or Twitter handle, which are supposed to be unique for each user. In fact, you’ve probably often had to create a user name that is unique (typically at great pain; who really takes all those normal user names and leaves you stuck with)
Databases are no different in that they need something for which to look. Moreover, databases work best when they can identify every individual row in a table by a unique piece of information. Putting it more accurately still, databases function better with numbers than with 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 numerical value for each row that uniquely, identifies that row. It identifies each user as separate from all others, so your database can locate it, every time
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) ensure that no two users are entered into the users table with the same user _id? Do you need yet another table just to keep up with the current count of users?
This isn’t a trivial problem, because if you lose the ability to uniquely identify a user, things can go south from there quickly. On the other hand, nobody wants to spend hours writing number generators for every table or every web application.
The solution is not in your code, but in your database. Most databases, MySQL included, give you the ability to use an attribute called AUTO_INCREMENT. You specify this on a field in a table, and every time you add a row to that table, the field automatically creates a new number, incremented from the last row that was added to the table. For example, if one script adds a new user and MySQL sets the user _id to 1029, and another script later adds a new user, MySQL increments the previous number and assigns 1030 as the ID of the new user.
IDS AND PRIMARY KEYS ARE GOOD BEDFELLOWS
In addition to setting user _id to increment automatically you’ve actually done something else subtly in MySQL: you’ve basically defined user _id as the Dr in the users table. The primary key is a database term for that special, unique value assigned to a particular rDW in a table.
Primary keys are important because databases typically create an tndex using a table’s primary key. An index is a database-level mechanism by which a database can find rows based on that index quickly. With 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
Basically an indexed field is like having a highly organized set of values. An unindexed field can still be searched, but in that case your database has to go through each” value, one by one, until it finds the exact value for which you’re searching. It’s the difference between looking for a book in a well-organized library and looking for one in your great-great-grandfather’s deserted attic.
When you instruct MySQL to automatically increment user _id, you identify that field as special. In fact. MySOL won’t let you set more than one field to AUTO_INCREMENT, because it assumes that you put that on a field to use as a primary key
There’s just a little hitch, though: you have to instruct MySQL that you want user _id to be the primary key, by including the following:
CREATE TABLE users (
user_id int AUTO_INCREMENT PRIMARY KEY,
This makes explicit what is implicit with AUTO_INCREMENT: user_id uniquely identifies each user entry in your table. In fact. if you don’t do this MySQL gives you an error. As an example suppose that you have the following SQL, without the
PRIMARY KEY keyword:
CREATE TABLE users (
user_id int AUTO_INCREMENT,
If you were to run this query, MySQL would give you a bit of a weird error in the php My Admin console, as illustrated in; Figure 7-3.
This error-the infamous #1075 if you’ve been around MySQL for long-informs you that since you have an AUTO_INCREMENT column that you need to mark as PRIMARY KEY. It would be nice if MySQL would take care of that for you, but alas, it’s up to you, so be sure to include PRIMARY KEY. At this point, you’re (iinwst ready
to create this table for real.
Adding Constraints to Your Database
Remember that the purpose of a field like user _id is to facilitate eaioY searching. Adding AUTO_INCREMENT (and setting the field as a primary key) helps in that, but there’s something subtle that also happens behind the scenes when you create an AUTO_INCREMENT column. You are also saying, “No matter what, this column will have a value.” That’s because MySQL is filling in that value.
More than likely, there are additional fields that you almost always want to be filled in. For example, there’s really never a good time to let a user not put in her first or last name. And you should probably require an email address, too. Twitter handles and Facebook URLs are not always going to be attached to a user, so those can be left off, but the rest is mandatory
Of course, could just decide to have 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 a.m.? It’s never a good idea to not validate when you con validate.
To Null or Not to Null
Although the (table makes figuring out which columns should be NOT NULL fairly easy, that’s not always (or often) the case. In fact, even with use«, there’s ambiguity: are you sure you want to require an email address? It is possible that someone might not have one (it still happens, although why email-Iess folks would be surfing the Internet might be Quite a mystery), or you might have users concerned with you sparnming them, and they don’t want to enter an email. Are you sure that you want to require that as part of a user’s information
It might surprise you, but making a column NOTNULL is one of the most important decisions you make with regard to an individual table. This is particularly true if you decide fi(![ to make a column NOTNULL.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 NOTNULL, thinking that it’s just safer to use it frequently and grab more data rather than less. Users can become upset if they’re forced 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, user name, and password. Everything else can be added later.
it should also be required that they enter the street, city, and country, 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 NOTNULL
Like AUTO_INCREMENT, this change is quick, easy, and goes a long way toward protecting the integrity of your information (or, to be more accurate, your user’s information).
You should have a useful SQL statement, so go ahead and create your,table. Log in to MySQL by using your command-line tool, the web form you built earlier, or another web tool like php My Admin, and create the table. You’re about to need it.
If you’re using a tool like php My Admin, you can now view your created table, It should look something like Figure 7-4.