Building Database-Driven Applications with PHP and MySQL
Creating New Databases and Tables
Creating and managing databases can be done using SQL via the command line interface to MySQL, through PHP queries you write yourself (mysql_query()), through popular managers like PhpMyAdmin (http://sourceforge.net/projects/phpmyadmin/), or through MySQL's own GUI (http://www.mysql.com/products/mysqlcc/). Most Web hosts now include a default installation of PhpMyAdmin.
Because there are numerous options, this section will briefly focus on the SQL commands for creating databases and tables, rather than a specific method of executing them.
The new database is completely empty (and relatively useless) until some tables are added to it. Here, things get a little trickier.
Remember, each table is made up of multiple columns (fields). When a new table is created, we need to assign names for all of the fields we want included. Additionally, each must be assigned a MySQL data type and, in some instances, a length (some MySQL data types require a field length to be defined; others do not.)
MySQL's data types determine what kind of information can be stored in a field. There are a wide range of data types available, which can loosely be classified into separate groups—string, numeric, and date/time types. Some of the most common types include:
- INT(225) (integer)—Holds integer values up to 255 characters. Length must be specified!
- VARCHAR(225)—Holds any string up to 255 characters. Length must be specified!
- TEXT—Holds strings with no maximum lengths
- DATETIME—Holds formatted date and time information.
To create the users table from the original example, the SQL would look like this:
The query begins with the CREATE TABLE command. Next is the unique name of the new table. Within the parentheses, each new field name is specified, followed by the field type and the maximum number of characters, if required.
The Importance of Primary IDs
You'll notice that the id field is followed by "AUTO_INCREMENT" and that it is also marked as a Primary Key after all the field names are defined.
Good database design says that in almost every instance a table should have an id column that is a unique value. This gives each record an identifier that can be used for editing, deleting and updating, and for joining tables. When AUTO_INCREMENT is applied to a field, MySQL will automatically populate the field with unique number that increments every time a record is inserted. The field that serves as the unique identifier is the table's primary key, and should be defined as such.
When there is something wrong with your PHP code, the processor automatically spits out an error message along with the line number where the error occurred. This makes troubleshooting exceptionally easy. At first glance, though, no such luck with MySQL queries. As mentioned before, the PHP functions that execute SQL queries just return FALSE when there is a problem. Not very helpful.
Fortunately, you are not left in the dark; there is a way to get more detailed error information. The PHP function mysql_error() returns the exact error message of the last failed query, according to MySQL. You'll frequently see it used to do quick and dirty error output like this:
In effect, this line of code says "Execute this SQL statement. If the command fails, stop processing the running script and output the error."
The error message can also be assigned to a variable for use in creating more graceful error handling, or in situations where you do not wish to terminate the script:
This article has introduced you to the basics of using SQL to work with MySQL through PHP. Next time, we'll build on the concepts introduced here to build a working application.
Liz Fulghum currently lives in Annapolis, MD where she works as a web designer for a custom shirt retailer. Liz was convinced to try PHP as an alternative to Perl; she has been a fan ever since and frequently works as a freelance developer.
# # #
Page 3 of 3