Monday, December 19, 2011

PHP: Simple Pagination in MySQL I

File: booklist.php
<?php
// booklist.php?page=10
$start_page = (empty($_GET['page'])) ? '0' : $_GET['page'];

function pagination(){
 $sql = mysql_query("SELECT * FROM books ORDER BY id"); 
 $result = mysql_num_rows($sql);
 $counter = $result / 10;
 $paginate = substr($counter, 0, strpos($counter, '.'));

 for($i = 0; $i <= $paginate; $i++){
  $pages = $i * 10;
  $pg_num = $i + 1;
  echo '<a href="booklist.php?page='.$pages.'">'.$pg_num.'</a> ';
 }

 return '<br />Total: '.$result;
}

$sql = mysql_query("SELECT * FROM books ORDER BY id LIMIT $start_page, 10");

// database fields
$fields = "id,title,description,created";
$fields_array = explode(',',$fields);
 
// display
echo '<table border="1">';

echo '<tr>';
foreach($fields_array as $key=>$field){   
 echo '<th>'.$field.'</th>';  
}
echo '</tr>';
 
while($row = mysql_fetch_assoc($sql)){ 
 foreach($fields_array as $key=>$field){
  $field_name = (empty($row[$field])) ? '0' : $row[$field];
  echo '<td align="center">'.$field_name.'</td>'; 
 }
}

// pagination 
echo '<tr><td colspan="4">'.pagination().'</td></tr>';
echo '</table>';
?>

1 comment: