Anyway to make COUNT(*) return 0?

[kilnakorr]

Hi

I'm using the below code

<?php
//Getting failed orders for Online
$data=mysqli_query($mysqli,"SELECT Error, COUNT(*) as total FROM db WHERE Department='Online' AND Date BETWEEN '$Fromdate' AND '$Todate' GROUP BY Date");
?>
var totalOnline=[<?php 
while($info=mysqli_fetch_array($data)){
echo $info['total'].',';} /* 
?>];

Now some of these doesn't return anything as it should, but is there a way to return 0 instead?

[Barand]

If your where clause doesn't match any records then nothing is returned, so you need to test for that condition (and output "0") before attempting your while loop.

Edited March 17, 2019 by Barand

[kilnakorr]

Thanks for the reply.

Yes, I was thinking the same thing. Testing if something is returned and if not set my variable to '0'.

I just can't seem to figure out how?!

[gw1500se]

if ($data===false) {
   // no results returned
{
else {
   //good return
}

 

[Barand]

Erm, no.

$data will be false if the query fails. Not finding any results is not a failure (and may in fact be the desired result).

Some thing like

$data = $db->query("SELECT ..." );
$info = $data->fetch();
if ($info) {
    do {
        echo $info['<whatever>'] .'<br>';
    }  while ($info = $data->fetch());
} else {
    echo 0;
}

Incidentally, your query makes no sense and would be invalid with many flvours of SQL. "Error" will have no relation to the count that is shown as you are grouping by date.

[kilnakorr]

Thanks for the reply, and very true the "Error" makes no sense at all.

I can't  get your code to work, maybe because it needs to be split up as the while loop, sets an array.

The main problem is, that I have the following code:

var myData=[<?php
$data=mysqli_query($mysqli,"SELECT Date, COUNT(*) as total FROM db WHERE Step='CPR' AND Error='1' AND Date BETWEEN '$Fromdate' AND '$Todate' GROUP BY Date");
while($info=mysqli_fetch_array($data)) 
echo '"'.$info['Date'].'",'; /* We use the concatenation operator '.' to add comma delimiters after each data value. */
?>];

The result of the above code is:

var myData=["2019-01-02","2019-01-03","2019-01-15","2019-01-18","2019-01-21","2019-01-22","2019-01-24","2019-01-28","2019-01-31","2019-02-01","2019-02-02","2019-02-06","2019-02-07","2019-02-08","2019-02-15","2019-02-17","2019-02-20","2019-02-25","2019-02-28","2019-03-05","2019-03-06","2019-03-15","2019-03-19","2019-03-30",];

Basically, getting every date where the 'Step' is CPR and 'Error' is 1.

Now I also need to get the same values but only for a specific 'Department':

$data=mysqli_query($mysqli,"SELECT COUNT(*) as total FROM jss_test WHERE Step='CPR' AND Error='1' AND Department='Kundeservice' AND Date BETWEEN '$Fromdate' AND '$Todate' GROUP BY Date");
?>
var totalKS=[<?php 
while($info=mysqli_fetch_array($data)){
    echo $info['total'].',';} /* The concatenation operator '.' is used here to create string values from our database names. */
?>];

 This returns:

var totalKS=[1,2,2,1,1,1,1,2,];

All good, but since I don't have same number of results, they don't really match up as intended.

 

What I need really is be able to have the same amount of results, but if  'Department' isn't 'Kundeservice' then return a "0"

 

Hope it makes sense, and someone got an idea.

 

[gw1500se]

Sorry. Misunderstood the question.

[Barand]

Perhaps

SELECT SUM(CASE WHEN Department='Kundeservice' THEN 1 
            ELSE 0
       END) as total 
FROM jss_test 
WHERE Step='CPR' 
  AND Error='1' 
  AND Date BETWEEN '$Fromdate' AND '$Todate' 
GROUP BY Date    

 

Edited March 17, 2019 by Barand
Correction to query

[kilnakorr]

14 hours ago, Barand said:

Perhaps

SELECT SUM(CASE WHEN Department='Kundeservice' THEN 1 
            ELSE 0
       END) as total 
FROM jss_test 
WHERE Step='CPR' 
  AND Error='1' 
  AND Date BETWEEN '$Fromdate' AND '$Todate' 
GROUP BY Date    

 

Thanks a bunch. I did get it to work doing an if statement in the loop, but this is much better!