Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources]

Setting LIMITs

Often you might work with a large database table, but only really be interested in a few entries within it. Let's say you wanted to track the popularity of different jokes on your site. You could add a column named TimesViewed to your Jokes table. Start it with a value of zero for new jokes, and add one to the value of the requested joke every time the joke page is viewed, to keep count of the number of times each joke in your database has been read.

The query for adding one to the TimesViewed column of a joke with a given ID is as follows:

$sql = "UPDATE Jokes SET TimesViewed=TimesViewed+1
WHERE ID='$id'";
if (!mysql_query($sql)) {
echo("<p>Error adding to times viewed for this joke!</p>\n");
}

A common use of this "joke view counter" would be to present a "Top 10 Jokes" list on the front page of the site, for example. Using ORDER BY TimesViewed DESC to list the jokes from highest TimesViewed to lowest, we would just have to pick the 10 first values from the top of the list. But if we have thousands of jokes in our database, the retrieval of a list of thousands would be quite wasteful in terms of the processing time and server system resources required, such as memory and CPU load, to use only ten of those retrieved.

But, if we use a LIMIT clause, we can specify a certain number of results to be returned. In our example, we need only the first ten:

$sql = "SELECT * FROM Jokes ORDER BY TimesViewed DESC LIMIT 10";

Although much less interesting, we could get rid of the word DESC and retrieve the 10 least popular jokes in the database.

Often, you want to let users view a long list of entries, say, the results of a search, but wish to display only a few at a time. Think of the last time you went looking through pages of search engine results to find a particular Website. You can use a LIMIT clause to do this sort of thing—simply specify both the result to begin the list with, and the maximum number of results to display. The query below, for example, will list the 21^st to 25^th most popular jokes in the database:

$sql = "SELECT * FROM Jokes ORDER BY TimesViewed DESC
LIMIT 20, 5";

Remember, the first entry in the list of results is entry number 0. Thus, the 21^st entry in the list is entry number 20.