Page 1 of 1

PHP/MySQL solution to this conundrum

Posted: Thu Jan 20, 2005 12:49 pm
by mjseaden
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

Posted: Thu Jan 20, 2005 12:54 pm
by feyd
limit is indeed what to use:

Code: Select all

SELECT ... LIMIT 10,10

Posted: Thu Jan 20, 2005 12:59 pm
by timvw
the 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

Code: Select all

SELECT *
FROM foo
ORDER BY 1
LIMIT 20 OFFSET 10