在查询(行数)中放置位置的问题 - problem with put where in query(rownum)?

problem with put where in query(rownum)?

我使用"codeigniter"和rownum查询，我想把WHERE在内部查询，但有以下错误。怎么样?

A Database Error Occurred错误编号:1064

你的SQL语法有错误;查看手册对应于MySQL服务器版本，以便使用正确的语法WHERE '*' = '0') t， (SELECT @rownum:=0) r' at line 7

SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT * FROMhotel_submit ORDER BY id desc LIMIT 0,6 WHERE * = 1@rownum: = 0) r

文件名:D: ' xampp '根' hdr数据库' system ' ' DB_driver.php

行号: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总是排在LIMIT和ORDER之前:

按讨论编辑

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

还有其他问题，我看到这个查询(似乎过于复杂，可能不需要子查询)，但没有你的数据库结构，我不能假定纠正它。然而，关键字的顺序是最重要的。

查看这些关于SQL语法和用法各个方面的教程文章:http://www.tizag.com/sqlTutorial/sqlwhere.php

尝试:

$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);

编辑:

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

很难知道你想把过滤器放在哪里…也可以是:

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