Page 1 of 1

Advanced pagination puzzle

Posted: Sat Sep 23, 2006 12:14 pm
by nates310
ok, I've got the whole pagination thing down. I can go through my records 10 at a time and have a pre 123 next at the bottom to move through the pages. typically, this works because your query from the MYSQL table is ordered by the key field which is an integer. Each "next" record is achieved by indexing the key up one notch. But now, I have a new puzzle. I want to apply that sort of logic to an entirely different senario. I want to query my MYSQL table and order by the name column alphabetically. I want to show a table that lists everyone's name. I can click on any name and it will take me to a detail page of that name showing all their contact info. At the bottom of this second page, there would be a prev and next button that would take me to the previous or next record alphabetically. Any ideas? I can't really wrap my mind around the logic of this thing. Maybe it's simple and I've just got coders block, but I need some help.

Posted: Sat Sep 23, 2006 12:51 pm
by Christopher
The ORDER BY clause in SQL will allow you to sort by different columns.

Posted: Sat Sep 23, 2006 12:54 pm
by nates310
while that is true, it does not answer the question. I could easily create a table that listed everyone alphabetically... that's no problem. The issue is moving between detail pages. If I were on the detail page for Bob Smith and viewing his contact info, but now I want to click next so that the detail page will show the next alphabetical listing (say for Kiefer Sutherland). If I were moving through them by order of the primary key, i would simply index the key up one and life would be good ($i++) but how would you do that if you wanted the next name in the alphabet???

Posted: Sat Sep 23, 2006 12:59 pm
by Christopher
That's not the politest response given that you are asking for free help. Your question was "Any ideas?". Perhaps you could ask more specific questions.

Posted: Sat Sep 23, 2006 1:01 pm
by nates310
i apologize, i wasn't trying to be rude. It just seemed as if you hadn't read the whole post. I edited my response to help give some details

Posted: Sat Sep 23, 2006 1:30 pm
by Ollie Saunders
nates310, how are you doing your selection?

I have a sneeky feeling you are doing:

Code: Select all

SELECT `stuff` FROM `table` WHERE `tableId` > 40 AND `tableId` < 50 ORDER BY `tableId`
Where this would be more appropriate (because it works when tableId is not a number)

Code: Select all

SELECT `stuff` FROM `table` ORDER BY `tableId` LIMIT 40,10

moving in the right direction

Posted: Sat Sep 23, 2006 2:26 pm
by nates310
you're exactly right on how I was doing the query. And, I think I'm on the right track with "limit" way of doing the query. but I don't think I'm communicating clearly exactly what I want to do. Or maybe, you're telling me the answer and I'm just too dense to get it. Alas, let me give er one more go....

What I have...
A MYSQL db with a table called userinfo. This table contains several fields such as key, name, address, email, etc.


I have two php pages. The first page is is a query that calls every "name" from the above MYSQL table and orders by the name column. An html table on the page displays these names in alphabetical order. Right now, each of those rows in the html table is hyperlinked to "detailpage.php?key=<? echo row_info['key']; ?>

Then, the second php page is the detailpage.php
On this page, i query the mysql table again but only select * from userinfo where key="the key that was just passed to the page"
I then have an html table that lists all the fields for that record (name, address, email, etc.)

What I want to do...
Add a pagination type deal at the bottom of detailpage.php so that I can click "Next" and go to the next name in the database

Why...
with the way it is right now, I can look at a detailpage for "bob smith" but when done and I want to move to the next entry (kiefer sutherland) I have to back out of detailpage.php, go back to the table that lists everyone's name, scroll down to the next one, and click on kiefer sutherland.

The difficulty...
If I were wanting to move through the records on my detailpage.php in order of the key (primary key) that would be very easy. I would query the database based on the key that was passed to the page, but I would just hyperlink "next" to link back to the detailpage.php with a new key indexed up one integer. However, I cannot figure out how to go to the next alphabetical listing.

I understand the idea of LIMIT 40,10 (skip the first 40 records and display the next 10). But I don't understand how to make that work for me in this situation.
If I jump into the detailpage.php at Bob Smith, how do I know how many rows down in the query I am? If I could figure that out, I could just put that value in a variable and add one to it for the next record.

But how do I figure out how many rows down I am? How do I ensure that I actually get Bob Smith when detailpage.php shows up? and not some other record.

Posted: Sat Sep 23, 2006 3:21 pm
by aaronhall
Try something like this:

Code: Select all

<?
$thisRowID = mysql_real_escape_string($_GET['id']);

$nextRow = mysql_result(mysql_query("SELECT id FROM table WHERE id > '$thisRowID' ORDER BY id ASC LIMIT 0,1"),0);

// this is where you display the item
// ...

?>

<a href="asdfasdf.php?id=<?=$nextRow?>">next</a>
EDIT: You can use this same query for the alphabetical listing by replacing $thisRowID in the query with the name of this listing, and comparing it to the name field. Ex: "WHERE name > '$thisRowName'..."

Posted: Sat Sep 23, 2006 3:22 pm
by Mordred
You do know how far in the results you are. You've just clicked on the link. Just pass it on to the other script

Posted: Sat Sep 23, 2006 3:31 pm
by aaronhall
But what if he didn't just click on the next link?... Probably best to calculate the next ID number in the result set based on the current ID number, not on the last page's LIMIT clause.

Re: moving in the right direction

Posted: Sat Sep 23, 2006 3:50 pm
by Christopher
nates310 wrote:Why...
with the way it is right now, I can look at a detailpage for "bob smith" but when done and I want to move to the next entry (kiefer sutherland) I have to back out of detailpage.php, go back to the table that lists everyone's name, scroll down to the next one, and click on kiefer sutherland.
If you want to be able to go to the next record on the detail page, then I think I would add an additional parameter to be passed to the detail page that was something like "page=53&sort=name&next=yes" to tell the detail page to look up the next record. Then you can do something like:

Code: Select all

$sort = preg_replace('/[^a-z]/', '', $_GET['sort']);
$n = intval($_GET['page']) + 1;        // next record after current
$sql = "SELECT `stuff` FROM `table` ORDER BY `$sort` LIMIT $n,1";

Posted: Sat Sep 23, 2006 7:03 pm
by timvw
here's one of my bookmarks:

http://www.xaprb.com/blog/2006/04/28/ho ... ds-in-sql/

(and a search for this topic will probably result in lots more of good links...)

Posted: Mon Sep 25, 2006 11:10 am
by nates310
OK, I've been sifting through all this to try and make something work. On my first page, I can add a variable $row that first =1 and then index it up once every time the loop runs through on the table that displays everyone's name. Thus, if someone clicks on a certain row, I can pass the row number to the detailpage. Now, on the detail page, all I have to do is run the exact same query with the exact same parameters and order and I choose only the row number that was just passed to the page, I should have displayed the correct record I'm looking for. THen, my next and prev buttons below can just index the row number up or down one.

Here's my only hitch. How, in MYSQL, do I select only row number x in a query?

Could I LIMIT $row-1, 1

Posted: Mon Sep 25, 2006 12:31 pm
by nates310
On my first page, where I list all the names, this is what I did....

Code: Select all

<? 
mysql_select_db($database, $DB);
$query= sprintf("SELECT * FROM user_info WHERE username = '%s' ORDER BY lname, name ASC", $username);
$result = mysql_query($query, $DB) or die(mysql_error());
$row_result= mysql_fetch_assoc($result);
$totalRows_result = mysql_num_rows($result); ?>


<HTML>
<Table>

<? $row_count =1; 
do { 
?>
    <tr>
              <td>
				  
                  <a href="detail.php?row=<? echo $row_count; ?>">
                  <?echo $row_result['name']; ?>
                 </a>
              </td>

             
      </tr>
<? $row_count++;} while ($row_result = mysql_fetch_assoc($result)); ?>
</table>
</html>
This gives me a table listing everyone's name in my database and linkink their name to the detail page and passing the variable $_GET['row'] which will tell me what row number I'm on.

On the detail page, I did this

Code: Select all

<?
$skip=$_GET['row']-1;

mysql_select_db($database, $DB);
$query = sprintf("SELECT * FROM user_info WHERE username= '%s' ORDER BY lname, name ASC LIMIT %s, 1", $username, $skip);
$result = mysql_query($query, $DB) or die(mysql_error());
$row_result = mysql_fetch_assoc($result);
$totalRows_result = mysql_num_rows($result);
?>
Now I can display the info from the record that I want to display. I can also create Prev and Next tags by doing this

Code: Select all

<html>
<p> <a href="detail.php?row=<? echo $_GET['row']-1; ?>"><<prev </a> | <a href="detail.php?row=<? echo $_GET['row']+1; ?>"> next>> </a></p>
</html>
I'll just add the code to tell it to stop displaying links when on the first or last record

Now, if you're planning to do something like this and might have many users logged on at once editing the same fields of data, then this method probably doesn't work very well. Because, in the time that you are on the detail page, if someone else edits the records, it's going to upset your row count. However, in my situation, each user has their own set of data, so only one user would ever be editing their set of data at once. So this works great for me. Thanks for all the help!

Posted: Mon Sep 25, 2006 2:52 pm
by Mordred
Yes, this is what I had in mind.
Alternatively, when you get the list of users you can see for each user the database id of his prev and next neighbours and pass that instead, it would be a lot more efficient. If you fetch your user list by pages (which you don't, but you should), remember to ask for one additional user each time, or you'll lose the next link on the last one.

Btw, you have an sql injection with $_GET['row'], and maybe $username (where does it come from?)
Be careful with these things!

Edit: Sorry, no injection, -1 is a valid limit.
Edit 2: Sorry, no no injection (= yes injection ;) ) you can break it if you pass an array, which will give a fatal error. (possible full path disclosure if errors are enabled onthe host)