millsy007 Posted January 30, 2009 Share Posted January 30, 2009 I have a website form where the user selects: A date (from a calendar) eg 01/30/2009 A time from a drop down list eg 09:00 I then want to combine these values and query my mysql table, but I am unsure of the format to put it in. I am thinking: $start_dttm= $date.$time; and then querying SELECT * FROM shuttle WHERE start_dttm = '$start_dttm' however my sql record is in the datetime format of 2009-01-22 09:00:00 so I dont think this is working? Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/ Share on other sites More sharing options...
justinh Posted January 30, 2009 Share Posted January 30, 2009 however my sql record is in the datetime format of 2009-01-22 09:00:00 so I dont think this is working? that seems to be the date and time to me... Is there a certain format you wanted it to be? Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750631 Share on other sites More sharing options...
premiso Posted January 30, 2009 Share Posted January 30, 2009 $date = explode("/", $date); $date = $date[2] . '-' . $date[1] . '-' . $date[0]; $start_dttm= $date . ' ' . $time . ':00'; Should put it in that format. The above should work as far as I know. Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750633 Share on other sites More sharing options...
phparray Posted January 30, 2009 Share Posted January 30, 2009 this is a little simpler. $date = date('Y-m-d',strtotime($date)); Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750664 Share on other sites More sharing options...
premiso Posted January 30, 2009 Share Posted January 30, 2009 this is a little simpler. $date = date('Y-m-d',strtotime($date)); Maybe simpler, but about 8-10x slower. Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750667 Share on other sites More sharing options...
phparray Posted January 30, 2009 Share Posted January 30, 2009 Tushay Sir! Maybe slower but will work with any well formatted date not just one with slashes. Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750674 Share on other sites More sharing options...
premiso Posted January 30, 2009 Share Posted January 30, 2009 Tushay Sir! Maybe slower but will work with any well formatted date not just one with slashes. Agreed, it all depends on if the date format will always be that. If not then yes, the strtotime is better for extended functionality Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750677 Share on other sites More sharing options...
PFMaBiSmAd Posted January 30, 2009 Share Posted January 30, 2009 strtotime only works with slashes in the mm/dd/yyyy format, not dd/mm/yyyy, while the explode method can be tailored to work with either. The explode method also allows the date to be validated using checkdate Also since strtotime uses mktime/Unix Timestamp it suffers from the same 1970/2038 limitations, so it cannot be used for a lot of things like birthdays. Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750680 Share on other sites More sharing options...
millsy007 Posted January 30, 2009 Author Share Posted January 30, 2009 It is for an application only a couple people will use so speed is not so important, thanks for the feedback though. Works great for me Had another question though Is there a way that I could do a match just on the date. So if a user selects a date in the date field, I run a query that would bring out all records where the dates matched, ignoring the times? ie. If My SQL Table had id depart_dttm 1 2009-01-22 09:00:00 2 2009-01-22 22:00:00 and the user entered just the date in the date field, what would I pass into my sql query to bring out both records? Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750682 Share on other sites More sharing options...
premiso Posted January 30, 2009 Share Posted January 30, 2009 I believe using the DATE_FORMAT in MySQL in the where clause would allow for this. WHERE date_format(`depart_dttm`, '%Y-%c-%d') = '2009-01-22' Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750685 Share on other sites More sharing options...
PFMaBiSmAd Posted January 30, 2009 Share Posted January 30, 2009 The mysql DATE() function can do that directly - DATE(expr) Extracts the date part of the date or datetime expression expr. Quote Link to comment https://forums.phpfreaks.com/topic/143134-querying-mysql-date-using-php-variable/#findComment-750686 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.