MySQL Crash Course, Part 3

Welcome back to another installment of PHP Foundations. In my last two articles, I have been on a bit of a tangent from PHP while I discuss the fundamentals of working with the MySQL RDBMS and MySQL SQL syntax. These concepts are essential to understanding how to use MySQL effectively from PHP. In any case, you should be happy to know today's column
will wrap up this tangent so that I can return to the more familiar ground of
PHP. I will wrap up my crash-course series on MySQL by discussing some useful
functions available when using SQL and PHP, introducing a clause or two for the
SELECT statement, and explaining a few of the principles behind
relating one table to another (after all, it is a "relational" database
system).

More SELECT Usage

As you have surely realized by now, the SELECT statement is
incredibly complex and important when working with SQL. To further expand your
knowledge of this statement, let's examine a few more SELECT-specific clauses. As you may have noticed in the previous columns, the data within the result sets did not appear in any particular order
after a query. Since it's often useful to work with sorted results, the
SELECT statement provides the ORDER BY clause, as demonstrated
here:

This asks MySQL to provide a result set consisting of the names of every
author in the authors table, sorting them in ascending order alphabetically.
If I'd wanted to sort this list in descending order instead, I'd have written:

Although the default sorting order is ascending, you can explicitly specify
it by replacing the DESC keyword above with ASC. To
order a result set by more than one column, simply provide a list of columns
(in the order by which they should be sorted) separated by commas. Although it
won't mean much for such a small table, here's how it would look:

When executed, this will return a single row of the result set. To return a
subset of the result set (a range of rows), the LIMIT clause can
also accept two parameters. The first parameter represents the start row (zero
is the first row — it's not tied to any unique ID or primary key) and the
second parameter is the number of results to return, as shown:

Exploring Table Relationships

At this point, we have at least touched on most of the common operations used
in day-to-day SQL. However, recall that MySQL is not just a database management
system, but rather a relational database management system. To understand why
relational databases are important, let's re-examine our two tables:

Looking at the above tables, how would you determine the name of the author
of each individual book? As you can see, each table does have an
author_id column in common, though the title of each book is
stored in the books table, while author names live in the
authors table. With what we have learned thus far, there is no
reasonable way to create a single result set that contains both the title of a
book and the name of its author. To do this, we must somehow merge these two
tables together.

All of my discussions of the SELECT statement so far has been
limited to selecting from one table. Although that has served our purposes just
fine thus far, it is time to introduce the concept of table joins. Although
joins themselves can be fairly complex, the fundamental principles are
straightforward. Consider the following query:

This query told the database to retrieve the title column from
the books table (that's books.title) and the
name column from the authors table
(authors.name). Because we are now working with more than one
table, the FROM portion of the query must also provide the names
of the tables with which we are working (books and
authors).

The final portion of the query is the most important in this circumstance,
as it defines what and how data from both tables will be included in the
final result set. In this case, we have provided the condition WHERE
books.author_id = authors.author_id. This condition relates the data
within the books table to the data within the authors
table by the author_id column present in both. Thus, as MySQL
constructs the result set, it compares the respective author_id
columns and matches up the data appropriately. The concept of relating tables
together is the single most fundamental idea behind the modern database. It
allows you to generate a wealth of reports based on data in your database
quickly and easily.

Aggregate Reports Through Joins

Joins make other tricky issues possible, as well as making other functions
very valuable. For instance, consider a report of how many books were
published by each individual author. Since we are going to require data from
two different tables, this obviously would require a join; however, that alone
won't suffice. In order to produce such a query, you'll need the
COUNT() MySQL function and the GROUP BY clause.

As I alluded to earlier, SQL is a language focused on extracting data from a
database. To facilitate this, SQL provides a wide range of functions for
everything from date manipulation to string concatenation and mathematical
functions. To count the total number of books per author, we need the
COUNT() function. As its name implies, this function counts the
number of rows within a result set. It takes a single parameter (the column to
count) and returns an integer representing the number of rows counted. For
instance, you could count how many rows are in the books table
using this query:

As you can see, the query returns two columns. The first column is the name
of the author taken from the authors.name column, while the second
column is a calculated value based on the COUNT() function.
However, notice that I have renamed the column in the result set, using the
AS keyword. This is an important detail that will become
especially useful when we work with MySQL from a PHP script, since data from a
query is usefully accessed by the column name itself.

The last thing that is significantly different about this query than any
other we have seen thus far is the GROUP BY clause. This clause
condenses a particular column (in this case, the authors.name
column), grouping duplicate entries together into a single row. This is useful
in this circumstance, since in this query it wouldn't make a whole lot of sense to
have multiple copies of the same name in the result set. In fact, when using
the COUNT() function with a multi-column result set, you must have
a GROUP BY clause in order for the query to make sense.

More to Come Next Time

With that, I'll end my MySQL crash course! If you didn't previously know SQL,
hopefully by now you have learned enough to start writing simple queries. My
next column will begin my introduction to the MySQL extension for PHP. Until
then, if you would like to read more about how SQL works, you can find the
MySQL documentation on the MySQL home page.

John Coggeshall
is a a PHP consultant and author who started losing sleep over PHP around five years ago.