CodeIgniter中query()后调用get()触发1096无表使用错误

错误信息 #

1096 No tables used SELECT *

出错代码 #

public function getH20StockDevelopmentGraph($recStart, $recEnd)
{
    $sql = "SELECT v.DATE,
                    (SELECT SUM(b.QTY)
                    FROM tbl_ax_transaction b
                    WHERE DATE(b.DATEPHYSICAL) <= v.DATE
                        AND b.ITEMID = '166647'
                        AND ((b.RECEIPT <> 'Ordered') OR (b.RECEIPT IS NULL))) STOCK_ON_HAND
            FROM (SELECT ADDDATE('1970-01-01',
                                    T4.I * 10000 + T3.I * 1000 + T2.I * 100 + T1.I * 10 + T0.I) DATE
                    FROM (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T0,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T1,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T2,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T3,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T4) V
            WHERE DATE BETWEEN '$recStart' AND '$recEnd'";
    $this->db->query($sql);
    return $this->db->get()->result();
}

问题原因 #

你执行自定义SQL用了$this->db->query($sql)，之后又调用$this->db->get()——这个方法默认会生成SELECT * FROM的查询语句，但你没指定要查询的表，所以MySQL抛出1096错误。

修复方案 #

直接通过$this->db->query($sql)获取结果，不需要额外调用get()。修改后的代码：

public function getH20StockDevelopmentGraph($recStart, $recEnd)
{
    $sql = "SELECT v.DATE,
                    (SELECT SUM(b.QTY)
                    FROM tbl_ax_transaction b
                    WHERE DATE(b.DATEPHYSICAL) <= v.DATE
                        AND b.ITEMID = '166647'
                        AND ((b.RECEIPT <> 'Ordered') OR (b.RECEIPT IS NULL))) STOCK_ON_HAND
            FROM (SELECT ADDDATE('1970-01-01',
                                    T4.I * 10000 + T3.I * 1000 + T2.I * 100 + T1.I * 10 + T0.I) DATE
                    FROM (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T0,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T1,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T2,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T3,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T4) V
            WHERE DATE BETWEEN '$recStart' AND '$recEnd'";
    // 直接获取query执行后的结果
    return $this->db->query($sql)->result();
}

另外提醒：直接把$recStart和$recEnd拼进SQL存在SQL注入风险，建议改用参数绑定优化：

public function getH20StockDevelopmentGraph($recStart, $recEnd)
{
    $sql = "SELECT v.DATE,
                    (SELECT SUM(b.QTY)
                    FROM tbl_ax_transaction b
                    WHERE DATE(b.DATEPHYSICAL) <= v.DATE
                        AND b.ITEMID = '166647'
                        AND ((b.RECEIPT <> 'Ordered') OR (b.RECEIPT IS NULL))) STOCK_ON_HAND
            FROM (SELECT ADDDATE('1970-01-01',
                                    T4.I * 10000 + T3.I * 1000 + T2.I * 100 + T1.I * 10 + T0.I) DATE
                    FROM (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T0,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T1,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T2,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T3,
                            (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T4) V
            WHERE DATE BETWEEN ? AND ?";
    // 参数绑定避免SQL注入
    return $this->db->query($sql, [$recStart, $recEnd])->result();
}

内容的提问来源于stack exchange，提问作者Muhammad Angga Oktaharisetia