Get next/prev record based on date

[grant.baker]

Ok, what I'm trying to do is have next and prev buttons to navigate through a mysql database sorted by date. Currently, the code I wrote does this, but gets stuck on duplicate dates.

$Next = mysql_query("SELECT * FROM $logbook WHERE Date>='$Date' AND ID!='$ID' ORDER BY Date LIMIT 1");
$Prev = mysql_query("SELECT * FROM $logbook WHERE Date<='$Date' AND ID!='$ID' ORDER BY Date DESC LIMIT 1");

When there are duplicate dates, this code returns the same id for both $Next and $Prev. I also tried:

$Next = mysql_query("SELECT * FROM $logbook WHERE Date>='$Date' AND ID!='$ID' ORDER BY Date, ID LIMIT 1");
$Prev = mysql_query("SELECT * FROM $logbook WHERE Date<='$Date' AND ID!='$ID' ORDER BY Date, ID DESC LIMIT 1");

This always returns the earliest Date instead of the previous one for $Prev, so I tried:

$Next = mysql_query("SELECT * FROM $logbook WHERE Date>='$Date' AND ID!='$ID' ORDER BY Date, ID LIMIT 1");
$Prev = mysql_query("SELECT * FROM $logbook WHERE Date<='$Date' AND ID!='$ID' ORDER BY Date DESC, ID ASC LIMIT 1");

The output from this query is the same as the first. The reason I can't simply sort by ID (which is an auto-incrementing field) is that entries may be post dated (Date is not a timestamp). Therefore, an entry with id 888 may need to come before an entry with id 877 because it has an earlier date. So what I need help figuring out is how to deal with duplicate dates.

[smantscheff]

Maybe you could use an arbitrary sorting using the IDs. If the order of posts with the same date does not matter, increase the limit parameter and keep the date until there are no more posts for this date.

SELECT * FROM $logbook WHERE Date>='$Date' AND ID!='$ID' ORDER BY Date, ID LIMIT $i, 1

Increase $i until there are no more posts.

[grant.baker]

I'm having trouble grasping how to implement your suggestion. And couldn't iterating with php result in a lot of queries and unnecessary server load?

[|-|x]

try maybe sorting on ID and date both descending

SELECT * FROM $logbook WHERE Date<='$Date' AND ID!='$ID' ORDER BY Date DESC, ID DESC LIMIT 1

you could also add an extra clause to exclude the previously selected ID AND ID!='$Next["ID"]'

(of course you need to grab the value from the MySQL result properly, but you get the idea)

[grant.baker]

I'll try using a session variable to keep track of which duplicate dates I've already looked at. I'll post what I find.

[Biiim]

Pretty rough but you could pull the amount of duplicates and increment them in the php script

$Next = mysql_query("SELECT `fields`,count(*) AS `numposts` FROM $logbook WHERE Date>='$Date' AND ID!='$ID' GROUP BY `date` ORDER BY Date, id LIMIT ".$numposts+1.",1");
$Prev = mysql_query("SELECT `fields`,count(*) AS `numposts` FROM $logbook WHERE Date<='$Date' AND ID!='$ID' GROUP BY `date` ORDER BY Date DESC, id LIMIT ".$numposts-1.",1");

once next hits numposts, reset numposts to 0 and make where date > $date and previous is more complex cause once you are at 0 and click previous you will need to work out how many posts are on that date and view the last one.

As a second thought, It might be easier to just add time to it?

[grant.baker]

Hey guys, thanks so much for the help. I finally figured it out, and I didn't end up using sessions. Here's what I ended up with:

$ID = $_GET['Record'];
$result = mysql_query("SELECT * FROM $logbook WHERE ID='$ID'");
$result=mysql_fetch_array($result);
$Date=$result['Date'];
$Rows=mysql_query("SELECT * FROM $logbook WHERE Date='$Date' ORDER BY Date ASC");
$Num=mysql_num_rows($Rows);
if($Num==1){ //If there's only 1 record, get record from next/previous date
    $Next = mysql_fetch_array(mysql_query("SELECT * FROM $logbook WHERE Date>'$Date' ORDER BY Date LIMIT 1"));
    $Prev = mysql_fetch_array(mysql_query("SELECT * FROM $logbook WHERE Date<'$Date' ORDER BY Date DESC LIMIT 1"));
}
else{ //If there is more than 1, put them in an array and get position of current ID
$i = 0;             //counter so we know where our current record is located
while( $data = mysql_fetch_assoc($Rows) ) {
    $records[] = $data;
    if( $ID == $data['ID'] ) {
        $currentRecordPosition = $i;
    }
    $i++;
}  

//If there is a record in the previous/next array position, get ID from array
//If not, get ID from record with next/previous date

    if( is_array($records[$currentRecordPosition - 1]) ){
        $Prev = $records[$currentRecordPosition - 1];    
    } else { 
        $Prev = mysql_fetch_array(mysql_query("SELECT * FROM $logbook WHERE Date<'$Date' ORDER BY Date DESC, ID DESC LIMIT 1"));
    }

    if( is_array($records[$currentRecordPosition + 1]) ){
        $Next = $records[$currentRecordPosition + 1];    
    } else {
        $Next = mysql_fetch_array(mysql_query("SELECT * FROM $logbook WHERE Date>'$Date' ORDER BY Date, ID LIMIT 1"));
    }
}

Hope this helps somebody with a similar problem!

[raybigto]

For the NEXT line 30 I had to change it to:

if($currentRecordPosition + 1 < $i)

instead of:

if( is_array($records[$currentRecordPosition + 1]) )

similar for the PREVIOUS too.