php - Creating multiple pages from sql query

Question

I am trying to create multiple pages from my query. If I have 100 results, and I want 10 results per page, I would like there to be ten pages created, with each page showing a different query. This is how my query is set up:

$sql ="SELECT * FROM Post WHERE (active ='1') ORDER BY PostID DESC ";
$result = mysql_query($sql,$db);
$numrows = mysql_num_rows($result);

while(($post = mysql_fetch_assoc($result))) {
$posts[] = $post;
}


 <?php
if (!$numrows){ echo $errormessage;}
else{


 foreach($posts as $post): ?>

 echo stripslashes($post['text']);  

 <?php endforeach; }?>

This pulls each "post" from the database and puts displays them all out.

I would like to do something like this:

$results = mysql_num_rows($result);


$numpages = 10/$results; //gives the number of pages

while($numpages<$results)
{

//run code from above\
}

What is the best way to do this with the method that I use? I appreciate your opinions because I'm at one of those stages where I'm lost in my own logic. Thank You!

Answer 1

Most pagination examples fail to meet real life requirements. A custom query string, for example.
So, here is a complete yet concise example:

<?
per_page=10;
// Let's put FROM and WHERE parts of the query into variable
$from_where="FROM Post WHERE active ='1'";
// and get total number of records
$sql = "SELECT count(*) ".$from_where;
$res = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);
$row = mysql_fetch_row($res);
$total_rows = $row[0];

//let's get page number from the query string 
if (isset($_GET['page'])) $CUR_PAGE = intval($_GET['page']); else $CUR_PAGE=1;
//and calculate $start variable for the LIMIT clause
$start = abs(($CUR_PAGE-1)*$per_page);

//Let's query database for the actual data
$sql = "SELECT * $from_where ORDER BY PostID DESC LIMIT $start,$per_page";
$res = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);
// and fill an array
while ($row=mysql_fetch_array($res)) $DATA[++$start]=$row;

//now let's form new query string without page variable
$uri = strtok($_SERVER['REQUEST_URI'],"?")."?";    
$tmpget = $_GET;
unset($tmpget['page']);
if ($tmpget) {
  $uri .= http_build_query($tmpget)."&";
}    
//now we're getting total pages number and fill an array of links
$num_pages=ceil($total_rows/$per_page);
for($i=1;$i<=$num_pages;$i++) $PAGES[$i]=$uri.'page='.$i;

//and, finally, starting output in the template.
?>
Found rows: <b><?=$total_rows?></b><br><br>
<? foreach ($DATA as $i => $row): ?>
<?=$i?>. <a href="?id=<?=$row['id']?>"><?=$row['title']?></a><br>
<? endforeach ?> 

<br>
Pages: 
<? foreach ($PAGES as $i => $link): ?>
<? if ($i == $CUR_PAGE): ?>
<b><?=$i?></b>
<? else: ?> 
<a href="<?=$link?>"><?=$i?></a>
<? endif ?> 
<? endforeach ?>