Hi, i am writing a search result page. I want to query results from a sql table by specific date and hour in this page.
date and time saved in a same column (TIMESTAMP)
I was try with this code :
[php]
$date = $_GET[‘date’] ;
$hour = $_GET[‘hour’] ;
mysql_query(“SELECT * FROM table_name WHERE date_column LIKE ‘%{$date}%’ AND date_column LIKE ‘%{$hour}%’”);
[/php]
but this code is not perfect, because hour search not working correctly. Please anyone guide me.
That query will certainly not work how you want. Give me an example of $_GET[‘date’] and $_GET[‘hour’] values. Are they coming across as timestamps already, highly unlikely, or are they normal human readable dates and times?
Hi thank you for replay.
example :
date_column row 1 = “2014-07-26 18:00:00”
date_column row 2 = “2014-07-26 15:18:00”
date_column row 3 = “2014-07-26 12:00:18”
[php]
$date = ‘2014-07-26’ ;
$hour = ‘18’ ;
[/php]
I want to display 1st row’s value only. (because 18 means 6pm), but problem is it query all ‘18’ values from timestamp table. example 18 minut or 18 second :
how to set only query for hour ?
Ok, so you can’t compare a timestamp to a date value or time value, you have to compare it to another timestamp value. So you need to take the $_GET vars and make them into a timestamp. Here is an example based on your code. Since you wouldn’t want to search for an exact time using a timestamp cause it literally would only return results if they matched exactly that precise second timestamp value, I made it return results between the requested time AND + 1 hour.
[php]
$date = $_GET[‘hour’];
$hour = (int)$_GET[‘hour’];
$hour = $hour.’:00:00’;
$start = strtotime($date.’ '.$hour);
$end = strtotime("+1 hour", $start);
$sql = “SELECT * FROM table_name WHERE date_column BETWEEN $start AND $end”;
mysql_query($sql);
[/php]