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.