previous next links

[dwperry1]

I have the following code allowing me to navigate to the previous or next record in the db.  In the code below, I would like to swap 'new_date' (which is in the 000-00-00) format for the existing 'record_id' in the script.  I need to navigate via the new_date because it is possible with my program to delete a record and reenter it later for that passed date causing the record_id to be out of sinc with the reord_id.

 

Example:  My record_id is auto-increment so if I find a mistake in a past record and go to fix it for that date in the past, my auto-increment would be 235, 236, 237, 568, 239, 240 etc., so I  can't use record_id to navigate next and previous. 

 

When I use new_date instead of record_id, my error says either that the query was empty, or that Variable id not defined. Script terminating.

 

I would appreciate any help you can give, Thanks!

 

Doug

 

This is also on a web page, so here's my code:

 

php:

 

$current_id = $_GET['record_id'];
$record_id = $current_id;

$prevquery = "SELECT * FROM daily_sales WHERE record_id < $current_id ORDER BY record_id DESC LIMIT 1";

echo $current_id;
$prevresult = mysql_query($prevquery) or die(mysql_error());
while($prevrow = mysql_fetch_array($prevresult))
{
$previd = $prevrow['record_id'];
}

$nextquery = "SELECT * FROM daily_sales WHERE record_id > $current_id ORDER BY record_id ASC  LIMIT 1";
$nextresult = mysql_query($nextquery) or die(mysql_error());
while($nextrow = mysql_fetch_array($nextresult))
{
$nextid  = $nextrow['record_id'];
}

 

 

 

html:

 

<a href="http://www.mywebsite.com/view.php?record_id=<?php echo $previd; ?>">Previous</a>

<a href="http://www.mywebsite.com/view.php?record_id=<?php echo $nextid; ?>">next</a>

 

 

[Psycho]

You are making this more difficult than it needs to be! Your prev and next links don't need to have the IDs of those prev/next records in them. In fact, it is a waste to run those two queries. Your prev/next links can instead have a current ID and then a second parameter to define whether it is prev/next. then the receiving page will use that information to get the prev or next link

 

 

EDIT: Here is some sample code to illustrate

$rec_id = $_GET['record_id'];
$COMPARE_OP = '=';
if(isset($_GET['offset']))
{
    if($_GET['offset']==1) {
        $COMPARE_OP = '>';
    } else {
        $COMPARE_OP = '<';
    }
}

//Get the current record
$query = "SELECT *
          FROM daily_sales
          WHERE record_id {$COMPARE_OP} {$rec_id}
          ORDER BY record_id DESC
          LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
$current_record = mysql_fetch_assoc($prevresult);
$current_id = $current_record['record_id'];
 
//Insert code to display the current record


//Create links to prev and next records
echo "<a href=\"http://www.mywebsite.com/view.php?record_id={$current_id}&offset=-1\">Prev</a>";
echo "<a href=\"http://www.mywebsite.com/view.php?record_id={$current_id}&offset=-\">Next</a>";

[dwperry1]

Thanks for your input.  It looks like the code should work, but when I run it I get the following error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /www/htdocs/applealleycafe.com/books/view.php on line 322
 

[Psycho]

I said the code was for illustrative purposes - not that it was rady to copy/paste into your application. Besides, how the heck am I supposed to know where line 322 is? But, the query is apparenlty failing. Echo it to the page to verify the content

[dwperry1]

I really do appreciate your quick response and expertise. 

 

Line 322 is:   $current_record = mysql_fetch_assoc($prevresult);

 

Sorry.

 

The error message says it is not a valid argument, but I'm not experienced enough to know how to find out what is making it not work.

 

Any suggestions?

[boompa]

Try

 

 

$current_record = mysql_fetch_assoc($result);

[dwperry1]

I looked over the code more carefully and changed this:

 

$result = mysql_query($query) or die(mysql_error());

 

to this:

 

$prevresult = mysql_query($query) or die(mysql_error());

 

This got rid of the error, but the links don't work.  I will look at them next.  The code is good if I can make it work.  It is a much better approach to the problem.

 

Thanks for giving me something better to work with!

[dwperry1]

When I click on the links, both give me the following error message.

 

Variable id not defined. Script terminating.

[dwperry1]

I got the script to work, but I still have the same problem of skipping files because the code is still based on the record_id and not the date associated with the page.

[dwperry1]

Does anyone know how to associate the date with the file?  I have two column names with dates: new_date in the 01/01/2013 format and record_date with the 000-00-00 format.  when I run the original code with the record_id, everything works, but I can't seem to get the others to work.

 

Any help would be appreciated.  Thanks!