如何在codeigniter中编写此复杂查询? [英] How write this complex query in codeigniter?

问题描述

我使用mysql并尝试将此查询写入Codeigniter模型.该查询可以在phpmyAdmin中完美运行.

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

这是我编写的模型.

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();
}

但是我得到了错误:

错误号:1096

Error Number: 1096

未使用表

选择*

文件名:C:\ xampp \ htdocs \ himbauanwpbaru \ system \ database \ DB_driver.php

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

行号:330

我该如何解决这个问题?我在上面的代码中还剩下什么吗?我感谢任何建议和解决方案.谢谢

How I solve this problem ? Are there something I left from the code above? I appreciate any suggestion and solutions. Thank you

推荐答案

$ this-> db-> query()函数仅在成功或失败时返回TRUE/FALSE.它不返回数据库结果集，也不设置查询计时器，编译绑定数据或存储查询以进行调试.它只是让您提交查询.大多数用户很少使用此功能.

$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.

尝试这种方式:-

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

这篇关于如何在codeigniter中编写此复杂查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！