MySQL query result algorithem

Anon

Hello People
Imaine the search results you get from YAHOO for example. The first page contains 20 results and in the bottom there are numbers corresponding to the number of pages (pervious or next as well as the page itself). How is it done?
Can anybody give some hints? now another problem is that when they click on a specific record summary and the go the page containing detailed infomratio. They should be able to get back to the list on the right page without needing to do the querry over again.
Help I am new with PHP and am going insane trying to figure this our.

thanks
Simon

bretticus

Well, off the top of my head, the simplest way to do it is to use two select querys:

In the 1st query do a "select count(*) from table where column(s) = 'conditions'"

use PHP to get that value, that's your "Yahoo found this many results".

In the 2nd query do a "select column(s) from table where column(s) = 'conditions' LIMIT 0,20"

If you are not familiar with LIMIT, the first number is a record index (record 1 is indexed 0 like an array index). The next number tells MySQL how many rows to return.

Now to do this dynamically, you need to do some trivial math...well, this is how I do it.

1) I set a variable $page = 1 if it's not set already. example:

if (!isset($page)){$page = 1;}

2) I set a variable to store how many records I wanna return for each page:

$LIMIT = 10;

3) Next, I use the following statement:

$CURR_INDEX = ($page * $LIMIT) - $LIMIT;

so if $page is 1...1*10-10 = 0. I get the index 0 for page 1.

if $page = 2...2*10 = 20 - 10 = 10. I get the index 10...and so on

So now I simply use "select column(s) from table where column(s) = 'conditions' LIMIT $CURR_INDEX,$LIMIT"

next just link your previous and next links appropriately so that if you your browser url says /script.php?page=1 your link for next is /script.php?page=$page + 1 and vice-versa for previous. Simply if else statements.

Enjoy