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.
