SQL & Prev/Next buttons

[mreyes]

I need to create prev/next button for a page I am working on, but this is a little more complex than the "typical" examples I have come across (e.g http://www.phpbuilder.com/columns/rod20000221.php3)

I have a db table that contains columns called "ID" & "Name". When a page is generated all the data is pulled from the database using ID.

I need to create previous/next buttons that will cycle through the table rows alphabetically (ID's are not sequential, nor is the table ordered alpabetical) - e.g.

ID NAME
2 bob
5 dave
6 smith
9 joe
10 andy
45 paul
48 colin

So if somebody was looking at Dave's page, the previous/next button would goto Colin (prev) & Joe (next). Also if the end of the list was reached it would go back to the first in the list.

The coding to do this isnt a problem, my problem is the SQL query to do this (using postgres, but even a MYSQL example would help)

[Anon]

What part of the SQL are you having problems with? Are you using an ORDER BY clause with a LIMIT statement? If so, what are the difficulties? It's a fairly straightforward approach. Here is a mySQL example...

if (empty($starting_number)) { $starting_number=1; }

$sql = "SELECT table.id, table.name FROM table LIMIT $starting_number, 1 ORDER BY table.name";

On first instance of the page being loaded, you set $starting_number=1. That would select Andy. You would also need to know how many rows are in the table to determine the last page. Try this...

if (empty($total_rows))
{
$sql = "SELECT COUNT(table.id)";
$results = mysql_query($sql);
$total_rows = mysql_result($results, 0);
}

In your case, the total number of rows is the same as the number of pages. You can either requery the count every page (takes longer) or pass it to the next page in the anchor tag. Here's an example of the Next button anchor with the total_rows variable being passed to the next page.

if ($starting_number==$total_rows)
{
$next_row = 1; //Cycles back to page one
}
else
{
$next_row++; //Cycles to next page
}

$next_string = "<A HREF='somepage.php?starting_number=$next_row&numrows=$numrows'>";

A similar method would be applied to the previous button.

if ($starting_number==1)
{
$previous_row = $total_rows; //Cycles to the last page
}
else
{
$previous_row--; //Cycles to previous page
}

$previous_string = "<A HREF='somepage.php?starting_number=$previous_row&total_rows=$total_rows'>";

Keep in mind that if someone adds information to your database, and you don't recalc the $total_rows variable, then you'll be skipping the newly added record. If that is a potential problem, then I recommend requering the COUNT on every page.

I'm still in the midst of developing my own support site at AaronZoller.com, which should be open in the middle of June, but I use a similar method to what I described above. Once the site is open, I'll publish an article on Prev/Next button technologies cause there are a few different approaches one could use. I'll discuss them in detail there. Hope this helps in the meantime.

Aaron Zoller
www.AaronZoller.com

[Anon]

My actual email address is [e-mail] and not the Intel account that showed in my first reply. My bad. Oh well, hope Intel doesn't mind.

😉

Aaron Zoller
www.AaronZoller.com

[Anon]

My God you must be kidding me. I am such a space cadet. Where I wrote...

$next_string = "<A HREF='somepage.php?starting_number=$next_row&numrows=$numrows'>";

I should have wrote...

$next_string = "<A HREF='somepage.php?starting_number=$next_row&total_rows=$total_rows'>";

Let this be a lesson to everyone. Always proofread everything (including your email address when posting something on PHPbuilder).

Aaron Zoller
www.AaronZoller.com

[Anon]

$sql = "SELECT table.id, table.name FROM table LIMIT $starting_number, 1 ORDER BY table.name";

Should be "...FROM table ORDER BY table.id LIMIT 1, $starting_number"

[Anon]

Like I said, I'm a space cadet and an idiot. But you are mistaken on the order in which LIMIT variables go. LIMIT works like this...

This example will limit the result to 10 rows, starting with row 5.
SELECT name FROM customers LIMIT 5 , 10

It was taken straight from the mySQL site. http://www.mysql.com/doc/L/I/LIMIT_optimisation.html

AaronZoller
www.AaronZoller.com

[Anon]

We are both right, actually - I use postgresql as much I use mysql and the difference in limit between the two has annoyed me more than a few times.

So.. mysql LIMIT 1,2 = pgsql LIMIT 2,1

[Anon]

Indeed, I can see how that could get annoying. It kinda makes you wonder why database developers do such things. Probably some sick twisted sadist was responsible for that one.

AZ

[mreyes]

Thanks for the replies, I will be giving those ideas an test run tonight

Also inbetween the first post and now I came up with the following queries to soleve the problem, the only thing it cant do is cycle round to the first entry if current is the last one :

next id :
SELECT id, name FROM table
WHERE name=(SELECT MIN(name) FROM table
WHERE name>(SELECT name FROM table WHERE ID=$current_id));

prev id:
SELECT id, name FROM table
WHERE name=(SELECT MAX(name) FROM table
WHERE name<(SELECT name FROM table WHERE ID=$current_id));