Previous / next row question

I suspect your SQL statement. First, quote your dates.

$pre_id = @mysql_result("SELECT id FROM party WHERE date < '$date' LIMIT 1", 0);

 Second make sure that the values in $date are in yyyy-mm-dd format

The applies to the second SQL statement as well - '$date' rather than $date

Wow, we don't see that kind of statement used much any more:

$next_id = @mysql_result("SELECT id FROM party WHERE date > $date LIMIT 1", 0);

The reason nobody does that is because you have no way of knowing whether the statement worked or not.  MySQL is not a black box - it can and does fail for a variety of reasons, and you MUST test the return values from your queries or you will have undetectable errors.  I think you have undetectable errors here, so let's change the PHP code so that we can detect the errors and print them out.

Suggest you check the man page here:
http://us.php.net/manual/en/function.mysql-result.php

Then revise your query so you can see if it worked and if not, why not.

best, ~Ray

<?php // SAMPLE ONLY - NOT WORKING CODE
error_reporting(E_ALL);

$next_id = mysql_result("SELECT id FROM party WHERE date > $date LIMIT 1", 0); 
if ($next_id === FALSE) die( mysql_error() );

Select allOpen in new window

Please see the comments in the code below - this is what I think is happening, so please post back and correct me if I am wrong.

It looks like there could be a few things out of whack here.  First of all, "date" is a reserved word in MySQL and so you should probably not use it as a column name.  I am not sure from the query whether you did that or not, but if you did, it would be good to change it.  Too much chance for confusion.  And it looks like when you select the date, you name it f_open with the AS part of the SELECT.  So when you have done the extract, you will have a variable named $f_open, but nothing named $date - unless it is left in the script namespace from some other place.

Anyway, I hope that printing out the error messages and visualizing the data can be helpful to you in figuring out the issues here.  Cheers, ~Ray

<?php
// RUN A QUERY
$select = mysql_query("SELECT date_format(date,'%d/%m/%y') AS f_open, id, name, basename, edition, place, location, website, lineup, date, time, presale, door, age FROM party ORDER BY date LIMIT 1") or die(mysql_error());

// MAKE AN UNNECESSARY LOOP EXTRACTING ALL THE ROWS - BUT WE HAVE ONLY ONE ROW
while($r = mysql_fetch_array($select))
{
    extract($r);
}

// NOW DUMP THE ROW SO WE CAN SEE WHERE THE VALUE OF $r['date'] APPEARS - MAYBE NOWHERE 
var_dump($r);

// RUN TWO QUERIES USING THE DATE BUT CALLING IT $f_open
$pre_id  = mysql_result("SELECT id FROM party WHERE date < '$f_open' LIMIT 1", 0) or die( mysql_error() );
$next_id = mysql_result("SELECT id FROM party WHERE date > '$f_open' LIMIT 1", 0) or die( mysql_error() );

// PRINT OUT THE DATE FIELD
echo $f_open;

// DROP OUT OF PHP FOR A LITTLE BIT OF HTML
?>
 - <img src="images/logo.png" alt="" /><br />
 
<?php
// BACK INTO PHP FOR TWO ECHO STATEMENTS
if ($pre_id)  echo '<a href="index.php?id='.$pre_id.'">Previous record</a>';
if ($next_id) echo '<a href="index.php?id='.$next_id.'">Next record</a>';
?>

Select allOpen in new window

Hi guys, thanks for all the replies.

First of all, i do have my format as yyyy-mm-dd format. The data type of the field is DATE aswell.
Second of all, the query opens date AS F_open, but also date itself (later on in the query). The reason i call it as f_open is to output is as dd/mm/yy format.

Now, i tried changing the column to 'pdate' but that did not help. Putting $date in brackets did not help either.

To answer Ray.
Your first suggestion outputs the following:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/domains/nl/public_html/index.php on line 19

where line 19 is:
$next_id = mysql_result("SELECT id FROM party WHERE date > '$date' LIMIT 1", 0);

By the way, when i echo $date i get '2010-01-09'. Other entries are dates after this one, so that's why i don't get why it just won't work.

Then i tried the var_dump($r);, which gave me '[1]=> string(10) "2010-01-09" ["date"]=> string(10) "2010-01-09"' at 'date'

(by the way, var_dump($r) did not work in your code Ray, i had to move it up 3 lines before the while bracket closes :))

Thanks you guys.

I tried putting the query

"SELECT id FROM party WHERE date > '2010-01-09' LIMIT 1"

in phpMyAdmin, and it gives me the correct id of the record after it, so it's not the query.
But when i Google on the error i get they all say it's a query error? :S

Have you actually connected to the database in PHP?

Does the extract() function actually work and even if it does, how do you get the variable result back to the main program? (I assume that it takes a row and "manufacturers" variables based on the row because f_open seem to come from nowhere)

Yes it is connected. It displays the first row and i can echo $date, so that's not it.
Everything works except browsing back and forth through my records.

OK - this loop


// MAKE AN UNNECESSARY LOOP EXTRACTING ALL THE ROWS - BUT WE HAVE ONLY ONE ROW
while($r = mysql_fetch_array($select))
{
    extract($r);
}


Will run until mysql_fetch_array returns FALSE at which point $r is FALSE. Since the comment says that you only have one row wouldn't you be better off with

$r = mysql_fetch_array($select);

and no loop?

Nope, thought you had it, but unfortunately still this error:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/public_html/index.php on line 16


Where line 16 is:

$pre_id  = mysql_result("SELECT id FROM party WHERE date < '$date' LIMIT 1", 0) or die( mysql_error() );

Thanks Ray, it worked!

Only one problem though. Next records work fine, but when i'm at the last record and hit 'previous record', it goes to the first one? I tried to figure it out myself but no luck so far....

But i'm almost there!

Oh it get's more complicated.
Apparently the code searches for the nearest id that has a bigger date.

So for example i have this set:

id | date

20 | 2010-01-15
21 | 2010-04-12
22 | 2010-02-01

it will fetch id 21 after 20, but date-wise it should get id 22.....

So i guess i need to add something to these 2 lines?

$psql = "SELECT id FROM party WHERE date < '$date' LIMIT 1";
$nsql = "SELECT id FROM party WHERE date > '$date' LIMIT 1";

Thanks for your help so far guys, it's been really useful!

add something to these 2 lines?

Yes, probably some kind of ordering would do the trick, or maybe a SELECT MAX() or SELECT MIN() to get the values adjacent to the row that has $date.

You might get it with something like this (untested) or you might try using a single SELECT statement with the mysql_data_seek() function to move forward and back one.

Best regards, ~Ray

$psql = "SELECT MAX(`date`), id FROM party WHERE date < '$date' LIMIT 1";
$nsql = "SELECT MIN(`date`), id FROM party WHERE date > '$date' LIMIT 1";

Select allOpen in new window

Great help! Thanks :-)

FInally got it worked how i wanted it with the code below. Thans to everybody who helped!

<?php
$psql = "SELECT id FROM party WHERE date < '$date' ORDER BY date DESC LIMIT 1";
$nsql = "SELECT id FROM party WHERE date > '$date' ORDER BY date LIMIT 1";
?>

Select allOpen in new window