using limit and where clause to make pagination

Question

Everybody . please help me. Now I'm working with pagination.. I want to display 5 records per page depending on the tblcategory.cat_id . but my query string has problem..

1. table tblcategory

   CREATE TABLE IF NOT EXISTS tblcategory ( cat_id int(11) NOT NULL AUTO_INCREMENT, cat_name char(50) NOT NULL, PRIMARY KEY (cat_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

2. table tblcontent

   CREATE TABLE IF NOT EXISTS tblcontent ( con_id int(11) NOT NULL AUTO_INCREMENT, title char(100) NOT NULL, description text NOT NULL, date_posted date NOT NULL, author char(50) NOT NULL, cat_id int(11) NOT NULL, PRIMARY KEY (con_id), KEY cat_id (cat_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=78 ;

3. table tblimage

   CREATE TABLE IF NOT EXISTS tblimage ( img_id int(11) NOT NULL AUTO_INCREMENT, con_id int(11) NOT NULL, img_name varchar(250) NOT NULL, img_thum varchar(250) NOT NULL, PRIMARY KEY (img_id), KEY con_id (con_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

this is my query string , so what's wrong with my sql statement.

SELECT * FROM tblcontent LEFT JOIN tblcategory
    ON (tblcontent.cat_id = tblcategory.cat_id)
    LEFT JOIN tblimage ON (tblimage.con_id = tblcontent.con_id)
    ORDER BY tblcontent.date_posted DESC
    LIMIT 0,5 WHERE tblcategory.cat_id=1

The Result is : Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE tblcategory.cat_id=1' at line 5

Answer 1

try this

SELECT * FROM tblcontent LEFT JOIN tblcategory
    ON (tblcontent.cat_id = tblcategory.cat_id)
    LEFT JOIN tblimage ON (tblimage.con_id = tblcontent.con_id)
  WHERE tblcategory.cat_id=1
    ORDER BY tblcontent.date_posted DESC
    LIMIT 0,5

First you need to specify the where condition and then specify the order

see here for Syntax

Answer 2

The WHERE is misplaced - it needs to be before the ORDER BY.

I.e.

SELECT * FROM tblcontent
LEFT JOIN tblcategory ON (tblcontent.cat_id = tblcategory.cat_id)
LEFT JOIN tblimage ON (tblimage.con_id = tblcontent.con_id)
WHERE tblcategory.cat_id=1
ORDER BY tblcontent.date_posted DESC
LIMIT 0,5