1

I have data inserted into table 'dnt' with colum date being php datetime. 

$date = time();// this was inserted into the db as :1481811673
$today =  time();
"SELECT * FROM `dnt` WHERE 'date' = '$today'";
Emelda Chisom Agbo
  • 45
  • 1
  • 7

4 Answers4

1

This appears to answer your question http://www.tomjepson.co.uk/mysql-select-from-table-where-date-today/

tldr;

SELECT * FROM myTable WHERE DATE(myDate) = DATE(NOW())

Jez Emery
  • 676
  • 4
  • 18
1

Short and simple:

$stmt = "SELECT * FROM `dnt` WHERE 'date' = '".date('Y-m-d')."'";

To work with a timestamp:

$now = new DateTime();
$stmt = "SELECT *
FROM table
WHERE date = '".$now->getTimestamp()."'";

Detail

What the above query does is to SELECT all (*) FROM table dnt WHERE date =

the date() function in PHP returns a certain date based on the parameters you put in. the Y is for the years, the m for the months and the d for the days. So it will become date('Y-m-d') which will return 2010-01-01 for example.

the '". and ."' are to escape the php function so that it will not give you any syntax errors.

Peter
  • 8,776
  • 6
  • 62
  • 95
  • Can you please elaborate how `date` field of DB which is a time stamp(like: 345635234) that is integer can be compared with `date(Y-m-d)` which is string(like: 2016-12-12) ? – Abhay Maurya Dec 15 '16 at 19:02
1

You cannot compare a timestamp with date today as timestamp changes per second so to compare right, you need to convert the timestamp stored in db into a dateformat and then compare that date with today date. You can do it as follows:

$today = date('Y-m-d'); // date today in format - YYYY-mm-dd
//your query
"SELECT * FROM `dnt` WHERE DATE_FORMAT(FROM_UNIXTIME(dnt.date), '%Y-%m-%d') = '$today'";

I hope it helps

Abhay Maurya
  • 11,819
  • 8
  • 46
  • 64
0

Besides what Peter said - the query is incorrect. You are comparing the date string value (date between single quotes) to a timestamp.

$stmt = "SELECT * FROM `dnt` WHERE `date` = '".date('Y-m-d')."'";

zeb
  • 99
  • 4