使用php中的group_concat列出空值

Question

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.

Answer 1

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.