get data from the table view without displaying the data of the NULL lines in PHP

advertisements

I want to get data from table view (PostgreSQL) without displaying the NULL rows data in PHP (CodeIgniter). I have the following view records:

date_sampling       type_sampling      sampling_point          shift    ha_tpc      ha_entero       fr_name               fr_tpc    fr_entero   cooling_name    cooling_tpc    cooling_entero   hpm_name    hpm_tpc   hpm_entero    fluid_name          fluid_tpc   fluid_entero

2016-02-02          Personnel           Okta ( OPR FDR )        1B       450            50          NULL                   NULL       NULL          NULL            NULL           NULL           NULL        NULL      NULL         Okta ( OPR FDR )      450        50
2016-02-02          Personnel           Farlan ( OPR FR )       1B       400            50          Farlan ( OPR FR )      400        50            NULL            NULL           NULL           NULL        NULL      NULL         NULL                  NULL       NULL

I created the table view by this queries:

WITH table_tanggal AS (
         SELECT date_sampling.date_sampling::date AS date_sampling
           FROM generate_series((( SELECT min(t_f066hdr.date_sampling) AS min
                   FROM t_f066hdr))::timestamp with time zone, (( SELECT max(t_f066hdr.date_sampling) AS max
                   FROM t_f066hdr))::timestamp with time zone, '1 day'::interval) date_sampling(date_sampling)
        ), table066 AS (
         SELECT a.date_sampling, a.location_sampling, b.detail_id, b.sampling_point, b.type_sampling, b.no_lab,
            b.area, b.sampling_tgl, b.sampling_time, b.sampling_analys, b.shift, b.analysis_by,
            b.ha_tpc, b.ha_entero, b.ha_ecoli_gas, b.ha_ecoli_indol, b.ha_salmonella_he,
            b.ha_salmonella_xld, b.ha_salmonella_bsa, b.report_by, b.report_date,
            b.remarks, b.operator, b.operator2
           FROM t_f066hdr a
      JOIN t_f066dtl b ON a.headerid = b.headerid
        )
 SELECT table_tanggal.date_sampling, table066.type_sampling,
    table066.sampling_point, table066.shift,
     table066.ha_tpc, table066.ha_entero,  

        -- Person of Filling Room
        CASE
            WHEN table066.sampling_point ~* '.*(fr).*' AND table066.type_sampling = 'Personnel' THEN table066.sampling_point
        END AS fr_name,
        CASE
            WHEN table066.sampling_point ~* '.*(fr).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_tpc
        END AS fr_tpc,
        CASE
            WHEN table066.sampling_point ~* '.*(fr).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_entero
        END AS fr_entero, 

        -- Person of Cooling/Packing Room
        CASE
            WHEN table066.sampling_point ~* '.*(P1).*' OR table066.sampling_point ~* '.*(P2).*' AND table066.type_sampling = 'Personnel' THEN table066.sampling_point
        END AS cooling_name,
        CASE
            WHEN table066.sampling_point ~* '.*(P1).*' OR table066.sampling_point ~* '.*(P2).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_tpc
        END AS cooling_tpc,
        CASE
            WHEN table066.sampling_point ~* '.*(P1).*' OR table066.sampling_point ~* '.*(P2).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_entero
        END AS cooling_entero, 

        -- Person of HPM
        CASE
            WHEN table066.sampling_point ~* '.*(flex).*' AND table066.type_sampling = 'Personnel' THEN table066.sampling_point
        END AS hpm_name,
        CASE
            WHEN table066.sampling_point ~* '.*(flex).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_tpc
        END AS hpm_tpc,
        CASE
            WHEN table066.sampling_point ~* '.*(flex).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_entero
        END AS hpm_entero, 

        -- Person of Fluidizer
        CASE
            WHEN table066.sampling_point ~* '.*(fdr).*' AND table066.type_sampling = 'Personnel' THEN table066.sampling_point
        END AS fluid_name,
        CASE
            WHEN table066.sampling_point ~* '.*(fdr).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_tpc
        END AS fluid_tpc,
        CASE
            WHEN table066.sampling_point ~* '.*(fdr).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_entero
        END AS fluid_entero
   FROM table_tanggal
   FULL JOIN table066 ON table_tanggal.date_sampling = table066.date_sampling
  WHERE table066.location_sampling = 'CMP';

In my Model, i run the view by this query:

[...]
$dtquery = "select * from view_recap_swabbing_personnel where date_sampling >= '2016-02-01' and date_sampling <='2016-02-10' order by date_sampling asc"
[...]

and in my Controller, i executed the query with the following statement:

[...]
$data_detail1 = $this->M_che01->get_viewreport($dtquery);

$data = array ('data_detail1'=>$data_detail1);
[...]

My View file to get data records:

[...]
<tbody>
      <?php
      if (isset($data_detail1)) {
          foreach($data_detail1 as $laprow1) { ?>

      <tr>
          <td><?php echo $laprow1->date_sampling.'/'.$laprow1->shift;?></td>

          <td><?php echo $laprow1->fr_name;?></td>
          <td><?php echo $laprow1->fr_tpc;?></td>
          <td><?php echo $laprow1->fr_entero;?></td>
          <td><?php echo $laprow1->fr_ecoli;?></td>
          <td><?php echo $laprow1->fr_salmo;?></td>

          <td><?php echo $laprow1->cooling_name;?></td>
          <td><?php echo $laprow1->cooling_tpc;?></td>
          <td><?php echo $laprow1->cooling_entero;?></td>
          <td><?php echo $laprow1->cooling_ecoli;?></td>
          <td><?php echo $laprow1->cooling_salmo;?></td>

          <td><?php echo $laprow1->hpm_name;?></td>
          <td><?php echo $laprow1->hpm_tpc;?></td>
          <td><?php echo $laprow1->hpm_entero;?></td>
          <td><?php echo $laprow1->hpm_ecoli;?></td>
          <td><?php echo $laprow1->hpm_salmo;?></td>

          <td><?php echo $laprow1->fluid_name;?></td>
          <td><?php echo $laprow1->fluid_tpc;?></td>
          <td><?php echo $laprow1->fluid_entero;?></td>
          <td><?php echo $laprow1->fluid_ecoli;?></td>
          <td><?php echo $laprow1->fluid_salmo;?></td>
      </tr>
</tbody>
[...]

I successful get the data like the screenshot below:

As you can see, there are 2 row displayed in one date 2016-02-02, same like the table view data records. But what i want is how to make the data only display in 1 row, only 1 date row displayed and data in row 2 should be move in to row 1.

Thanks.


Maybe group by date_sampling and get max values for columns.

WITH table_tanggal AS (
         SELECT date_sampling.date_sampling::date AS date_sampling
           FROM generate_series((( SELECT min(t_f066hdr.date_sampling) AS min
                   FROM t_f066hdr))::timestamp with time zone, (( SELECT max(t_f066hdr.date_sampling) AS max
                   FROM t_f066hdr))::timestamp with time zone, '1 day'::interval) date_sampling(date_sampling)
        ), table066 AS (
         SELECT a.date_sampling, a.location_sampling, b.detail_id, b.sampling_point, b.type_sampling, b.no_lab,
            b.area, b.sampling_tgl, b.sampling_time, b.sampling_analys, b.shift, b.analysis_by,
            b.ha_tpc, b.ha_entero, b.ha_ecoli_gas, b.ha_ecoli_indol, b.ha_salmonella_he,
            b.ha_salmonella_xld, b.ha_salmonella_bsa, b.report_by, b.report_date,
            b.remarks, b.operator, b.operator2
           FROM t_f066hdr a
      JOIN t_f066dtl b ON a.headerid = b.headerid
        ),
 ResultTable AS (
 SELECT table_tanggal.date_sampling, table066.type_sampling,
    table066.sampling_point, table066.shift,
     table066.ha_tpc, table066.ha_entero,  

        -- Person of Filling Room
        CASE
            WHEN table066.sampling_point ~* '.*(fr).*' AND table066.type_sampling = 'Personnel' THEN table066.sampling_point
        END AS fr_name,
        CASE
            WHEN table066.sampling_point ~* '.*(fr).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_tpc
        END AS fr_tpc,
        CASE
            WHEN table066.sampling_point ~* '.*(fr).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_entero
        END AS fr_entero, 

        -- Person of Cooling/Packing Room
        CASE
            WHEN table066.sampling_point ~* '.*(P1).*' OR table066.sampling_point ~* '.*(P2).*' AND table066.type_sampling = 'Personnel' THEN table066.sampling_point
        END AS cooling_name,
        CASE
            WHEN table066.sampling_point ~* '.*(P1).*' OR table066.sampling_point ~* '.*(P2).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_tpc
        END AS cooling_tpc,
        CASE
            WHEN table066.sampling_point ~* '.*(P1).*' OR table066.sampling_point ~* '.*(P2).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_entero
        END AS cooling_entero, 

        -- Person of HPM
        CASE
            WHEN table066.sampling_point ~* '.*(flex).*' AND table066.type_sampling = 'Personnel' THEN table066.sampling_point
        END AS hpm_name,
        CASE
            WHEN table066.sampling_point ~* '.*(flex).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_tpc
        END AS hpm_tpc,
        CASE
            WHEN table066.sampling_point ~* '.*(flex).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_entero
        END AS hpm_entero, 

        -- Person of Fluidizer
        CASE
            WHEN table066.sampling_point ~* '.*(fdr).*' AND table066.type_sampling = 'Personnel' THEN table066.sampling_point
        END AS fluid_name,
        CASE
            WHEN table066.sampling_point ~* '.*(fdr).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_tpc
        END AS fluid_tpc,
        CASE
            WHEN table066.sampling_point ~* '.*(fdr).*' AND table066.type_sampling = 'Personnel' THEN table066.ha_entero
        END AS fluid_entero
   FROM table_tanggal
   FULL JOIN table066 ON table_tanggal.date_sampling = table066.date_sampling
  WHERE table066.location_sampling = 'CMP')
  select date_sampling,
    max(type_sampling) as type_sampling ,
    max(sampling_point) as sampling_point,
    max(shift) as shift,
    max(ha_tpc) as ha_tpc,
    max(ha_entero) as   ha_entero
    max(fr_name) as fr_name,
    max(fr_tpc_) as fr_tpc,
    max(fr_entero) as fr_entero,
    max(cooling_name) as cooling_name,
    max(cooling_tpc) as cooling_tpc,
    max(cooling_entero) as cooling_entero,
    max(hpm_name) as hpm_name,
    max(hpm_tpc) as hpm_tpc,
    max(hpm_entero) as hpm_entero,
    max(fluid_name) as fluid_name,
    max(fluid_tpc) as fluid_tpc,
    max( fluid_entero) as  fluid_entero
    from ResultTable
  group by date_sampling