php - database post: pagination -
so trying page paginate after 10 posts on forum. pagination buttons appear , function , variable url working correctly, page 1 displays posts in database instead of 10 , page 2 displays same thing (all posts).
i'm sure simple not seeing...
the code main forum page this:
$conn = mysql_connect("$host", "$username", "$password"); if(!$conn) die("failed connect database!"); $status = mysql_select_db("$db_name", $conn); if(!$status) die("failed select database!"); $sql="select * $tbl_name order id desc"; // oreder id desc order result descending $result=mysql_query($sql); include ('pagination.php'); ?> <?php $pager = new ps_pagination($conn, $sql, 10, 5, "param1=valu1¶m2=value2"); /* * enable debugging if want o view query errors */ $pager->setdebug(true); /* * paginate() function returns mysql result set * or false if no rows returned query */ $rs = $pager->paginate(); if(!$rs) die(mysql_error()); while($rows=mysql_fetch_array($result)){ // start looping table row ?> <tr> <td bgcolor="#ffffff"><? echo $rows['id']; ?></td> <td bgcolor="#ffffff"><font size="3"><a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo $rows['topic']; ?></a></font><br></td> <td align="center" bgcolor="#ffffff"><? echo $rows['view']; ?></td> <td align="center" bgcolor="#ffffff"><? echo $rows['reply']; ?></td> <td align="center" bgcolor="#ffffff"><? echo $rows['datetime']; ?></td> </tr> <?php // exit looping , close connection } mysql_close(); ?> <tr> <td colspan="5" align="right" bgcolor="#e6e6e6"><a href="create_topic.php"><strong>create new topic</strong> </a></td> </tr> </table> <br /> <center><?php echo $pager->renderfullnav(); ?></center> then code pagination file this:
<?php class ps_pagination { var $php_self; var $rows_per_page = 10; //number of records display per page var $total_rows = 0; //total number of rows returned query var $links_per_page = 5; //number of links display per page var $append = ""; //paremeters append pagination links var $sql = ""; var $debug = false; var $conn = false; var $page = 1; var $max_pages = 0; var $offset = 0; /** * constructor * * @param resource $connection mysql connection link * @param string $sql sql query paginate. example : select * users * @param integer $rows_per_page number of records display per page. defaults 10 * @param integer $links_per_page number of links display per page. defaults 5 * @param string $append parameters appended pagination links */ function ps_pagination($connection, $sql, $rows_per_page = 10, $links_per_page = 5, $append = "") { $this->conn = $connection; $this->sql = $sql; $this->rows_per_page = (int)$rows_per_page; if (intval($links_per_page ) > 0) { $this->links_per_page = (int)$links_per_page; } else { $this->links_per_page = 5; } $this->append = $append; $this->php_self = htmlspecialchars($_server['php_self'] ); if (isset($_get['page'] )) { $this->page = intval($_get['page'] ); } } /** * executes sql query , initializes internal variables * * @access public * @return resource */ function paginate() { //check valid mysql connection if (! $this->conn || ! is_resource($this->conn )) { if ($this->debug) echo "mysql connection missing<br />"; return false; } //find total number of rows $all_rs = @mysql_query($this->sql ); if (! $all_rs) { if ($this->debug) echo "sql query failed. check query.<br /><br />error returned: " . mysql_error(); return false; } $this->total_rows = mysql_num_rows($all_rs ); @mysql_close($all_rs ); //return false if no rows found if ($this->total_rows == 0) { if ($this->debug) echo "query returned 0 rows."; return false; } //max number of pages $this->max_pages = ceil($this->total_rows / $this->rows_per_page ); if ($this->links_per_page > $this->max_pages) { $this->links_per_page = $this->max_pages; } //check page value in case trying input aribitrary value if ($this->page > $this->max_pages || $this->page <= 0) { $this->page = 1; } //calculate offset $this->offset = $this->rows_per_page * ($this->page - 1); //fetch required result set $rs = @mysql_query($this->sql . " limit {$this->offset}, {$this->rows_per_page}" ); if (! $rs) { if ($this->debug) echo "pagination query failed. check query.<br /><br />error returned: " . mysql_error(); return false; } return $rs; } /** * display link first page * * @access public * @param string $tag text string displayed link. defaults 'first' * @return string */ function renderfirst($tag = 'first') { if ($this->total_rows == 0) return false; if ($this->page == 1) { return "$tag "; } else { return '<a href="' . $this->php_self . '?page=1&' . $this->append . '">' . $tag . '</a> '; } } /** * display link last page * * @access public * @param string $tag text string displayed link. defaults 'last' * @return string */ function renderlast($tag = 'last') { if ($this->total_rows == 0) return false; if ($this->page == $this->max_pages) { return $tag; } else { return ' <a href="' . $this->php_self . '?page=' . $this->max_pages . '&' . $this->append . '">' . $tag . '</a>'; } } /** * display next link * * @access public * @param string $tag text string displayed link. defaults '>>' * @return string */ function rendernext($tag = '>>') { if ($this->total_rows == 0) return false; if ($this->page < $this->max_pages) { return '<a href="' . $this->php_self . '?page=' . ($this->page + 1) . '&' . $this->append . '">' . $tag . '</a>'; } else { return $tag; } } /** * display previous link * * @access public * @param string $tag text string displayed link. defaults '<<' * @return string */ function renderprev($tag = '<<') { if ($this->total_rows == 0) return false; if ($this->page > 1) { return ' <a href="' . $this->php_self . '?page=' . ($this->page - 1) . '&' . $this->append . '">' . $tag . '</a>'; } else { return " $tag"; } } /** * display page links * * @access public * @return string */ function rendernav($prefix = '<span class="page_link">', $suffix = '</span>') { if ($this->total_rows == 0) return false; $batch = ceil($this->page / $this->links_per_page ); $end = $batch * $this->links_per_page; if ($end == $this->page) { //$end = $end + $this->links_per_page - 1; //$end = $end + ceil($this->links_per_page/2); } if ($end > $this->max_pages) { $end = $this->max_pages; } $start = $end - $this->links_per_page + 1; $links = ''; for($i = $start; $i <= $end; $i ++) { if ($i == $this->page) { $links .= $prefix . " $i " . $suffix; } else { $links .= ' ' . $prefix . '<a href="' . $this->php_self . '?page=' . $i . '&' . $this->append . '">' . $i . '</a>' . $suffix . ' '; } } return $links; } /** * display full pagination navigation * * @access public * @return string */ function renderfullnav() { return $this->renderfirst() . ' ' . $this->renderprev() . ' ' . $this->rendernav() . ' ' . $this->rendernext() . ' ' . $this->renderlast(); } /** * set debug mode * * @access public * @param bool $debug set true enable debug messages * @return void */ function setdebug($debug) { $this->debug = $debug; } } ?>
you're running 2 queries. 1 that's used display everything, , 1 in pager you're ignoring:
$result=mysql_query($sql); <-- query you're using in output portion $rs = $pager->paginate(); <-- second query you're utterly ignoring. if(!$rs) die(mysql_error()); while($rows=mysql_fetch_array($result)) <--fetching/displaying first query's results you should doing fetches on $rs, not $result.
Comments
Post a Comment