dongxi1879
dongxi1879
2016-06-03 02:22

从表视图中获取数据而不在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:

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongzhuan1185 dongzhuan1185 5年前

    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
    
    点赞 评论 复制链接分享
  • doutuobao9736 doutuobao9736 5年前

    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?

    点赞 评论 复制链接分享

相关推荐