Moveprevius problem

[TomasART]

I want to have a movenext and move previus option in me site. De next work perfectli, but the move previus return the firstrecordset. Here are the code.

// Next recort work fine
$next_rec = "select * from people where contactid > $rec_id limit 1";

// previus record dont work, return the first record
$next_rec = "select * from people where contactid < $rec_id limit 1";

Any solution ?

Thanks,
Tomás

[Purple]

Hi,

The get next is working because you are asking for all records starting from a specified point to a limit of 1. The get previous is getting all records with a value less than the start point to a limit of 1 which is the first record in the table in standard sort order.

If you change the select statement for the previous to include

order by contactid desc

you will get the records you are after but in reverse sort order

So you can either nest two select statements to correct the order or select into an array and sort in PHP (doing it in the SQL should be quicker)

Purple

[Purple]

Hi,

Apologies for the double post, it has been a while since I have worked on MySQL (the solution proposed will work on MSSQL which at 2000 misses the ability to use limit and offset)

So the better way to the previous page is :

[PHP]SELECT * FROM table
LIMIT 10 OFFSET 100[/PHP]

where you want to return ten records from one hundred. So:
Offset = start point - number of records per page
Limit = number of records per page.

Hope this helps

Purple

[TomasART]

mmm not wotk the offset. Do nothing :(.

Any other idea?

[Purple]

Hi,

How many records in the table ? Are you getting an error back ? Have you got error messages turned on ?

Purple

[TomasART]

NO, no error, do nothing. 4000 records.

Thanks.

[Purple]

Hi Tomas,

can you echo the sql statement you are processing and post it up plse.

Purple

[TomasART]

//NEXT RECORD. WORKS FINE
$querySEG = "SELECT * FROM products_description where product_id > '".$id."' AND campaign_categorie_id = '" . $categoria ."' ORDER BY '".$id."' LIMIT 1";
$resultSEG = mysql_query($querySEG, $this->link) or $this->_Error(mysql_error(), 'ERROR | DB_ERROR'); 
$rowSEG = mysql_fetch_array($resultSEG);
 
//PREVIUS RECORD. DONT WORK :(     
$queryANT = "SELECT * FROM products_description where product_id < '".$id."' AND campaign_categorie_id = '" . $categoria ."' ORDER BY '".$id."' DESC LIMIT 1";
$resultANT = mysql_query($queryANT, $this->link) or $this->_Error(mysql_error(), 'ERROR | DB_ERROR'); 
$rowANT = mysql_fetch_array($resultANT);
 

[Purple]

Hi,

//PREVIUS RECORD. DONT WORK :( 
$queryANT = "SELECT * FROM products_description where product_id < '".$id."' AND campaign_categorie_id = '" . $categoria ."' ORDER BY '".$id."' DESC LIMIT 1";
$resultANT = mysql_query($queryANT, $this->link) or $this->_Error(mysql_error(), 'ERROR | DB_ERROR'); 
$rowANT = mysql_fetch_array($resultANT);

the ORDER BY ".$id." DESC LIMIT 1 should reference a column not a row
try changing to ORDER BY product_id DESC LIMIT 1

I am a little suprised this did not generate an error

Purple

[Purple]

Also..

if that works, I suggest you work on the offset approach cause it is significantly more efficient.

Purple