Entry

How do I add hypertext links (such as 1-10,11-20,next,back etc) using PHP/MySQL to serve portions of query results ?

May 15th, 2000 08:31
Sam Leibowitz, Niketan Pandit, Onno Benschop,

First, you need to get a count of all the records that meet your 
requirements. Let's say you have a really simple query: "SELECT id, 
name FROM mytable".   You could pass that to mysql_query(), then use 
mysql_num_rows() to get a count of all the rows.  Or, you could put the 
counting function right into the SQL query ("SELECT count(id) FROM 
mytable").
After that, you can loop through the results using the LIMIT specifier 
in your SQL query. When use in SELECTs, LIMIT takes two arguments: the 
first (optional) specifies the offset of the first row to return, while 
the second (mandatory) specifies the number of rows to return. So, to 
get rows 21 through 30, you'd use "SELECT id, name FROM mytable LIMIT 
20,10".  
Finally, you can use GET arguments (or whatever) to control which set 
of results the viewer gets. For example, if you're viewing results 11-
20, you could use the following two links:
<a href="thispage.php?limit=0,10">last 10 results</a>
<a href="thispage.php?limit=20,10">next 10 results</a>
To make this work, the LIMIT in the SQL query would have to be set 
dynamically: 
$query = "SELECT id, name FROM mytable LIMIT ".$limit.",10";
In all likelihood, you'll want to check to make sure that you don't 
offer a "next 10 results" option whne you've reached the end - 
actually, that's the only reason you need to count the results as 
described at the beginning of this post.  But that's a pretty simple 
operation, and left as an exercise for the reader. ;)