PHP/MySQL solution to this conundrum

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

PHP/MySQL solution to this conundrum

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

limit is indeed what to use:

Code: Select all

SELECT ... LIMIT 10,10
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Post Reply