Hi there,
I have a list of products recorded in a database.
I have a query which returns a certain number of those products that match those specifications.
However, I only wants lines 10-20 of the return on the query. Is there any addition to the query, or a PHP method other than inefficient for() or while() loops, to jump to line 10 and only cycle to line 20?
Would LIMIT be useful?
(by the way in database speak I guess line should say 'row')
Many thanks
Mark
PHP/MySQL solution to this conundrum
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
limit is indeed what to use:
Code: Select all
SELECT ... LIMIT 10,10the name for your problem: pagination
mysql allows you to use limit , mssql has top, other rdbms will probably have a similar function.
If i'm not mistaken, in a relational model you would speak of tuples. In sql you talk about rows.
Let it be clear, that a query returns all the rows that meet the requirements (defined in the where clause). But there is no implicit ordering on those rows, so before you can use LIMIT, you have to make sure there is an ordering on the returned rows, because otherwise it's not really possible which row is the 10th...
In MySQL the following query would return 20 rows, starting from the 10th
mysql allows you to use limit , mssql has top, other rdbms will probably have a similar function.
If i'm not mistaken, in a relational model you would speak of tuples. In sql you talk about rows.
Let it be clear, that a query returns all the rows that meet the requirements (defined in the where clause). But there is no implicit ordering on those rows, so before you can use LIMIT, you have to make sure there is an ordering on the returned rows, because otherwise it's not really possible which row is the 10th...
In MySQL the following query would return 20 rows, starting from the 10th
Code: Select all
SELECT *
FROM foo
ORDER BY 1
LIMIT 20 OFFSET 10