PHP Get Next/Previous MySQL Record

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

PHP Get Next/Previous MySQL Record

Post by curseofthe8ball »

We have a page which display a bio and need to add a link at the bottom which allows the user to navigate to the next or previous bio. We sort the bios by last name on the site so we'd like the previous/next links to cycle to the appropriate record using the same sort method.

Our query to display all of the bios looks like this:

Code: Select all

$qry = "SELECT * FROM bios WHERE status = 'active' order by `last_name` ASC";
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP Get Next/Previous MySQL Record

Post by califdon »

There are a number of ways to do what you want, depending on how many records you expect a user to need to navigate to, how much data you need to display about each one, etc.

Basically, you will need to decide whether you want to return to the server each and every time the user wants to view the previous or next record, or whether you want to send all the data to the browser the first time, in an array that allows the user to navigate a few records each way without returning to the server. In a typical operation of this sort, my first inclination would probably be to use AJAX to retrieve data after the first time.

The trick here is how to determine which is the "previous" record, in sorted order. One way you can do that is to send all the data to the browser as a Javascript array, which is then easy to navigate through, backward and forward, without returning to the server. But that is practical only if the number of records is reasonably small. I wouldn't recommend that if you have thousands of records that a user might want to navigate through. If it would satisfy your needs, though, to be able to go to a small number, say 10 records forward or back, you could use a more complex query process (probably a query based on another query) to deliver an array of, say 21 records, to the browser and have Javascript code that initially displays the 11th row of the array, then permits the user to go back or forward as many as 10 records.

As you can see, it depends on what your requirement is.
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Re: PHP Get Next/Previous MySQL Record

Post by curseofthe8ball »

Thanks for the reply.

I have no problem pinging the server each time to load the data. Ideally I'm just looking for a way to get the previous ID number and the next ID number to load into the various links.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: PHP Get Next/Previous MySQL Record

Post by twinedev »

Code: Select all

<?php

$aryBio = FALSE;
$intNextID = FALSE;
$intPrevID = FALSE;

$rsThisOne = mysql_query('SELECT * FROM `bios` WHERE `status` = "active" AND `id` = ' $intBioID);
if ($rsThisOne && mysql_num_rows($rsTHisOne)>0) {
    $aryBio = mysql_fetch_assoc($rsThisOne);
    mysql_free_result($rsThisOne);
}

if ($aryBio) {
    $strMresLastName = mysql_real_escape_string($aryBio['last_name']);
    $rsNextOne = mysql_query('SELECT `id` FROM `bios` WHERE `status`="active"` AND `last_name`>"'.$strMresLastName.'" LIMIT 1');
    if ($rsNextOne && mysql_num_rows($rsNextOne) > 0) {
        $intNextID = mysql_result($rsNextOne,0);
        mysql_free_result($rsNextOne);
    };
    $rsPrevOne = mysql_query('SELECT `id` FROM `bios` WHERE `status`="active"` AND `last_name`<"'.$strMresLastName.'" LIMIT 1');
    if ($rsPrevOne && mysql_num_rows($rsPrevOne) > 0) {
        $intPrevID = mysql_result($rsPrevOne,0);
        mysql_free_result($rsPrevOne);
    };
}

?>
Now, granted this code assumes there will not be more than one person with the same last name, but so does your sample code to get the ordered list.

That is one basic way, as mentioned, other ways, but for small site (not a ton of hits), should do ya.

-Greg

PS. Also, if you are going this route, make sure to add an index to the field last_name
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Re: PHP Get Next/Previous MySQL Record

Post by curseofthe8ball »

Greg,

I've tried the above and have added an index for the last_name field but I'm not getting any values passed to $intNextID or $intPrevID. No errors are appearing, just nothing is being passed to this variables.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: PHP Get Next/Previous MySQL Record

Post by twinedev »

I typed that up quickly, so may have something off. Did you get the correct value for $aryBio?

Oh wait, just saw it, I accidentally put a backtick after `status`="Active" in both of the queries there, that is probably the issue.

-Greg
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Re: PHP Get Next/Previous MySQL Record

Post by curseofthe8ball »

Greg,

I removed that backtick but still no luck. My code looks like this:

Code: Select all

$aryBio = FALSE;
$intNextID = FALSE;
$intPrevID = FALSE;

$rsThisOne = mysql_query('SELECT * FROM `bios` WHERE `status` = "active" AND `id` = "$id"');
if ($rsThisOne && mysql_num_rows($rsTHisOne)>0) {
    $aryBio = mysql_fetch_assoc($rsThisOne);
    mysql_free_result($rsThisOne);
}

if ($aryBio) {
    $strMresLastName = mysql_real_escape_string($aryBio['last_name']);
    $rsNextOne = mysql_query('SELECT `id` FROM `bios` WHERE `status`="active" AND `last_name`>"$strMresLastName" LIMIT 1');
    if ($rsNextOne && mysql_num_rows($rsNextOne) > 0) {
        $intNextID = mysql_result($rsNextOne,0);
        mysql_free_result($rsNextOne);
    };
    $rsPrevOne = mysql_query('SELECT `id` FROM `bios` WHERE `status`="active" AND `last_name`<"$strMresLastName" LIMIT 1');
    if ($rsPrevOne && mysql_num_rows($rsPrevOne) > 0) {
        $intPrevID = mysql_result($rsPrevOne,0);
        mysql_free_result($rsPrevOne);
    };
}
How can I troubleshoot where the issue is?
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: PHP Get Next/Previous MySQL Record

Post by twinedev »

Steps to troubleshoot would be

Start with the first query section you expect data that isn't getting it.
Add or die('ERROR WITH QUERY: '.mysql_error()); to the end of each mysql_query line (get rid of existing ending semi colon)

Now in this case, you will most likely not get anything (it probably is not actually erroring based upon what I see as wrong).

Next step, split the query out, (assign it to a variable, then use that variable in mysql_query()) Before the query, dump it out.

If all looks good**, copy the query, and manually run it against the database (using a program that does it, or using something like phpMyAdmin so you can see the direct results to see if you can see anything.

Notice my **'s up there. For the code you gave, you won't need to go past this point. When you echo out the actual SQL statement, you will see you are trying to execute:

Code: Select all

SELECT * FROM `bios` WHERE `status` = "active" AND `id` = "$id"
Hey, it doesn't have the actual ID in it, it has $id in there.... You can't just use a variable in a string like you did (need to do it like my example). Variables only evaluate when in a string wrapped in double quotes. Now, there are double quotes there, but they are part of the string, now wrapping it.

-Greg

PS. You get points for wanting to know how to troubleshoot it, not just "fix it for me" that is usually on forums ;-) Good to want to learn!
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Re: PHP Get Next/Previous MySQL Record

Post by curseofthe8ball »

Greg,

In the initial code, I was getting an error for this:

Code: Select all

$rsThisOne = mysql_query('SELECT * FROM `bios` WHERE `status` = "active" AND `id` = ' $id);
I've edited my code again but I'm still not getting any values to the two variables I need.

Code: Select all

$aryBio = FALSE;
$intNextID = FALSE;
$intPrevID = FALSE;

$rsThisOne = mysql_query('SELECT * FROM `bios` WHERE `status` = "active" AND `id` = "'.$id.'"');
if ($rsThisOne && mysql_num_rows($rsTHisOne)>0) {
    $aryBio = mysql_fetch_assoc($rsThisOne);
    mysql_free_result($rsThisOne);
}

if ($aryBio) {
    $strMresLastName = mysql_real_escape_string($aryBio['last_name']);
    $rsNextOne = mysql_query('SELECT `id` FROM `bios` WHERE `status` = "active" AND `last_name` > "'.$strMresLastName.'" LIMIT 1');
    if ($rsNextOne && mysql_num_rows($rsNextOne) > 0) {
        $intNextID = mysql_result($rsNextOne,0);
        mysql_free_result($rsNextOne);
    };
    $rsPrevOne = mysql_query('SELECT `id` FROM `bios` WHERE `status` = "active" AND `last_name` < "'.$strMresLastName.'" LIMIT 1');
    if ($rsPrevOne && mysql_num_rows($rsPrevOne) > 0) {
        $intPrevID = mysql_result($rsPrevOne,0);
        mysql_free_result($rsPrevOne);
    };
}
I'm banging my head against my desk here :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: PHP Get Next/Previous MySQL Record

Post by Christopher »

I haven't read the whole thread, but a couple things based on the last couple posts. I'd recommend assigning your SQL to a variable first so you can echo it so see what the syntax actually is. And I would recommend using single quotes in SQL so you can wrap the string in double quotes and thereby easily embed variables. The MySQL manual always shows single quotes in SQL examples so I think single quotes are the SQL standard.

Code: Select all

$sql = "SELECT * FROM `bios` WHERE `status` = 'active' AND `id` = $id";
$rsThisOne = mysql_query($sql);
echo "SQL=$sql, errmsg=" . mysql_error() . "<br/>\n";

$sql = "SELECT `id` FROM `bios` WHERE `status` = 'active' AND `last_name` > '$strMresLastName' LIMIT 1";
$rsNextOne = mysql_query($sql);
echo "SQL=$sql, errmsg=" . mysql_error() . "<br/>\n";

$sql = "SELECT `id` FROM `bios` WHERE `status` = 'active' AND `last_name` < '$strMresLastName' LIMIT 1";
$rsPrevOne = mysql_query($sql);
echo "SQL=$sql, errmsg=" . mysql_error() . "<br/>\n";
Also, it is not clear what the field types are. You are checking id which I assume is an integer. And, you are doing a less-than check on a CHAR/VARCHAR column. Make sure you understand what that will do.
(#10850)
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: PHP Get Next/Previous MySQL Record

Post by twinedev »

Christopher wrote:The MySQL manual always shows single quotes in SQL examples so I think single quotes are the SQL standard.
This made me curious as I have always used both. So I just looked it up. By default you can use single or double quotes, however it is possible to start up mysqd with a setting that will tell it that a double quote acts like a backtick (From: http://dev.mysql.com/doc/refman/5.0/en/ ... nsi_quotes )

Code: Select all

SELECT "field1" FROM "tablename" WHERE "field2" = 'text value' 
Interesting, never knew that.
I might start using them the other way around for SQL statement ;-) I've already started adapting table/database names to be this_style instead of thisStyle for scripts that may get used on a windows system, since windows behaves differently when it comes to case sensitivity (found the hard way after writing an app to run on a intranet running on windows.)

-Greg
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Re: PHP Get Next/Previous MySQL Record

Post by curseofthe8ball »

Took me a bit to find a solution that worked perfectly for my needs, but I did. I wanted to share here incase anyone had any ways to improve it and incase someone else is looking for a similar solution.

Code: Select all

$query1 = "SELECT * FROM bios WHERE status = 'active' order by `last_name` ASC";
$result1=mysql_query($query1) or die(mysql_error());

$fetch = mysql_fetch_array($result1);
while(is_array($fetch)) {
$term_array[] = $fetch['id'];
$fetch = mysql_fetch_array($result1);
}

while(list($k,$v)=each($term_array)){ if($id==$v){$pk=$k-1;$nk=$k+1; }}
reset($term_array);while(list($k,$v)=each($term_array)){if($k==$pk){ $intPrevID = $v; }}
reset($term_array);while(list($k,$v)=each($term_array)){if($k==$nk){ $intNextID = $v; }}

if (empty($intPrevID)) { $intPrevID = end($term_array); } else { $intPrevID = $intPrevID; }
if (empty($intNextID)) { $intNextID = reset($term_array); } else { $intNextID = $intNextID; }
Post Reply