using limit and where clause to make pagination

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..

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 ;

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 ;

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

---

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

---

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

Ie

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

链接地址: http://www.djcxy.com/p/59694.html

上一篇: MYSQLI INSERT MULTIPLE ROWS

下一篇: 使用限制和where子句做分页