CODEWITHSUNDEEP
phpmysqlmysqliphpmyadmin
rtstorm
rtstorm

Reputation: 345

PHP mysql query that does not count 0000-00-00

I have 4 dates that I need to count, the first date needs to be equal to $_POST and other 3 should not count 0000-00-00 as a date.

I tried this query:

$sql_list = "SELECT Agent,count(datum_obrade) as obrada, 
count(datum_kontakta) as kontakti,
count(datum_vracanja) as zadrzani,
count(datum_terminacije) as terminacija from wb_base WHERE datum_obrade = '".$_POST["From"]."' 
group by Agent";

The problem is that count() will count 0000-00-00 as a date (my column is PHPMyAdmin is set as DATE) I notice if I manually set date to NULL in DB the query wont count it, but the problem is that my jquery date picker won't return NULL into db if it is empty. I also asked this question here

So how do I set this query to count all that dates that are not 0000-00-00. If I add AND after WHERE then the query needs to match 3 criteria. Also if I use OR it will still count wrong.

Upvotes: 0

Views: 252

Answers (2)

Uueerdo
Uueerdo

Reputation: 15961

SELECT `Agent`
  , COUNT(NULLIF(datum_obrade, '0000-00-00')) as obrada
  , COUNT(NULLIF(datum_kontakta, '0000-00-00')) as kontakti
  , COUNT(NULLIF(datum_vracanja, '0000-00-00')) as zadrzani
  , COUNT(NULLIF(datum_terminacije, '0000-00-00')) as terminacija 
FROM wb_base 
WHERE datum_obrade = ?
GROUP BY `Agent`
;

These NULLIF() functions will convert values matching '0000-00-00' to null, and otherwise use the values that do not.

Upvotes: 0

Carsten Massmann
Carsten Massmann

Reputation: 28206

Try this

sql_list = "SELECT Agent,count(datum_obrade) as obrada, 
count(case when datum_kontakta>0 then 1 end) as kontakti,
count(case when datum_vracanja>0 then 1 end) as zadrzani,
count(case when datum_terminacije>0 then 1 end) as terminacija from wb_base WHERE datum_obrade = '".$_POST["From"]."' 
group by Agent";

The case clause filters out all entries that are not >0. In these cases a null value is sent to the count() function where it will not score. So only "valid" dates are counted in the end.

Upvotes: 1

Related Questions