Jump to content

Anyway to make COUNT(*) return 0?


kilnakorr

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.