splitting search results into pages?

sneakyimp

hi:

i am writing some search functionality for a dating website. I want to display results in pages of 10 or 25. it's obviously a waste to query the database for the full search every time.

I'm confused about the SQL angle though...how do you limit the number of records returned by altering your SQL query? what does that SQL look like.

and when the user clicks past the first page, how do you alter your sql to get records 11-20 rather than 1 through 10?

drew010

you can use LIMIT in your queries to limit the number of results.

SELECT stuff FROM table WHERE something LIMIT 0, 25

the first part, 0, specifies what record to start at (record counts begin at 0, not 1), and the 25, tells how many records to return.

So LIMIT 100, 25 would show 25 records starting from number 101.

you can use the query string to pass along the starting record, and leave the 25 part static or retrieve it from user preferences. you may need to error check the start portion if it is in the query string in case the user puts negative numbers or words, it could mess up your query and case some mysql errors in your script.

sneakyimp

THANKS, drew! and dang but that was fast...

So you're saying pass the current record offset in the query string. I'm using POST so I reckon I would put it there in a hidden input. But it sure sounds like a pain to pass all the variables in a POST (there are like 30 of them).

What I will probably do is put the sql query that i have painstakingly constructed from several dozen checkboxes into a POST field and the offset in a separate POST field and maybe pass it that way....if you click "next 25", you will submit a tiny POST form with the sql query and offset as your fields.

my search page currently submits data to itself (that is to say my search.php page has ACTION="search.php" in the search form).

should I submit instead to a search_results.php page?

drew010

yeah in this case since you have prev next buttons, just pass along the offset in a hidden form field. probably submit to search.php

sneakyimp

OK...i think i have this working...but i still am wondering about a couple of things...

what is the most effective way to determine if you have shown the last record (so you don't foolishly put a NEXT button on there).

should i run another query without the LIMIT stuff? that seems like a bit much querying to me.

isn't their a COUNT keyword in sql?