savagenoob savagenoob - 12 days ago 5
MySQL Question

MySQL Query Fails

I am trying to write a query that calculates SUMS of a row and total entries based on todays date. The date field in my database is in 03/14/2012 2:03:19 format. The query is ignoring the date and returning the SUM of all entries in the database. Here is my query:

$string = "
SELECT SUM(totalcollected)
FROM accounting
WHERE agent = '$agentid'
AND DATE(date) = DATE(NOW())
AND amount1type = 'Cash'
OR amount2type = 'Cash'
";


Any suggestions?

Answer

The problem is AND resolves before OR, but you can avoid the OR entirely with this cool trick:

SELECT SUM(totalcollected)
FROM accounting
WHERE agent = '$agentid'
AND DATE(date) = DATE(NOW())
AND 'Cash' IN (amount1type, amount2type)