In our web site, we??™ll implement paging at the data tier level, not only because of its better
performance but also because it allows you to learn some tricks about database programming
that you??™ll find useful when developing your web sites.
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 128
To implement paging at the data tier level, we need to know how to build a SELECT query
that returns just a portion of records (products) from a larger set, and each database language
seems to have different ways of doing this. To achieve this functionality in MySQL, you need to
use the LIMIT keyword with the SELECT statement. LIMIT takes one or two arguments. The first
argument specifies the index of the first returned record, and the second specifies how many
rows to return.
The following SQL query tells MySQL to return the rows 15, 16, 17, 18, and 19 from the list
of products ordered by their IDs (remember that an index starts at zero for the first record, so
we ask for row 15 by asking for the index 14??”the row number minus one):
SELECT name
FROM product
ORDER BY product_id
LIMIT 14, 5;
With the current database you should get these results:
name
------------------------------------------------------------------------------------
Notre Dame
Paris Peace Conference
Sarah Bernhardt
Hunt
Italia
You??™ll use the LIMIT keyword to specify the range of records you??™re interested in when
retrieving lists of products.
Pages:
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224