Move thru Mysql records using PHP

programming
1

Hello All -

I am having an issue browsing thru my dB records using PHP. I have an entire back end built where the customers populate and i can paginate through them. When you click on a customer, all of their details show up.

Im trying to add a prev, next button to this detail page so I can move thru each record based on Last Name w/o having to go back to the paginated page showing the results.

Here is the code I have currently. This works by using the +1 -1 to the ID column. The problem is that when alphabetized, the user IDs are out of order. Please Help!!

Thanks,


if(isset($_GET['ID']) && (int)$_GET['ID']) $ID = $_GET['ID']; 
   else $ID = 1; 
   $query = "SELECT * FROM table WHERE ID = '$ID' LIMIT 1"; 
   $result = mysql_query($query); 
   $self = $_SERVER['PHP_SELF'];
   if($num=mysql_numrows($result)) 
   { 
   $record = mysql_fetch_assoc($result); 
   $name = $record["LASTNAME"]; 
   } 
   $next = $ID + 1; 
   $prev = $ID - 1;

Here are the links :


<a href="<? $self ?>?loc=view_contact_detail&ID=<? echo $prev;?>">Prev</a> | <a href="<? $self ?>?loc=view_contact_detail&ID=<? echo $next;?>">Next</a>

Thanks again,

2

you may have to use two different queries depending on whether ID is set or not, so if ID is set it queries by last name instead of ID.

3

You don’t need 2 queries - MySQL comes with the offset parameter used in conjunction with LIMIT - which means no matter the sort order you can get the next record by LIMITing to 1 and offsetting by a number of records.

Then you can just pass the sort order in as a string and build the query dynamically

“SELECT * FROM table WHERE $SortColumn = ‘$SearchCriteria’ ORDER BY $SortColumn LIMIT $OffsetAmount, 1”

Hmm thinking about that you’ll run into the ‘how do I get the next/prev rows’ problem…

Hang on I’ve done this on another post…

All I did was used a couple of subqueries to get the extra rows


[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]*[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ID [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] $SortColumn [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]table [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ID [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'$ID'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] info [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] table[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]$SortColumn [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] info[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]$SortColumn [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ORDER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] table[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]$SortColumn LIMIT 1[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] lineInfo [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] lineInfo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ID [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] table[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ID[/SIZE]

So what this does is:

The most inner subquery selects the row specified by $ID from the table.

The subquery outside this joins to that using the specified sort column but selects one single row which has a sortcolumn with a value greater than the specified row.

The outer query selects the row from table based on the ID passed from the subqueries.

What you end up with is the next row in the table … so gimme a minute to modify it and I can give you a result which contains the current row and the next/prev rows. Then you can provide PHP logic which sets your vars and you only need 1 query and a couple of vars

Ok the query is as follows - this will get the next/prev records when given an ID as a target record and a sort column


[SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]SELECT *, 1 as NextRec[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]FROM People [/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]INNER JOIN [/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]([/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]SELECT People.ID FROM People INNER JOIN (SELECT $SortColumn FROM People WHERE ID = '$ID' ) as info ON People.$SortColumn > info.$SortColumn ORDER BY People.$SortColumn LIMIT 1[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]) as nextrec ON nextrec.ID = People.ID[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]UNION ALL[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]SELECT *, 0 as NextRec[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]FROM People [/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]INNER JOIN [/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]([/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]SELECT People.ID FROM People INNER JOIN (SELECT $SortColumn FROM People WHERE ID = '$ID' ) as info ON People.$SortColumn < info.$SortColumn ORDER BY People.$SortColumn DESC LIMIT 1[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]) as prevrec ON prevrec.ID = People.ID[/COLOR][/SIZE][/COLOR][/SIZE]

Then all you need to do is assign the record that has a column value of 1 for NextRec to your next record var, and vice versa.

Attached is a proof of concept in SQL 2005

4

good thing i say may :lol: nice work Charleh

5

WOW - Thanks for the comprehensive reply. It makes logical sense, I will try to impliment that code today and see if I can get it working.

Thanks alot!