开发者

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");
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜