?
Solved

Previous / next row question

Posted on 2010-01-11
18
Medium Priority
?
517 Views
Last Modified: 2012-05-08
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!
<?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>';
?>

Open in new window

0
Comment
Question by:priktop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
18 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26288274
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

0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26288281
The applies to the second SQL statement as well - '$date' rather than $date
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26288664
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() );

Open in new window

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26288752
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>';
?>

Open in new window

0
 

Author Comment

by:priktop
ID: 26290532
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.
0
 

Author Comment

by:priktop
ID: 26290589
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
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26291198
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)
0
 

Author Comment

by:priktop
ID: 26291854
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.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26292210
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?
0
 

Author Comment

by:priktop
ID: 26292722
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() );
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 26293065
If I am understanding you correctly, you changed the code I recommended.  Your code said this:

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

But I specifically recommended this, for a variety of reasons:

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

I'll try to post an example of how I would do this (rather than how I would fix your code) and maybe that will work better.  Unfortunately I cannot test the suggested code, so I am dependent on you to do that... Please do not make changes unless you know that your changes are useful improvements - omitting error reporting when we are trying to debug is not a helpful thing to do.

Thanks and regards, ~Ray
<?php
error_reporting(E_ALL); // VITALLY IMPORTANT TO SEE ALL ERRORS, WARNINGS, AND NOTICES

// CONSTRUCT A QUERY
$sql = "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";

// RUN A QUERY
$res = mysql_query($sql) or die( mysql_error() );

// GET THE RESULTS SET FROM THE RESOURCE
$row = mysql_fetch_assoc($res);

// ACTIVATE THIS LINE TO SEE THE ROW OF DATA
var_dump($row);

// GET THE LOCAL DATA ELEMENT FROM THE ROW
$f_open = $row["f_open"];

// CONSTRUCT AND RUN TWO QUERIES USING THE DATE BUT CALLING IT $f_open
$psql = "SELECT id FROM party WHERE date < '$f_open' LIMIT 1";
$nsql = "SELECT id FROM party WHERE date > '$f_open' LIMIT 1";

$pres = mysql_query($psql) or die( mysql_error() );
$nres = mysql_query($nsql) or die( mysql_error() );

// ASSUMPTION - NO RESULTS
$pre_id  = FALSE;
$next_id = FALSE;

// TEST TO SEE IF WE GOT RESULTS AND CAPTURE THE RESULTS, IF ANY
if (mysql_num_rows($pres))
{
    $prow = mysql_fetch_assoc($pres);
    $pre_id  = $prow["id"];
}

if (mysql_num_rows($nres))
{
    $nrow = mysql_fetch_assoc($nres);
    $next_id = $prow["id"];
}

// 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>';
}

Open in new window

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 26293084
Please revise line 43 to this...  And feel free to correct any of my typos - I am the widely acknowledged king of typos.  ;-)
$next_id = $nrow["id"];

Open in new window

0
 

Author Comment

by:priktop
ID: 26295299
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!
0
 

Author Comment

by:priktop
ID: 26295376
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!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26295480
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.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26295501
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";

Open in new window

0
 

Author Closing Comment

by:priktop
ID: 31675770
Great help! Thanks :-)
0
 

Author Comment

by:priktop
ID: 26296330
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";
?>

Open in new window

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question