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? Quote 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. Quote 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? Quote 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")); Quote 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" Quote 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. Quote 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 Quote Link to comment https://forums.phpfreaks.com/topic/142941-solved-datetime-search-problem/#findComment-749520 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.