peter_anderson Posted July 3, 2010 Share Posted July 3, 2010 Hi there, I need to perform a check against the orders database to see if 30 days since the order has passed or not. The date is stored as "11/Apr/2010" (j/M/Y), but I am unsure of how to do so. Can anyone point me in the right place? Thanks Quote Link to comment Share on other sites More sharing options...
robert_gsfame Posted July 3, 2010 Share Posted July 3, 2010 U can use mktime() Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted July 3, 2010 Share Posted July 3, 2010 MySQL has some very good date and time comparison and arithmetic functions you can use right in the query string. Have a look at these. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html Quote Link to comment Share on other sites More sharing options...
peter_anderson Posted July 3, 2010 Author Share Posted July 3, 2010 Thanks, How do I get the results from the MySql date time function query? Heres what I've got so far. <?php //some was stripped // date $today_date = date("Y-m-d"); // query $q = "SELECT STR_TO_DATE(date, '%j/%M/%Y') FROM `orders` WHERE id='$orderid' AND customer='$email'"; $rs = $sql->query($q); $r = $rs->fetch_assoc(); $order_date = $r['date']; // compare dates function date_diff($date1, $date2){ $current = $date1; $datetime2 = date_create($date2); $count = 0; while(date_create($current) < $datetime2){ $current = gmdate("Y-m-d", strtotime("+1 day", strtotime($current))); $count++; } return $count; } echo(date_diff('2010-01-01', $order_date)." days <br \>"); exit(); ?> How do I get the result of $q? Thanks Quote Link to comment Share on other sites More sharing options...
peter_anderson Posted July 3, 2010 Author Share Posted July 3, 2010 Just found it, that $order_date should be: <?php $order_date = $r['STR_TO_DATE(date, \'%j/%M/%Y\')']; ?> Quote Link to comment Share on other sites More sharing options...
fred12ned Posted July 3, 2010 Share Posted July 3, 2010 If you changed the SQL to SELECT STR_TO_DATE(date, '%j/%M/%Y') AS date_formatted FROM `orders` WHERE... You could have nicer looking array keys $r['date_formatted'] Quote Link to comment Share on other sites More sharing options...
peter_anderson Posted July 3, 2010 Author Share Posted July 3, 2010 Just noticed that the month is not working. 24/Mar/2010 is changing to 2010-01-24 using SELECT STR_TO_DATE(date, '%j/%M/%Y')... It's the same for all months. What could be the problem? Quote Link to comment Share on other sites More sharing options...
fred12ned Posted July 3, 2010 Share Posted July 3, 2010 It is the line $current = gmdate("Y-m-d", strtotime("+1 day", strtotime($current))); change it to $current = gmdate("d/m/Y", strtotime("+1 day", strtotime($current))); 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.