Almost every dynamic Web application eventually boils down to accessing, manipulating, and presenting information. A forum is an idea example of this type of application. Users register at the site; their information is added to the database. Someone wants to post a new message? The database is queried to validate the user and again to store the message once the user has finished writing it. Displaying each page, the forum listing, the threads listing, the message listing, and the user profile pages relies on accessing stored information. All apparently separate sets of information are stored together in a single database and it's all pulled together with languages such as PHP and SQL.
This article is the first of four parts, focusing on building database-driven applications with PHP and MySQL. In this part, you will be introduced to MySQL and SQL, discover what databases look like, and begin to learn the commands to work with them.
Why MySQL?
There's a plethora of database products out there, from Oracle, mSQL, and PostgreSQL to MS Access. Even amid all this competition, MySQL has managed to become and remain exceptionally popular in the *nix programming community and the first choice for PHP developers.
There are several reasons for this. The biggest draw is the fact that MySQL is completely free under the GPL license, which gives it an instant price advantage over often-expensive commercial options. It is also open source, enjoying the benefits of community development and quick attention to bugs. And because it is natively supported by PHP without compiling in any additional modules, it is a natural choice for Web hosts that want to offer database support.
These all add up to make MySQL a compelling choice.
Free and accessible do not equate a less capable product, though; for small to medium-large applications, MySQL is also fast, adaptable, and feature-rich. Performance benchmarks against other databases are frequently biased (in one direction or another), but they generally give MySQL high marks. MySQL's own documentation indicates that, barring an operating system's own limitations, databases can be as large as 8 million terabytes.
Talking to Databases Using SQL
SQL is an abbreviation for Structured Query Language. It is a surprising natural language that allows us to "talk" to relational databases, such as MySQL, to request and modify data. It also allows us to get information about and modify the framework that contains the data, databases themselves, and tables within them. The queries formed with SQL can range from the simple to the very complex, which makes it an extremely powerful tool for working with data.
SQL is not exclusive to MySQL, nor should it be mistaken as being a part of PHP. It's also not the only database query language out there. It is, however, the most popular. If you learn how to speak SQL, that knowledge translates to a range of other popular database products.
The Anatomy of a Database
Simply put, a database is a collection of structured information. Relational databases, such as MySQL, allow information to broken into sets called tables. Take a look at this simple illustration of a table with some data in it:
users |
Id |
name |
email |
1 |
John |
john@doe.com |
2 |
Jane |
jane@doe.com |
3 |
Jack |
test@test.com |
Tables are very similar to associative arrays. This table has three rows of distinct information representing different users. Rows are also known as records. It is also divided into three columns: id, name, and email. The intersection of a row and a column is considered a field (The difference between a column and a field is a technical one and the terms are frequently used interchangeably), which is populated with the actual data.
Keep the structure of this sample database in mind; we'll be returning to it later in the article to illustrate several examples.
Working with Existing Databases Through PHP
Whether you are using PHP and MySQL, or another combination, the same basic procedure for working with a database applies.
First, a connection to the database server is established. This connection usually lasts for the lifetime of the running script. Additionally, as you will later see, there are instances where they can be contiguous across scripts.
Next, a specific database is selected to work with. Only one database at a time can be selected per connection, but you can switch to different databases without terminating the connection. You can also have multiple connections established within the script, each with an independent selected database.
Finally, once you have a connection established and a database selected, you can begin to work with the tables within them by using SQL to issue various commands. Data can be retrieved, added, modified, or deleted, and changes can be made to the selected database itself.
When finished, the connection to the database server is terminated manually with mysql_close(), or automatically with the end of the script.
Let's take a closer look at each step separately.
Connecting to MySQL
Just like a multi-user computer system or an FTP server, MySQL allows different user accounts to be established with individual levels of access. To establish a connection to MySQL, three pieces of information are required: the hostname of the database server, the username for the account, and the associated password.
This information is passed to the mysql_connect() function, which attempts to establish the connection:
$connect = mysql_connect('hostname','username','password');
The function returns a value TRUE on success, and FALSE on failure, so it is useful (though not necessary) to assign the function to a variable. In the above example, $connect is used. It can be evaluated to avoid performing any additional queries if the connection attempt failed:
if ($connect==FALSE) {
print "Database connection failed";
exit;
}
It is also useful to store the results in a variable because the function returns a unique connection identifier upon success. The variable then can be passed to any of PHP's MySQL handling functions (typically as the second parameter) to specify that this database connection should be used, as opposed to any other active ones within the script.
Selecting a Database
Once a connection is set up, the next step is to select a database. To do this, all that is needed is the name of an existing database:
mysql_select_db('database');
As with mysql_connect(), this function returns TRUE or FALSE depending on whether the database was successfully selected. To specify which connection the function should use, we can pass a connection identifier to the function:
mysql_select_db('database',$connect);
Without the second parameter, the function uses the database connection that was last established within the script.
IE 7
Firefox 2.0
