PHP MySQLi Pagination

Question

I'm trying to build a pagination app however my code keeps failing and I don't know why. It works when I replace the LIMIT variables with numbers but not with variables in the query.

$pageNum = $_GET['page'];
    $id = $_GET['id'];
if ($pageNum == NULL) {
$pageNum = 1;   
}
include("config.php");
include("header.php");

$numPosts = $connect->query("SELECT * FROM forum_posts WHERE category='" . $id . "'         ORDER BY latestReply ASC");
$numPosts = $numPost->num_rows;
$resultsPerPage = 10;
$lastPage = ceil($numPosts/$resultsPerPage);

if (!(isset($pagenum))){ 
 $pageNum = 1; 
 }

 if ($pagenum < 1) { 
 $pageNum = 1; 
 } elseif ($pageNum > $lastPage) { 
 $pageNum = $lastPage; 
 } 

$limit1 = $pageNum * $resultsPerPage - $resultsPerPage;
$limit2 = $limit1 + $resultsPerPage;
$post = $connect->query("SELECT * FROM forum_posts LIMIT $limt1, $limit2 WHERE category='" . $id . "' ORDER BY latestReply ASC");

I keep getting this error however:

[17-Nov-2013 17:12:22 Europe/London] PHP Warning:  mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/robbiewi/public_html/forum/category.php on line 59

On this line:

while ($posts = mysqli_fetch_array($post)) {

All help is much appreciated!

Your script is vulnerable to SQL injection. Why don’t you [use prepared statements](http://stackoverflow.com/q/60174/53114)? — Gumbo, Nov 17 '13 at 17:48

Krish R · Accepted Answer · 2013-11-17T17:55:42.507

WHERE condition should be placed before the LIMIT

should be

SELECT * FROM forum_posts WHERE category='" . $id . "' ORDER BY latestReply ASC LIMIT $limt1, $limit2

instead of,

SELECT * FROM forum_posts LIMIT $limt1, $limit2 WHERE category='" . $id . "' ORDER BY latestReply ASC

Select syntax: http://dev.mysql.com/doc/refman/5.0/en/select.html

SELECT
[ALL | DISTINCT | DISTINCTROW ]
  [HIGH_PRIORITY]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
  [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

Thanks I had never seen that Select Syntax before! – Robbo5899 Nov 17 '13 at 17:59 — Robbo5899, Nov 17 '13 at 17:59

score 0 · Answer 2 · answered Nov 17 '13 at 17:46

0

You wrote $limt1 in your SQL statement, but I guess you just misstyped it right now.
Are you sure that $limit1 and $limit2 are numbers? Maybe you could fix it when you use $limit1 = $limit1 * 1 to be sure it's number, worked for me sometimes.

answered Nov 17 '13 at 17:46

TheFrozenOne

1,695
9
19

Yeah I fixed the typo just after i posted here. Thanks – Robbo5899 Nov 17 '13 at 18:00

PHP MySQLi Pagination

2 Answers2