priktop
asked on
Previous / next row question
Hello,
I have a database with events (parties) in it, and i'm calling the first row. Then i'm checking if there are more entries, sorted on date. If there is any, a link to that row has to appear. Below is the code, but it just doesn't work.
The database HAS parties in it which happen after the first so a 'Next record' HAS to appear. What am i doing wrong?
Also, I want the script to get the upcoming event, so that it checks the current date and than gets the row which has the nearest (upcoming) date. But i have absolutely no clue how to do this.
Thanks in advance!
I have a database with events (parties) in it, and i'm calling the first row. Then i'm checking if there are more entries, sorted on date. If there is any, a link to that row has to appear. Below is the code, but it just doesn't work.
The database HAS parties in it which happen after the first so a 'Next record' HAS to appear. What am i doing wrong?
Also, I want the script to get the upcoming event, so that it checks the current date and than gets the row which has the nearest (upcoming) date. But i have absolutely no clue how to do this.
Thanks in advance!
<?php
$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());
while($r = mysql_fetch_array($select)){
extract($r);
}
$pre_id = @mysql_result("SELECT id FROM party WHERE date < $date LIMIT 1", 0);
$next_id = @mysql_result("SELECT id FROM party WHERE date > $date LIMIT 1", 0);
?>
<?=$f_open?> - <img src="images/logo.png" alt="" /><br />
<?
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>';
?>
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
$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() );
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
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>';
?>
ASKER
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_ht ml/index.p hp 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.
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_ht
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.
ASKER
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
"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)
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)
ASKER
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.
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?
// 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?
ASKER
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.ph p 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() );
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/public_html/index.ph
Where line 16 is:
$pre_id = mysql_result("SELECT id FROM party WHERE date < '$date' LIMIT 1", 0) or die( mysql_error() );
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
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!
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.
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
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";
ASKER
Great help! Thanks :-)
ASKER
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";
?>
$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