problem with put where in query(rownum)?
i use of "codeigniter" and rownum
query, i want put WHERE
in inside query but have following error. how is it?
A Database Error Occurred Error Number: 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 '*' = '0' ) t, (SELECT @rownum:=0) r' at line 7
SELECT @rownum:=@rownum+1 rownum, t.* FROM ( SELECT * FROM hotel_submits ORDER BY id desc LIMIT 0, 6 WHERE * = 1 ) t, (SELECT @rownum:=0) r
Filename: D:\xampp\htdocs\hdr\system\database\DB_driver.php
Line Number: 330
$this->db->query("SELECT @rownum:=@rownum+1 rownum, t.*
FROM (
SELECT *
FROM hasana_you
ORDER BY id desc
LIMIT $offset, $coun_page
WHERE * = 1 //or $id instead 1
) t,
(SELECT @rownum:=0) r");
WHERE
always comes before LIMIT
and ORDER
:
EDITED PER DISCUSSION
SELECT
@rownum:=@rownum+1 rownum,
t.*
FROM (
SELECT
*
FROM
hasana_you
WHERE
column_a = 1 OR
column_b = 1 OR
column_c = 1 OR
column_d = 1
ORDER BY
id desc
LIMIT
$offset, $count_page
) AS t
There are other issues that I see with this query (seems overly complex, may not need the subquery), but without your db structure I could not presume to correct it. However, the stated order of keywords stands as the primary concern.
Check out these tutorial articles on the various aspects of SQL syntax and usage: http://www.tizag.com/sqlTutorial/sqlwhere.php
Try:
$id= 1;
$f= $this->db->query("SELECT GROUP_CONCAT(column_name,
\" like '%$id%' OR \" SEPARATOR '') AS str
FROM information_schema.columns
WHERE table_name='hasana_you'");
$f1= $f->row();
$filter= substr($f1->str,0,-4);
Edited:
$x= $this->db->query("SELECT * FROM (SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r,
(SELECT *
FROM hasana_you
WHERE $filter
ORDER BY id desc
) t) x
ORDER BY id desc
LIMIT $offset, $count_page");
It's hard to know where you want the filter... can also be:
$x= $this->db->query("SELECT * FROM (SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r,
(SELECT *
FROM hasana_you
ORDER BY id desc
) t) x
WHERE $filter
ORDER BY id desc
LIMIT $offset, $count_page");
精彩评论