donv29560 2014-01-17 07:04
浏览 138
已采纳

使用php中的group_concat列出空值

I have the following query:

$query4 = $db->query("SELECT count(codes.lcfruh) AS front_lcfruh, kw, datum,   GROUP_CONCAT(name) AS name FROM codes 
RIGHT JOIN dienstplan ON (dienstplan.schicht = codes.lcfruh OR dienstplan.schicht = codes.lcteildienst OR dienstplan.schicht = codes.lcshteil) 
RIGHT JOIN personal ON personal.perso_id = dienstplan.perso_id 
WHERE personal.status_sum = 'rezeption' AND dienstplan.schicht!='' AND dienstplan.kw = '$kw' AND personal.zeigen='ja' 
GROUP BY dienstplan.datum");

I want to have a result of 7 input fields (for every day of the week). That works correct. Both fields with the 0 or value greate than 0 are listed, actually input field are created.

while ($result = $query4 ->fetch_object()) {
echo '<p class="taglist1"><input name="" type="text" title="'.$result->name.'" class="zbroj'.$result->front_lcfruh.'" value="'.$result->front_lcfruh.'"></p>';
}

In the title I want to have names listed if they have certain value, that is working also UNTIL I put the following line in the code in the WHERE CLAUSE

AND (dienstplan.schicht = codes.lcfruh OR dienstplan.schicht = codes.lcteildienst OR     dienstplan.schicht = codes.lcshteil) 

The problem is the following in this case The result is not showing the fields that are not matching, where I should have the count(codes.lcfruh)=0. The fields that are not matching are not shown.

Is there a chance that I can put this line elsewhere in the code in order to get the null fields listed also.

  • 写回答

1条回答 默认 最新

  • douben6670 2014-01-17 07:11
    关注

    The code that you are putting into the where clause is:

    AND (dienstplan.schicht = codes.lcfruh OR
         dienstplan.schicht = codes.lcteildienst OR
         dienstplan.schicht = codes.lcshteil
        ) 
    

    Although you don't intend for this to be the case, this is also adding the conditions:

    dienstplan.schicht is not null and
    (codes.lcfruh is not null or dienstplan.schicht is not null OR dienstplan.schicht is not null)
    

    Because NULL values are treated as false in the where. This "undoes" the right outer join.

    The solution is to move the conditions to the on clause. Alternatively, you could add additional or clauses specifying that NULL is an ok value.

    EDIT:

    Try changing the where clause to:

    AND (dienstplan.schicht = codes.lcfruh OR
         dienstplan.schicht = codes.lcteildienst OR
         dienstplan.schicht = codes.lcshteil OR
         dienstplan.schicht is null or
         (codes.lcfruh is null and codes.lcteildienst is null and codes.lcshteil is null)
        ) 
    

    This will keep the rows that fail the right outer join.

    EDIT II:

    Next idea. Move the condition to the group_concat(). If the first query is working, then maybe this is what you want:

    GROUP_CONCAT(case when dienstplan.schicht = codes.lcfruh OR
                           dienstplan.schicht = codes.lcteildienst OR
                           dienstplan.schicht = codes.lcshteil
                      then ''
                      else name
                 end)
    

    If this doesn't work, then edit your question with sample data and desired results.

    EDIT III:

    Is this what you want?

    SELECT count(case when personal.status_sum = 'rezeption' AND dienstplan.kw = '52'
                      then codes.lcfruh
                 end) AS front_lcfruh,
           kw, datum,
           GROUP_CONCAT(case when personal.status_sum = 'rezeption' AND dienstplan.kw = '52'
                             then name
                        end) AS name
    FROM codes
    RIGHT JOIN dienstplan ON (dienstplan.schicht = codes.lcfruh)
    RIGHT JOIN personal ON personal.perso_id = dienstplan.perso_id
    GROUP BY dienstplan.datum;
    

    I moved the conditions into the select statement, so all the rows would appear.

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥50 使用ADC0809 与 51 单片机设计电路以实现显示电压模拟值
  • ¥15 QGC打开没有地图显示,离线地图也不显示,如何解决?
  • ¥20 Android Studio 的 webview 与访问网络存在的限制
  • ¥15 某帖子的数据集不清楚来源,求帮助
  • ¥15 idea构建mod报错无效的源发行版项目链接,如何解决?
  • ¥15 springboot中的路径问题
  • ¥80 App Store Connect 中设置了订阅项目,Xcode 中预览可以正确显示价格,真机测试却无法显示
  • ¥15 MATLAB的PIV算法问题
  • ¥15 RflySim例程学习:matlab编译报错
  • ¥20 谁来给我解答一下疑惑
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部