4. Introduction to MySQL
Choosing Your Column Types 114
Choosing Other Column Properties 118
Because this book discusses how to integrate several technologies (primarily PHP, SQL, and MySQL), a solid understanding of each individually is important before you begin writing PHP scripts that use SQL to interact with MySQL. This chapter is a departure from its predecessors in that it temporarily leaves PHP behind to delve into MySQL.
MySQL is the world’s most popular open-source database application (according to MySQL’s Web site, www.mysql.com) and is commonly used with PHP. The MySQL software comes with the database server (which stores the actual data), different client applications (for interacting with the database server), and several utilities. In this chapter you’ll see how to define a simple table using MySQL’s allowed data types and other properties. Then you’ll learn how to interact with the MySQL server using two different client applications. All of this information will be the foundation for the SQL taught in the next chapter.
Naming Database Elements
Before you start working with databases, you have to identify your needs. The purpose of the application (or Web site, in this case) dictates how the database should be designed. With that in mind, the examples in this chapter and the next will use a database that stores some user registration information.
When creating databases and tables, you should come up with names (formally called identifiers) that are clear, meaningful, and easy to type. Also, identifiers
• Should only contain letters, numbers, and the underscore (no spaces)
• Should not be the same as an existing keyword (like an SQL term or a function name)
• Should be treated as case-sensitive
• Cannot be longer than 64 characters (approximately)
• Must be unique within its realm
This last rule means that a table cannot have two columns with the same name and a database cannot have two tables with the same name. You can, however, use the same column name in two different tables in the same database (in fact, you often will do this). As for the first three rules, I use the word should, as these are good policies more than exact requirements. Exceptions can be made to these rules, but the syntax for doing so can be complicated. Abiding by these suggestions is a reasonable limitation and will help avoid complications.
To name a database’s elements
1. Determine the database’s name.
This is the easiest and, arguably, least important step. Just make sure that the database name is unique for that MySQL server. If you’re using a hosted server, your Web host will likely provide a database name that may or may not include your account or domain name.
For this first example, the database will be called sitename, as the information and techniques could apply to any generic site.
2. Determine the table names.
The table names just need to be unique within this database, which shouldn’t be a problem. For this example, which stores user registration information, the only table will be called users.
3. Determine the column names for each table.
The users table will have columns to store a user ID, a first name, a last name, an email address, a password, and the registration date. Table 4.1 shows these columns, with sample data, using proper identifiers. As MySQL has a function called password, I’ve changed the name of that column to just pass. This isn’t strictly necessary but is really a good idea.
Chapter 6, “Database Design,” discusses database design in more detail, using more complex examples.
To be precise, the length limit for the names of databases, tables, and columns is actually 64 bytes, not characters. While most characters in many languages require 1 byte apiece, it’s possible to use a multibyte character in an identifier. But 64 bytes is still a lot of space, so this probably won’t be an issue for you.
Whether or not an identifier in MySQL is case-sensitive actually depends upon many things (because each database is actually a folder on the server and each table is actually one or more files). On Windows and normally on Mac OS X, database and table names are generally case-insensitive. On Unix and some Mac OS X setups, they are case-sensitive. Column names are always case-insensitive. It’s really best, in my opinion, to always use all lowercase letters and work as if case-sensitivity applied.
Choosing Your Column Types
Once you have identified all of the tables and columns that the database will need, you should determine each column’s data type. When creating a table, MySQL requires that you explicitly state what sort of information each column will contain. There are three primary types, which is true for almost every database application:
• Text (aka strings)
• Numbers
• Dates and times
Within each of these, there are many variants—some of which are MySQLspecific—you can use. Choosing your column types correctly not only dictates what information can be stored and how but also affects the database’s overall performance. Table 4.2 lists most of the available types for MySQL, how much space they take up, and brief descriptions of each type. Note that some of these limits may change in different versions of MySQL, and the character set (to be discussed in Chapter 6) may also impact the size of the text types.
Many of the types can take an optional Length attribute, limiting their size. (The square brackets, [ ], indicate an optional parameter to be put in parentheses.) For performance purposes, you should place some restrictions on how much data can be stored in any column. But understand that attempting to insert a string five characters long into a CHAR(2) column will result in truncation of the final three characters (only the first two characters would be stored; the rest would be lost forever). This is true for any field in which the size is set (CHAR, VARCHAR, INT, etc.). Thus, your length should always correspond to the maximum possible value (as a number) or longest possible string (as text) that might be stored.
The various date types have all sorts of unique behaviors, the most important of which you’ll learn in this book (all of the behaviors are documented in the MySQL manual). You’ll use the DATE and TIME fields primarily without modification, so you need not worry too much about their intricacies.
There are also two special types—ENUM and SET—that allow you to define a series of acceptable values for that column. An ENUM column can store only one value of a possible several thousand, while SET allows for several of up to 64 possible values. These are available in MySQL but aren’t present in every database application.
To select the column types
1. Identify whether a column should be a text, number, or date/time type (Table 4.3).
This is normally an easy and obvious step, but you want to be as specific as possible. For example, the date 2006-08-02 (MySQL format) could be stored as a string—August 2, 2006. But if you use the proper date format, you’ll have a more useful database (and, as you’ll see, there are functions that can turn 2006-08-02 into August 2, 2006).
2. Choose the most appropriate subtype for each column (Table 4.4).
For this example, the user_id is set as a MEDIUMINT, allowing for up to nearly 17 million values (as an unsigned, or non-negative, number). The registration_date will be a DATETIME. It can store both the date and the specific time a user registered. When deciding among the date types, consider whether or not you’ll want to access just the date, the time, or possibly both. If unsure, err on the side of storing too much information.
The other fields will be mostly VARCHAR, since their lengths will differ from record to record. The only exception is the password column, which will be a fixed-length CHAR (you’ll see why when inserting records in the next chapter). See the sidebar “CHAR vs. VARCHAR” for more information on these two types.
3. Set the maximum length for text columns (Table 4.5).
The size of any field should be restricted to the smallest possible value, based upon the largest possible input. For example, if a column stores a state abbreviation, it would be defined as a CHAR(2). Other times you might have to guess somewhat: I can’t think of any first names longer than about 10 characters, but just to be safe I’ll allow for up to 20.
The length attribute for numeric types does not affect the range of values that can be stored in the column. Columns defined as TINYINT(1) or TINYINT(20) can store the exact same values. Instead, for integers, the length dictates the display width; for decimals, the length is the total number of digits that can be stored.
If you need absolute precision when using non-integers, DECIMAL is preferred over FLOAT or DOUBLE.
Depending upon the version of MySQL in use, the TIMESTAMP field type is automatically set as the current date and time when an INSERT or UPDATE occurs, even if no value is specified for that particular field. If a table has multiple TIMESTAMP columns, only the first one will be updated when an INSERT or UPDATE is performed.
MySQL also has several variants on the text types that allow for storing binary data. These types are BINARY, VARBINARY, TINYBLOB, MEDIUMBLOB, and LONGBLOB. Such types can be used for storing files or encrypted data.
Choosing Other Column Properties
Besides deciding what data types and sizes you should use for your columns, you should consider a handful of other properties.
First, every column, regardless of type, can be defined as NOT NULL. The NULL value, in databases and programming, is equivalent to saying that the field has no known value. Ideally, in a properly designed database, every column of every row in every table should have a value, but that isn’t always the case. To force a field to have a value, add the NOT NULL description to its column type. For example, a required dollar amount can be described as
cost DECIMAL(5,2) NOT NULL
When creating a table, you can also specify a default value for any column, regardless of type. In cases where a majority of the records will have the same value for a column, presetting a default will save you from having to specify a value when inserting new rows (unless that row’s value for that column is different from the norm).
gender ENUM('M', 'F') default 'F'
With the gender column, if no value is specified when adding a record, the default will be used.
If a column does not have a default value and one is not specified for a new record, that field will be given a default value based upon its type. For numeric types, the default value is 0. For most date and time types, the type’s version of “zero” will be the default (e.g., 0000-00-00). The first TIMESTAMP column in a table will have a default value of the current date and time. String types use an empty string ('') as the default value, except for ENUM, whose default value (again, if not otherwise specified) is the first possible enumerated value (M in the above example).
The number types can be marked as UNSIGNED, which limits the stored data to positive numbers and zero. This also effectively doubles the range of positive numbers that can be stored (because no negative numbers will be kept, see Table 4.2). You can also flag the number types as ZEROFILL, which means that any extra room will be padded with zeros (ZEROFILLs are also automatically UNSIGNED).
Finally, when designing a database, you’ll need to consider creating indexes, adding keys, and using the AUTO_INCREMENT property. Chapter 6 discusses these concepts in greater detail, but in the meantime, check out the sidebar “Indexes, Keys, and AUTO_INCREMENT” to learn how they affect the users table.
To finish defining your columns
1. Identify your primary key.
The primary key is quixotically both arbitrary and critically important. Almost always a number value, the primary key is a unique way to refer to a particular record. For example, your phone number has no inherent value but is unique to you (your home or mobile phone).
In the users table, the user_id will be the primary key: an arbitrary number used to refer to a row of data. Again, Chapter 6 will go into the concept of primary keys in more detail.
2. Identify which columns cannot have a NULL value.
In this example, every field is required (cannot be NULL). As an example of a column that could have NULL values, if you stored peoples’ addresses, you might have address_line1 and address_line2, with the latter one being optional. In general, tables that have a lot of NULL values suggest a poor design (more on this in...you guessed it...Chapter 6).
3. Make any numeric type UNSIGNED if it won’t ever store negative numbers.
The user_id, which will be a number, should be UNSIGNED so that it’s always positive (primary keys should be unsigned). Other examples of UNSIGNED numbers would be the price of items in an e-commerce example, a telephone extension for a business, or a zip code.
4. Establish the default value for any column.
None of the columns here logically implies a default value.
5. Confirm the final column definitions (Table 4.6).
Before creating the tables, you should revisit the type and range of data you’ll store to make sure that your database effectively accounts for everything.
Text columns can also have defined character sets and collations. This will mean more...in Chapter 6.
Default values must always be a static value, not the result of executing a function, with one exception: the default value for a TIMESTAMP column can be assigned as CURRENT_TIMESTAMP.
Accessing MySQL
In order to create tables, add records, and request information from a database, some sort of client is necessary to communicate with the MySQL server. Later in the book, PHP scripts will act in this role, but being able to use another interface is necessary. Although there are oodles of client applications available, I’ll focus on two: the mysql client and the Web-based phpMyAdmin. A third option, the MySQL Query Browser, is not discussed in this book but can be found at the MySQL Web site (www.mysql.com), should you not be satisfied with these two choices.
The rest of this chapter assumes you have access to a running MySQL server. If you are working on your own computer, see Appendix A, “Installation,” for instructions on installing MySQL, starting MySQL, and creating MySQL users (all of which must already be done in order to finish this chapter). If you are using a hosted server, your Web host should provide you with the database access. Depending upon the hosting, you may be provided with phpMyAdmin, but not be able to use the command-line mysql client.
Using the mysql Client
The mysql client is normally installed with the rest of the MySQL software. Although the mysql client does not have a pretty graphical interface, it’s a reliable, standard tool that’s easy to use and behaves consistently on many different operating systems.
The mysql client is accessed from a command-line interface, be it the Terminal application in Linux or Mac OS X 
, or a DOS prompt in Windows 
. If you’re not comfortable with command-line interactions, you might find this interface to be challenging, but it becomes easy to use in no time.
A Terminal window in Mac OS X.
A Windows DOS prompt or console (although the default is for white text on a black background).
To start the application from the command line, type its name and press Return or Enter:
mysql
Depending upon the server (or your computer), you may need to enter the full path in order to start the application. For example:
/Applications/MAMP/Library/bin/mysql (Mac OS X, using MAMP)
C:xamppmysqlinmysql (Windows, using XAMPP)
When invoking this application, you can add arguments to affect how it runs. The most common arguments are the username, password, and hostname (computer name, URL, or IP address) you want to connect using. You establish these arguments like so:
mysql -u username -h hostname –p
The -p option will cause the client to prompt you for the password. You can also specify the password on this line if you prefer—by typing it directly after the -p prompt—but it will be visible, which is insecure. The -h hostname argument is optional, and you can leave it off unless you cannot connect to the MySQL server without it.
Within the mysql client, every statement (SQL command) needs to be terminated by a semicolon. These semicolons are an indication to the client that the query is complete and should be run. The semicolons are not part of the SQL itself (this is a common point of confusion). What this also means is that you can continue the same SQL statement over several lines within the mysql client, which makes it easier to read and to edit, should that be necessary.
As a quick demonstration of accessing and using the mysql client, these next steps will show you how to start the mysql client, select a database to use, and quit the client. Before following these steps,
• The MySQL server must be running.
• You must have a username and password with proper access.
Both of these ideas are explained in Appendix A.
As a side note, in the following steps and throughout the rest of the book, I will continue to provide images using the mysql client on both Windows and Mac OS X. While the appearance differs, the steps and results will be identical. So in short, don’t be concerned about why one image shows the DOS prompt and the next a Terminal.
To use the mysql client
1. Access your system from a command-line interface.
On Unix systems and Mac OS X, this is just a matter of bringing up the Terminal or a similar application.
If you are using Windows and installed MySQL on your computer, choose Run from the Start menu (or press Windows Key+R), type cmd in the window 
, and press Enter (or click OK) to bring up a DOS prompt.
Executing cmd within the Run prompt in Windows is one way to access a DOS prompt interface.
2. Invoke the mysql client, using the appropriate command 
.
/path/to/mysql/bin/mysql -u username -p
Access the mysql client by entering the full path to the utility, along with the proper arguments.
The /path/to/mysql part of this step will be largely dictated by the operating system you are running and where MySQL was installed. I’ve already provided two options, based upon installations of MAMP on Mac OS X or XAMPP on Windows (both are installed in Appendix A).
The basic premise is that you are running the mysql client, connecting as username, and requesting to be prompted for the password. Not to overstate the point, but the username and password values that you use must already be established in MySQL as a valid user (see Appendix A).
3. Enter the password at the prompt and press Return/Enter.
The password you use here should be for the user you specified in the preceding step. If you used the proper username/password combination (i.e., someone with valid access), you should be greeted as shown in 
. If access is denied, you’re probably not using the correct values (see Appendix A for instructions on creating users).
If you are successfully able to log in, you’ll see a welcome message like this.
4. Select the database you want to use 
.
USE test;
After getting into the mysql client, run a USE command to choose the database with which you want to work.
The USE command selects the database to be used for every subsequent command. The test database is one that MySQL installs by default. Assuming it exists on your server, all users should be able to access it.
exit
Type either exit or quit to terminate your MySQL session and leave the mysql client.
You can also use the command quit to leave the client. This step—unlike most other commands you enter in the mysql client—does not require a semicolon at the end.
6. Quit the Terminal or DOS console session.
exit
The command exit will terminate the current session. On Windows, it will also close the DOS prompt window.
If you know in advance which database you will want to use, you can simplify matters by starting mysql with
/path/to/mysql/bin/mysql -u username
-p databasename
The mysql client on most systems allows you to use the up and down arrows to scroll through previously entered commands. If you make a mistake in typing a query, you can scroll up to find it, and then correct the error.
In the mysql client, you can also terminate SQL commands using G instead of the semicolon. For queries that return results, using G displays those results as a vertical list, as opposed to a horizontal table, which is sometimes easier to peruse.
If you are in a long statement and make a mistake, cancel the current operation by typing c and pressing Return or Enter. If mysql thinks a closing single or double quotation mark is missing (as indicated by the '> and "> prompts), you’ll need to enter the appropriate quotation mark first.
Using phpMyAdmin
phpMyAdmin (www.phpmyadmin.net) is one of the best and most popular applications written in PHP. Its sole purpose is to provide an interface to a MySQL server. It’s somewhat easier and more natural to use than the mysql client but requires a PHP installation and must be accessed through a Web browser. If you’re running MySQL on your own computer, you might find that using the mysql client makes more sense, as installing and configuring phpMyAdmin constitutes unnecessary extra work (although all-in-one PHP and MySQL installers may do this for you). If using a hosted server, your Web host is virtually guaranteed to provide phpMyAdmin as the primary way to work with MySQL and the mysql client may not be an option.
Using phpMyAdmin isn’t hard, but the next steps run through the basics so that you’ll know what to do in the following chapters.
To use phpMyAdmin
1. Access phpMyAdmin through your Web browser 
.
The first phpMyAdmin page (when connected as a MySQL user that can access multiple databases).
The URL you use will depend upon your situation. If running on your own computer, this might be http://localhost/phpMyAdmin/. If running on a hosted site, your Web host will provide you with the proper URL. In all likelihood, phpMyAdmin would be available through the site’s control panel (should one exist).
Note that phpMyAdmin will only work if it’s been properly configured to connect to MySQL with a valid username/password/hostname combination. If you see a message like the one in 
, you’re probably not using the correct values (see Appendix A for instructions on creating users).
Every client application requires a proper username/password/hostname combination in order to interact with the MySQL server.
2. If possible and necessary, use the list on the left to select a database to use 
.
Use the list of databases on the left side of the window to choose with which database you want to work. This is the equivalent of running a USE databasename query within the mysql client.
What options you have here will vary depending upon what MySQL user phpMyAdmin is connecting as. That user might have access to one database, several databases, or every database. On a hosted site where you have just one database, that database will probably already be selected for you. On your own computer, with phpMyAdmin connecting as the MySQL root user, you would see a pull-down menu or a simple list of available databases 
.
3. Click on a table name in the left column to select that table 
.
Selecting a table from the left column changes the options on the right side of the page.
You don’t always have to select a table—in fact you never will if you just use the SQL commands in this book, but doing so can often simplify some tasks.
4. Use the tabs and links (on the right side of the page) to perform common tasks.
For the most part, the tabs and links are shortcuts to common SQL commands. For example, the Browse tab performs a SELECT query and the Insert tab creates a form for adding new records.
5. Use the SQL tab 
or the SQL query window 
to enter SQL commands.
The SQL tab, in the main part of the window, can be used to run any SQL command.
The SQL window can also be used to run commands. It pops up after clicking the SQL icon at the top of the left side of the browser (see the second icon from the left in 
).
The next three chapters, and a couple more later in the book, will provide SQL commands that must be run to create, populate, and manipulate tables. These might look like
INSERT INTO tablename (col1, col2) VALUES (x, y)
These commands can be run using the mysql client, phpMyAdmin, or any other interface. To run them within phpMyAdmin, just enter them into one of the SQL prompts and click Go.
There’s a lot more that can be done with phpMyAdmin, but full coverage would require a chapter in its own right (and a long chapter at that). The information presented here will be enough for you to follow any of the examples in the book, should you not want to use the mysql client.
phpMyAdmin can be configured to use a special database that will record your query history, allow you to bookmark queries, and more. See the phpMyAdmin documentation for details.
One of the best reasons to use phpMyAdmin is to transfer a database from one computer to another. Use the Export tab in phpMyAdmin connected to the source computer to create a file of data. Then, on the destination computer, use the Import tab in phpMyAdmin (connected to that MySQL server) to complete the transfer.
Review and Pursue
If you have any problems with the review questions or the pursue prompts, turn to the book’s supporting forum (www.LarryUllman.com/forums/).
Review
• What version of MySQL are you using? If you don’t know, find out now!
• What characters can be used in database, table, and column names?
• Should you treat database, table, and column names as case-sensitive or case-insensitive?
• What are the three general column types?
• What are the differences between CHAR and VARCHAR?
• How do you determine what size (in terms of subtype or length) a column should be?
• What are some of the other properties that can be assigned to columns?
• What is a primary key?
• If you’re using the command-line mysql client to connect to MySQL, what username and password combination is required?
Pursue
• Find the online MySQL manual for your version of MySQL. Bookmark it!
• Start thinking about what databases you may need for your projects.
• If you haven’t yet changed the MySQL root user password (assuming you’ve installed MySQL on your own computer), use the instructions in Appendix A to do so now.