0

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.

5
Contributors
7
Replies
13
Views
5 Years
Discussion Span
Last Post by raybigto
0

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.

0

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?

1

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)

Edited by |-|x

0

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.

0

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?

0

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!

Votes + Comments
Thanks for Sharing
0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.