How to retrieve Mysql data from todays date using PHP

I am using the PHP time() function to get the current time and Insert it into the database. I can also retrieve date by setting the value in a variable and doing something like: date(Y-m-d, $time) But now I want to get a result from the Database on the current date compared to the time inserted in the Database

I have tried:

SELECT sum(amount) as today_sales FROM activity WHERE type = 'debit' AND `time` = DATE(NOW())

But this query doesn’t work. It still displays 0 as a result

Answer

You’re using the wrong quotes. ' is for string data only.

SELECT sum(amount) as today_sales
  FROM activity WHERE type = 'debit' AND `time` = DATE(NOW())

Here 'debit' is a string. time is a column name, it uses identifier quotes.

What your query was doing was comparing the literal string 'time' to the current date, which of course will never match.

Tip: It’s generally a bad idea to have column names that conflict with reserved keywords which avoids a lot of this escaping mess in the first place.