Break up SQL output amongst pages?

Anon

Got a potentially large database, with the potential for a large number of entries.

So, given the following query

"Select * from table where <condition>"

How would I break it up so that I can see the first 10 results, and then the next 10 (11-20) and then the next ten (21-30) and so on?

Something like

"select * from table where <condition> start=10 end=11"

or similar.

Can U help me?

Thanks all,

Ben Smith

Anon

If your physical table is not keyed, then you should be using an "ORDER BY" clause so you know you are retrieving records in a certain sequence. Then, you can use a "LIMIT" clause to limit the number of retrieved records to 20, 30, etc.

You will then have to use the key to the last record you retrieved and pass it to the next page (or same page) so you can use it in the querry such as:

"Select * from myTable where (keyField > passedKey) ORDER BY keyField LIMIT 20"

or something to that affect. You can use a similar method on a "back" or "previous" button or link.

-- Rich Rijnders
-- Irvine, CA US

Anon

"select * from table where <condition> limit $offset,$limit"

Where $offset is the offset from the beginning of the first line of the result and $limit is how many results to be displayed.

For more information take time and see the following article:
http://phpbuilder.com/columns/rod20000221.php3

Good luck.

SaS.

Anon

Need I say more? Thanks!

Anon

There are some problems with Rod's article, so I wrote an update. You can find it at http://php.socket7.net

I only plug this thing cuz I know it works!

Brett

Anon

Thanks, but your script is actually limiting in one subtle way...

I am dealing with what will rapidly become a large database = 100,000 rows or more.

This script reads ALL of the rows from a query into memory, then counts them.

(see line in your script: $numresults = mysql_query("SELECT * FROM table WHERE condition ORDER BY whatever", $db)😉

This could quickly kill a busy web-server!

I'm going ahead using the SQL limit / offset functions - only the information to work with for the current page is kept in memory. (Important with lots of transactions)

Also, instead of counting the rows from what's read, you might try something like
"select count(*) as numrows from table where <condition>".

This would return only the number of rows that apply. Again, really nice on RAM, and also gives fast execution.

I just could not remember or find the name of limit/offset!

-Ben
=)

Anon

Hey Ben-

I should update my script, you are right. The "select count(*) ..." method IS faster.
I knew this going in to writing the small tutorial as well but it obviously didn't make the final cut!

Brett

Anon

Eh, http://php.socket7.net seems to be some teenage haxors portal website thingy .. no traces of this "update".

With a little sense you'd have posted your bit right here were it belongs instead of plugging your uninteresting website.

Anon

Actually, http://php.socket7.net is the temporary resting place for PHPHead.com.

Since when does being a 20-something who truly loves PHP, and has fun on a website made for friends make you an uninteresting teenage haxor? Cute.