Page 411 - Beginning PHP 5.3

Chapter 13: Retrieving Data from MySQL with PHP
You might be wondering what the point of LIMIT is, because you can always just loop through the result
set in PHP to extract only the rows you ’ re interested in. The main reason to use LIMIT is that it reduces
the amount of data that has to flow between MySQL and your PHP script.

Imagine that you want to retrieve the first 100 rows of a million - row table of users. If you use LIMIT
100 , only 100 rows are sent to your PHP script. However, if you don ’ t use a LIMIT clause (and your
query also contains no WHERE clause), all 1,000,000 rows of data will be sent to your PHP script, where
they will need to be stored inside a PDOStatement object until you loop through them to extract the first
100. Storing the details of a million users in your script will quickly bring the script to a halt, due to the
large amount of memory required to do so.

LIMIT is particularly useful when you ’ re building a paged search function in your PHP application. For
example, if the user requests the second page of search results, and you display 10 results per page, you
can use SELECT ... LIMIT 10, 10 to retrieve the second page of results. You build a paging system
using LIMIT in the “ Creating a Member Record Viewer ” section later in the chapter.

Sorting Results
One of the powerful features that really separate databases from text files is the speed and ease with
which you can retrieve data in any order. Imagine that you have a text file that stores the first and last
names of a million book club members, ordered by first name. If you wanted to retrieve a list of all the
members ordered by last name, you ’ d need to rearrange an awful lot of rows in your text file.

With SQL, retrieving records in a different order is as simple as adding the keywords ORDER BY to your
query, followed by the column you want to sort by:

mysql > SELECT username, firstName, lastName FROM members ORDER BY firstName;
+-----------+-----------+-----------+
| username | firstName | lastName |
+-----------+-----------+-----------+
| bigbill | Bill | Swan |
| janefield | Jane | Field |
| jojo | Jo | Scrivener |
| sparky | John | Sparks |
| marty | Marty | Pareene |
| mary | Mary | Newton |
| nickb | Nick | Blakeley |
+-----------+-----------+-----------+
7 rows in set (0.00 sec)

mysql > SELECT username, firstName, lastName FROM members ORDER BY lastName;
+-----------+-----------+-----------+
| username | firstName | lastName |
+-----------+-----------+-----------+
| nickb | Nick | Blakeley |
| janefield | Jane | Field |
| mary | Mary | Newton |
| marty | Marty | Pareene |
| jojo | Jo | Scrivener |
| sparky | John | Sparks |
| bigbill | Bill | Swan |
+-----------+-----------+-----------+

7 rows in set (0.00 sec)
373

9/21/09 9:11:58 AM
c13.indd 373
c13.indd 373 9/21/09 9:11:58 AM