Jump to content
honkmaster

Query based on todays date

Recommended Posts

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'";
Edited by honkmaster

Share this post


Link to post
Share on other sites

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'";
Edited by QuickOldCar

Share this post


Link to post
Share on other sites

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??

Share this post


Link to post
Share on other sites

Or convert those unix timestamps to DATETIME value

AND DATE(FROM_UNIXTIME(quotes.quote_complete)) = CURDATE()
  • Like 1

Share this post


Link to post
Share on other sites

Use + or - the INTERVAL

 

eg Completed in last 7 days

 DATE(FROM_UNIXTIME(quotes.quote_complete)) > CURDATE() - INTERVAL 7 DAY

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.