September 23, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Building Database-Driven Applications with PHP and MySQL

  • May 9, 2003
  • By Elizabeth Fulghum
  • Send Email »
  • More Articles »

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.

Setting up a new database is a two-step process. First, the database itself has to be created:

CREATE database_name;

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:

CREATE TABLE users (
id INT(12) AUTO_INCREMENT,
name VARCHAR(50),
email VARCHAR(50),
PRIMARY KEY (id)
);

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.

Troubleshooting

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:

mysql_connect('host','username','password) or die(mysql_error());

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:

if (mysql_connect('host','username','password)==FALSE) {
     $error = mysql_error();
}

Finally

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.

Stay Tuned!

Things to Remember

  • MySQL is a popular relational database product
  • SQL is a language used to communiate with MySQL (and other databases). It allows information to be retrevied, modified, and deleted. It also provides a means to modify databases and tables.
  • Databases are similiar to associative arrays. Each database can contain one or more tables. Each table is composed of records divided into fields.
  • Working with a database through a PHP script follows this basic process: Connect to the server, select a database, perform any queries on the database, close the connection.
  • SELECT, INSERT, UPDATE, and DELETE make up SQL's data manipulation commands.
  • The data retreived from a table can be limited in different ways: Specific columns can be returned by including them within the query, the WHERE clause can be used to to include or exclude specific records, and LIMIT can cap the number of entries that are returned.
  • Results can be sorted using the ORDER BY clause. By default, String Type fields are sorted alphabetically, Numeric Type fields are sorted from smallest to largest.
  • When creating a new table in a database, a unique table name must be specified. In addition, a name, field type, and length are defined for each column in the table.
  • In almost every case, a table should have an AUTO_INCREMENT Primary ID column defined.
  • All of PHP's MySQL handling functions return FALSE if there is an error in the query or the query fails. Using mysql_error() returns the exact error reported by MySQL.

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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel