cleary1981 Posted January 29, 2009 Share Posted January 29, 2009 Hi, I am lost. I have a database that has a field in the following format 2009-01-28 15:43:52 I then have values in php in the format of dd/mm/yyyy. I want to search my db to find the first occurence of a specified date. Can anyone help please? Link to comment https://forums.phpfreaks.com/topic/142941-solved-datetime-search-problem/ Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 What do you mean by 'first occurence'. The one closest to 2009-01-28 00:00:00 ? Anyway $date = "28/01/2009"; $dateArr = explode("/",$date); //split the date into array using / as delimiter $dateMySql = "{$dateArr[2]}-{$dateArr[1]}-{$dateArr[0]}"; //create astring with date formatted in MySQL compatible format $query = "SELECT * FROM table WHERE DATE(dateTimeField) = '$dateMySql' ORDER BY orderField ASC LIMIT 1"; orderField is the field by which you sort data in your table, to get 'first occurence' of date. If, as I said, you want date closest 2009-01-28 00:00:00, then instead of 'orderField' you should put the name of your dateTimeField. Link to comment https://forums.phpfreaks.com/topic/142941-solved-datetime-search-problem/#findComment-749472 Share on other sites More sharing options...
Errant_Shadow Posted January 29, 2009 Share Posted January 29, 2009 I'm having a similar problem. I need to retrieve data with yesterday's date. If I generate today's date using date("Y-m-d"); it shows up in my database with leading zeroes (2009-01-29). Can I explode that at the "-" and then manipulate the arrays as numbers or will they be strings? Link to comment https://forums.phpfreaks.com/topic/142941-solved-datetime-search-problem/#findComment-749483 Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 Why not just generate yesterday's date? echo date("Y-m-d",strtotime("yesterday")); Link to comment https://forums.phpfreaks.com/topic/142941-solved-datetime-search-problem/#findComment-749484 Share on other sites More sharing options...
Errant_Shadow Posted January 29, 2009 Share Posted January 29, 2009 Why not just generate yesterday's date? echo date("Y-m-d",strtotime("yesterday")); *rubs his eyes* I must not be reading that right. Wouldn't that generate todays date and save it in the variable "yesterday"? I'm not familiar with the code "strtotime" Link to comment https://forums.phpfreaks.com/topic/142941-solved-datetime-search-problem/#findComment-749487 Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 No... it will echo yesterdays date. If you want to store it into variable use: $yesterdaysDate = date("Y-m-d",strtotime("yesterday")); strtotime is pretty useful function. Read on it. Link to comment https://forums.phpfreaks.com/topic/142941-solved-datetime-search-problem/#findComment-749494 Share on other sites More sharing options...
Errant_Shadow Posted January 29, 2009 Share Posted January 29, 2009 Very educational, thank you Link to comment https://forums.phpfreaks.com/topic/142941-solved-datetime-search-problem/#findComment-749520 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.