Hi Elly,
Yes, what you need is a lot more sophisticated than the one described in PHPBuilder.
The boolean search criteria sounds tricky if you want to scan more than one column on your table(s). As long as you know which column a keyword applies to though it shouldn't be too difficult to construct a query in PHP, just a lot of AND's, OR's, LIKE's and IN's. I'm just trying to build a search engine myself (that's why I was looking through PHPBuilder) - not as sophisticated as what you're after but with some boolean functions. I'll keep you posted on my progress if you like and think it will help.
As for presenting the results two solutions spring to mind - one messy but not too demanding on the server and the other neater but hammering the database.
Neater one first:
1) construct the FROM, WHERE and ORDER BY parts of your query statement from the search criteria
2) stick a SELECT COUNT() on the front and run it, divide the result by 10 and round up (if there is a remainder) - this will give you the number of results pages you will need and the records retrieved counter for your heading.
3) replace the SELECT COUNT() with the SELECT list you actually want
4) add a LIMIT statement to the end of the query with the 'start' parameter set to a PHP variable ($skip) and the 'rows' parameter set to 10. The 'start' parameter tells MySQL to skip that many rows before returning any data
5) construct the |PREV|1|2|3|......|NEXT| bar with each option coded as an <A> tag. the URL should be $PHP_SELF (the current script) and include the record number each page should start with as a parameter in the search list of the URL. Something like this:
<A HREF="$PHP_SELF?$skip-10">PREV</A>
/ only if $skip > 10! /
<A HREF="$PHP_SELF?10>1</A>
<A HREF="$PHP_SELF?20>2</A>
.
.
<A HREF="$PHP_SELF?$skip+10>NEXT</A>
/ not if $skip+10 > number retrieved! /
At the top of the script load the QUERY_STRING (the parameter) into $skip. This way each time the script is called $skip is substituted
into the LIMIT statement to control the record number the query starts with and into the PREV and NEXT <A> tags to control their start points.
The ORDER BY statement is important here to make sure that the results are always returned in the same order to avoid duplicating or missing rows.
6) initialise $skip to zero, run the query to fetch the first ten rows, display them and exit. It should take care of itself after that.
Possibly a bit heavy on database access but hell - MySQL is fast!
The messier way:
Same sort of technique as before but run the query without a LIMIT statement and run the whole result set out to a flat file. Use the
$skip parameter idea to position yourself in the flat file and just read and display 10 records from there.
If this is unhelpful, unwanted or just plain boring please feel free to tell me to shut up.
Regards,
Phil
