phpmysqlcodeigniteractiverecordquery-builder

Calling CodeIgniter's active record get() method after calling query() gives error "No tables used"


I use MySQL and try to write this query into a model of CodeIgniter. This query run perfectly in phpmyAdmin.

SELECT 
    a.ar, 
    a.jabatan, 
    COUNT(*) AS JumlahWPBaru, 
    SUM( 
        CASE WHEN a.no_shim IS NOT NULL 
            THEN 1 
        WHEN a.no_shim IS NULL 
            THEN 0 
        END
    ) AS JumlahHimbauan
FROM (
    SELECT 
        a.ar, 
        c.jabatan, 
        a.npwp, 
        a.kpp, 
        a.cab, 
        a.namawp, 
        b.kd_shim, 
        b.no_shim, 
        b.tgl_shim
    FROM mfwpbudi a
    LEFT JOIN tb_shimbauan b ON ( a.npwp = b.npwp ) 
    LEFT JOIN easystp_user c ON ( SUBSTR( a.ar, 1, 9 ) = c.id_user     ) 
    WHERE a.tanggal_daftar >  '2013-12-31'
        AND c.jabatan =  '05E'
) AS a
GROUP BY a.ar

Here the model that I write.

function m_data_penyampaian_shim()
{
    $this->db->query("SELECT 
        a.ar, 
        a.jabatan, 
        COUNT(*) AS JumlahWPBaru, 
        SUM( 
            CASE WHEN a.no_shim IS NOT NULL 
            THEN 1 
            WHEN a.no_shim IS NULL 
            THEN 0 
            END ) AS JumlahHimbauan
        FROM (

        SELECT 
            a.ar, 
            c.jabatan, 
            a.npwp, 
            a.kpp, 
            a.cab, 
            a.namawp, 
            b.kd_shim, 
            b.no_shim, 
            b.tgl_shim
        FROM mfwpbudi a
        LEFT JOIN tb_shimbauan b ON ( a.npwp = b.npwp ) 
        LEFT JOIN easystp_user c ON ( SUBSTR( a.ar, 1, 9 ) = c.id_user ) 
        WHERE a.tanggal_daftar >  '2013-12-31'
        AND c.jabatan =  '05E'
        ) AS a
        GROUP BY a.ar");
    $qry=$this->db->get();
    return $qry->result();
}

but I get the error :

Error Number: 1096

No tables used

SELECT *

Filename: C:\xampp\htdocs\himbauanwpbaru\system\database\DB_driver.php

Line Number: 330

How I solve this problem? Are there something I left from the code above?


Solution

  • $this->db->query() function ONLY returns TRUE/FALSE on success or failure. It DOES NOT return a database result set, nor does it set the query timer, or compile bind data, or store your query for debugging. It simply lets you submit a query. Most users will rarely use this function.

    Try this way:-

    $sql  = "SELECT 
            a.ar, 
            a.jabatan, 
            COUNT(*) AS JumlahWPBaru, 
            SUM( 
                CASE WHEN a.no_shim IS NOT NULL 
                THEN 1 
                WHEN a.no_shim IS NULL 
                THEN 0 
                END ) AS JumlahHimbauan
            FROM (
    
            SELECT 
                a.ar, 
                c.jabatan, 
                a.npwp, 
                a.kpp, 
                a.cab, 
                a.namawp, 
                b.kd_shim, 
                b.no_shim, 
                b.tgl_shim
            FROM mfwpbudi a
            LEFT JOIN tb_shimbauan b ON ( a.npwp = b.npwp ) 
            LEFT JOIN easystp_user c ON ( SUBSTR( a.ar, 1, 9 ) = c.id_user ) 
            WHERE a.tanggal_daftar >  '2013-12-31'
            AND c.jabatan =  '05E'
            ) AS a
            GROUP BY a.ar";
    
    $query = $this->db->query($sql);
    $result = $query->result_array();
    return $result;