honkmaster Posted June 8, 2015 Share Posted June 8, 2015 Hi look for some help with querying based on todays date. I'm trying to count all the entries in the database based on username, status, date. The date bit is where I' stuck (see screen shot of database) Based on data in database I should get a count of 3 but get nothing?? The result I get is 0 and should be 3 based on date Any help would be a great help Cheers Chris //date bit $d=strtotime("today"); $wholedate2 = date("Y-m-d", $d); //query $query_rsUser = "SELECT COUNT(*) FROM quotes WHERE quotes.quote_user = 'username' AND quotes.quote_complete = '$date2' AND quotes.quote_status = 'Complete'"; Link to comment https://forums.phpfreaks.com/topic/296699-query-based-on-todays-date/ Share on other sites More sharing options...
QuickOldCar Posted June 8, 2015 Share Posted June 8, 2015 Where is the screenshot your database? Link to comment https://forums.phpfreaks.com/topic/296699-query-based-on-todays-date/#findComment-1513434 Share on other sites More sharing options...
honkmaster Posted June 8, 2015 Author Share Posted June 8, 2015 Sorry here it is Link to comment https://forums.phpfreaks.com/topic/296699-query-based-on-todays-date/#findComment-1513435 Share on other sites More sharing options...
QuickOldCar Posted June 8, 2015 Share Posted June 8, 2015 First off you are using $wholedate2 and $date2, pick one. If you used datetime your way could have worked, since is a timestamp needs like this. This has to do calculations on everything and is a waste. $query_rsUser = "SELECT COUNT(*) FROM quotes WHERE quotes.quote_user = 'username' AND DATE(quotes.quote_complete) = CURDATE() AND quotes.quote_status = 'Complete'"; Instead something like: $query_rsUser = "SELECT COUNT(*) FROM quotes WHERE quotes.quote_user = 'username' AND quotes.quote_complete BETWEEN '".$wholedate2." 00:00:00' AND '".$wholedate2". 23:59:59' AND quotes.quote_status = 'Complete'"; Link to comment https://forums.phpfreaks.com/topic/296699-query-based-on-todays-date/#findComment-1513437 Share on other sites More sharing options...
honkmaster Posted June 8, 2015 Author Share Posted June 8, 2015 Thanks for response but still getting a 0 count when it should return a result, if I remove date part it works so issue is with how the query is comparing the stored unix date/time and the date today 2015-06-08?? Link to comment https://forums.phpfreaks.com/topic/296699-query-based-on-todays-date/#findComment-1513446 Share on other sites More sharing options...
Barand Posted June 8, 2015 Share Posted June 8, 2015 Or convert those unix timestamps to DATETIME value AND DATE(FROM_UNIXTIME(quotes.quote_complete)) = CURDATE() Link to comment https://forums.phpfreaks.com/topic/296699-query-based-on-todays-date/#findComment-1513447 Share on other sites More sharing options...
honkmaster Posted June 8, 2015 Author Share Posted June 8, 2015 Ok that worked perfectly, thanks for help, if I wanted to use a date in the passed or future do use DATE_ADD() and DATE_SUB() instead of CURDATE() ? Once again thanks for help Cheers Chris Link to comment https://forums.phpfreaks.com/topic/296699-query-based-on-todays-date/#findComment-1513452 Share on other sites More sharing options...
Barand Posted June 8, 2015 Share Posted June 8, 2015 Use + or - the INTERVAL eg Completed in last 7 days DATE(FROM_UNIXTIME(quotes.quote_complete)) > CURDATE() - INTERVAL 7 DAY Link to comment https://forums.phpfreaks.com/topic/296699-query-based-on-todays-date/#findComment-1513454 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.