Woodburn2006 Posted July 31, 2006 Share Posted July 31, 2006 i am trying to get a date from a database and comparing it to todays date, then depending on the date i want to display it or not display it. but i find that when using the time() function to return todays date in seconds, it is out of sinc with the returned results from the database this is the code that i use to get todays date in seconds:[code]$today_seconds = time(); // todays date in seconds from jan 1, 1970[/code]then this is the code that i use to sort out the dates that iwant once they have been returned from the database.[code]while ($row_present = mysql_fetch_array($rs_present)) { $startdate_present = $row_present[0]; $exhibition_present = $row_present[1]; $dateend_present = $row_present[2]; $start_present = split("-",$startdate_present); $end_present = split("-",$dateend_present); $startdate_seconds_present = mktime(0,0,0,$start_present[0],$start_present[1],$start_present[2]); $enddate_seconds_present = mktime(0,0,0,$end_present[0],$end_present[1],$end_present[2]); if ($startdate_seconds_present < $today_seconds && $enddate_seconds_present > $today_seconds){echo "$startdate_present - $dateend_present: $exhibition_present<br>";} }[/code]using this i always found that i would have odd outcomes. so i decided to change the date in the database to the same as todays date (31/7/2006) and see what the outcome in seconds was and this is what i got.31/7/2006 from DB: 1215385200 31/7/2006 from system: 1154383906it seems from this that one of the dates does not start from jan 1st 1970any help would be very much appreciated.thanks alot Quote Link to comment Share on other sites More sharing options...
Caesar Posted July 31, 2006 Share Posted July 31, 2006 If you're storing dates as timestamps in your database, what method are you using? time()? Quote Link to comment Share on other sites More sharing options...
Woodburn2006 Posted July 31, 2006 Author Share Posted July 31, 2006 i cannot even get an output from that. i put in the code you said and then put in echo $dbdate; where i wanted it to go and nothing appearedany other ideas? Quote Link to comment Share on other sites More sharing options...
Caesar Posted July 31, 2006 Share Posted July 31, 2006 [code]<?php$currdate = time();$convert = date("d/m/Y",$currdate);echo "Date = $convert<br>";echo "Timestamp = $currdate<br>";?>[/code]Try this little simple conversion. It should echo out the timestamp as today's date. Quote Link to comment Share on other sites More sharing options...
Woodburn2006 Posted July 31, 2006 Author Share Posted July 31, 2006 thanks, that proves that the date coming from the system is ok, but when i use the same date and convert it with mktime() function i get a different rsult.i used this code:[code]<?php$currdate = time();$convert = date("d/m/Y",$currdate);echo "Date = $convert<br>";echo "Timestamp = $currdate<br>";$todays_date = "01-08-2006"; $start_present = split("-",$todays_date);$start = mktime(0,0,0,$start_present[0],$start_present[1],$start_present[2]);echo "After mktime convert = $start";?>[/code]and i got this result:Date = 01/08/2006Timestamp = 1154388039After mktime convert = 1136678400 any ideas why they are so different? Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted July 31, 2006 Share Posted July 31, 2006 Please show us how you are storing the data in the database. Use this code:[code]<?phpwhile ($row_present = mysql_fetch_assoc($rs_present)) echo '<pre>' . print_r($row_present,true) . '</pre>';?>[/code]Post the first record or so that displays on your screen. From that we will have a better handle on how to help you.Ken Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 31, 2006 Share Posted July 31, 2006 mktime is months BEFORE day (the American date order).. so 01/08 is actually January 08 Quote Link to comment Share on other sites More sharing options...
Woodburn2006 Posted July 31, 2006 Author Share Posted July 31, 2006 Array( [DATE_FORMAT(startdate, '%d-%m-%Y')] => 30-07-2006 [exhibition] => Paul Newman [DATE_FORMAT(enddate, '%d-%m-%Y')] => 06-10-2028)that is the firstthe date is stored in the database as: YYYY-MM-DD using the 'date' datatypethanks Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 31, 2006 Share Posted July 31, 2006 well if you already use the "date" datatype, then you can use the WHERE clause[code] SELECT * FROM table WHERE CURDATE() BETWEEN startdate AND enddate [/code]this will be much more consistent. Quote Link to comment Share on other sites More sharing options...
Woodburn2006 Posted July 31, 2006 Author Share Posted July 31, 2006 ok thanks, im quite new so could you please show how i would use the curdate method instead please? i want to get the dates from the database and somehow get them into the same format as todays date so that i can display the ones i wantthanks Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 31, 2006 Share Posted July 31, 2006 oh CURDATE() is just a function to return today's date in YYYY-MM-DD format. Quote Link to comment Share on other sites More sharing options...
Woodburn2006 Posted July 31, 2006 Author Share Posted July 31, 2006 ok, is there anyway i could use that to compare to the dates that i get from the database? Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 31, 2006 Share Posted July 31, 2006 in a query[code]SELECT * FROM table WHERE CURDATE() >= startdate[/code]or if you prefer to select it[code] $query = "SELECT CURDATE()"; [/code] Quote Link to comment Share on other sites More sharing options...
Woodburn2006 Posted August 1, 2006 Author Share Posted August 1, 2006 thanks,ive used[code] $sql_new = "SELECT * FROM events WHERE CURDATE() >= startdate"; $result = mysql_query($sql_new, $connection); if (mysql_error()) { print "Database Error: $sql " . mysql_error(); }[/code]and then used this to display it:[code]while ($row_new = mysql_fetch_array($result)) { echo $row_new[0];}[/code]but i get the result '23', what would the variable be?i have tried $row_new[0]; and $startdate but '$startdate' has nothing and $row_new[0] has the value of '23' Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted August 1, 2006 Share Posted August 1, 2006 extract($row_new) Quote Link to comment Share on other sites More sharing options...
Caesar Posted August 1, 2006 Share Posted August 1, 2006 Wood:[code]<?php$currdate = time();$convert = date("d-m-Y",$currdate);$date2 = '31-07-2006';$convert2 = strtotime($date2);$arr = explode('-',$date2);$format = "".$arr[0]."-".$arr[1]."-".$arr[2]."";echo "$convert<br>";echo "$format<br>";?>[/code] Quote Link to comment Share on other sites More sharing options...
Woodburn2006 Posted August 1, 2006 Author Share Posted August 1, 2006 cool thanks everyone for your helpsaved my lifethanks again Quote Link to comment 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.