从表视图中获取数据而不在PHP中显示NULL行数据
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
While you haven't specified which DB engine you're using (SQL Server?) at first glance it looks like it might be down to the way you created the view in the first place. Have you tried using a LEFT JOIN or INNER JOIN which should automatically exclude NULL values from the left/right hand side of the JOIN?