Jump to content
kilnakorr

Anyway to make COUNT(*) return 0?

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?

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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?!

Share this post


Link to post
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.

Share this post


Link to post
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.

 

Share this post


Link to post
Share on other sites

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 by Barand
Correction to query

Share this post


Link to post
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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.