php - 如何根据数据库中包含的信息量显示日期数据？

Question

当我按日期显示描述的数量时，我遇到了一个问题。也就是说，描述的数量与我的数据库中的内容不匹配。

这是我的数据库中的表

这是结果：

----------------------------------------------------
|      Tanggal      |   A  |   I   |   S   |   H   |
----------------------------------------------------
|     2018-01-01    |   1  |   1   |   3   |   1   |  
|     2018-01-02    |   1  |   1   |   3   |   1   |
----------------------------------------------------

它应该是这样的：

----------------------------------------------------
|      Tanggal      |   A  |   I   |   S   |   H   |
----------------------------------------------------
|     2018-01-01    |   1  |   1   |   0   |   1   |  
|     2018-01-02    |   0  |   0   |   3   |   0   |
----------------------------------------------------

这是我的控制器：

public function absen()
{
    $data['getall'] = $this->m_absen->getall();

    $data['alpa']   = $this->m_absen->alpa();

    $data['izin']   = $this->m_absen->izin();

    $data['sakit']  = $this->m_absen->sakit();

    $data['hadir']  = $this->m_absen->hadir();

    $this->load->view('admin/absen/v_absen', $data);
}

这是我的模特：

public function getall()
{
    $query = $this->db->query("SELECT * FROM tbl_absen_siswa group by tanggal");
    return $query->result();
}

public function alpa()
{
    $query = $this->db->query("select count(*) as total_alpa from tbl_absen_siswa where keterangan='A'");
    return $query->row();
}

public function izin()
{
    $query = $this->db->query("SELECT COUNT(*) AS total_izin FROM tbl_absen_siswa WHERE keterangan='I'");
    return $query->row();
}

public function sakit()
{
    $query = $this->db->query("select count(*) as total_sakit, nama_siswa from tbl_absen_siswa where keterangan='S'");
    return $query->row();       
}

public function hadir()
{
    $query = $this->db->query("SELECT COUNT(*) AS total_hadir FROM tbl_absen_siswa  WHERE keterangan='H'");
    return $query->row();
}

这是我的观点：

<table class="table table-striped table-bordered table-hover dt-responsive" width="100%" id="sample_1">
    <thead>
        <tr>
            <th width="5%">Tanggal</th>
            <th width="3%">A</th>
            <th width="3%">I</th>
            <th width="3%">S</th>
            <th width="3%">H</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach($getall as $g){ ?>
        <tr>
            <td><?php echo $g->tanggal; ?></td>
            <td><?php echo $alpa->total_alpa; ?></td>
            <td><?php echo $izin->total_izin; ?></td>
            <td><?php echo $sakit->total_sakit; ?>
            <td><?php echo $hadir->total_hadir; ?></td>
        </tr>
        <?php } ?>
    </tbody>
</table>

标签： phpcodeigniter

Answer 1

您只需要一个查询即可完成此类任务

你的模型

public function getall()
{
    $query = $this->db
        ->select('tanggal, count(keterangan) AS count, keterangan')
        ->from('tbl_absen_siswa')
        ->group_by('tanggal, keterangan')
        ->get();
    return $query->result();
}

你的控制器

public function absen()
{
    $arrResult = $this->m_absen->getall();
    $arrData = [];

    foreach($arrResult AS $objItem)
    {
        $arrData[$objItem->tanggal][$objItem->keterangan] = $objItem->count;
    }
    $this->load->view('so/so50683515', array('arrData' => $arrData));
}

和你的看法

<table class="table table-striped table-bordered table-hover dt-responsive" width="100%" id="sample_1">
    <thead>
    <tr>
        <th width="5%">Tanggal</th>
        <th width="3%">A</th>
        <th width="3%">I</th>
        <th width="3%">S</th>
        <th width="3%">H</th>
    </tr>
    </thead>
    <tbody>
    <?php foreach($arrData as $date => $arrItem)
    {
        ?>
        <tr>
            <td><?php echo $date; ?></td>
            <td><?php echo (isset($arrItem['A']))   ?   $arrItem['A']   :   0; ?></td>
            <td><?php echo (isset($arrItem['I']))   ?   $arrItem['I']   :   0; ?></td>
            <td><?php echo (isset($arrItem['S']))   ?   $arrItem['S']   :   0; ?></td>
            <td><?php echo (isset($arrItem['H']))   ?   $arrItem['H']   :   0; ?></td>
        </tr>
    <?php } ?>
    </tbody>
</table>