creating prev and next links

ali_p

Hi i am looking for the best way in which to limit the number of records returned from a query on my database. say my query returns 50 records when submitted, i would like to show 10 on the first page use a next button to show the next 10 and so on. A previous page button is also required to view the last 10 etc.
Can anyone shed some light on this ??

matto

assuming you're using mysql ...

first you have to know how many records there are.
select count(*) from table

then, select only those you need
select * from table ... limit START#,OFFSET

how to know where you are navigating?
say, you are looking at the third page where each page holds 10 records.

so your "next" link has to include the position ("show the Xth record of the query as the forst one of 10"):

<a href="<?=$PHP_SELF ?>?position=X">next 10</a>

this X then goes into the sql select as the START#. (OFFSET is 10.)

how to determine X for creating the "next" link?
newstartpos = oldstartpos + 10, of course. analogous for the "previous" link.

hope this helps!

I've just discovered there is a complete Pagination function in the Code Critique forum; this is just what you're looking for:

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10257633

ali_p

thank you very much.
you know i just found a really good article that deals with precisely this code at http://www.phpbuilder.com/columns/rod20000221.php3.

i'm very excited

ali_p

Ok i'm in a pickle now.
i have the results from my query returning a limited number of rows and the prev and next links in place , now the problem i am now faced with is that my next link will not show the next set of 3 results , it tells me that there are no results . I can't get it to see beyond the first 3 results.

below i have pasted my code,

//connect to server and select database
$conn = mysql_connect("hostname", "user_name", "password")
or die(mysql_error());
mysql_select_db("database_name",$conn) or die(mysql_error());

//choose the amount of stats you want to display in one page
$per_page = 3;

/original code // retrieve data from database
$sql_text = "SELECT FROM jobs";*/

//create and issue the query
$sql_text = "SELECT *
FROM jobs
WHERE division = '".$_GET['division']."'
ORDER BY job_type";

if(!isset($GET['page'])){
$page = 1;
}else{
$page = $GET['page'];
}

//set up the variables for the prev and next links
$prev_page = $page - 1;
$next_page = $page + 1;

//get the data from the database
$query = mysql_query($sql_text);

/set up specified page, so we have set $per_page to 3 we need to figure out the starting page based on the amount of data recieved from the database. mysql_num_rows() will get the no. of rows in the result from our mysql_query/
$page_start = ($per_page * $page) - $per_page;
$num_rows = mysql_num_rows($query);

if ($num_rows <= $per_page){
$num_pages = 1;
}elseif (($num_rows % $per_page) == 0) {
$num_pages = ($num_rows / $per_page);
}else{
$num_pages = ($num_rows / $per_page) + 1;
}
$num_pages = (int) $num_pages;

if ($page > $num_pages || $page < 0) {

echo 'You have specified an invalid page number'

;}

$sql_text = $sql_text." LIMIT $page_start, $per_page";
$query = mysql_query($sql_text);

<html>
<head>
<title>Top Secretaries</title>
</head>

<body>

There are currently <? echo "$num_rows"; ?> files in this section

<?
// previous link. we need a if statement to determine if prev page is not equal to 0, then echo the url for the link
if ($prev_page != 0){
echo '<a href="job_listing2.php?page='.$prev_page.'">prev</a> |';
}

//displays the next link
if ($page != $num_pages) {
echo '|<a href="job_listing2.php?page='.$next_page.'">Next</a>';
}
?>

<?php // This calls the data to be displayed.
while ($info = mysql_fetch_assoc($query)) {
echo("\n"
. "job ref: " . $info["job_ref"] . " \n"
. "job title: " . $info["job_title"] . " \n"
. "job type: " . $info["job_type"] . " \n"
. "location: " . $info["location"] . " \n"
. "contact:\n"
."<a href=\"".$info["contact"] . "\">".$info["contact"] ."</a> \n");
echo("\n");
echo("<hr style=\"color:black;width:500px;text-align:left;\">\n\n\n\n");
}
?>

</body>
</html>