metaphor metaphor - 5 months ago 6
PHP Question

get data from table view without displaying the NULL rows data in PHP

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:

enter image description here

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.

Answer

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
Comments