[SOLVED] USING NOW() to display list of today

[samona]

Hi,

I have an ongoing assignment that I'm programming.  Thanks for all previous help.  Right now

I have stored some records in a database using NOW() as the value for the created field.

 

I am trying to display only records that have been created today and my query is producing incorrect results.

 

For example,

 

$sql = "Select name, task  From 'table' where created= CURDATE()";

 

It's  not displaying anything.  I noticed that the created field has (year-month-day hr-min-sec), so I'm not sure how I would query for what was created in (year-month-day) and NOT hr-min-sec.

 

Any help will be appreciated.

[fenway]

$sql = "Select name, task  From 'table' where created= CURDATE()";

 

Try this:

$sql = "Select name, task  From 'table' where DATE(created)= CURDATE()";

 

[samona]

I'm getting the following error:

 

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '( created ) = CURDATE( )

LIMIT 0, 30' at line 1

 

I was reading the DATE() function in the php manual and it doesn't show a way to convernet NOW() to DATE(), so am I correct to say that MySQL is not capable of performing this conversion?

[samona]

I don't know how great this code is but it works:

 

Select * From task_user WHERE DATE_FORMAT(created, '%Y %m %d') = DATE_FORMAT(CURDATE(), '%Y %m %d')

That worked to display tasks that were created today.  I hope that's useful to someone.  If anyone knows any better way to do it, please post it.  Thanks!

[samona]

OK, to do yesterday you would write the following query

 

 

Select * From task_user WHERE DATE_FORMAT(created, '%Y %m %d') = DATE_FORMAT(CURDATE() - INTERVAL 1 DAY, '%Y %m %d')

 

 

Again, I'm new at this stuff so I don't know how optimized this code is.  If anyone knows better please post.  For the rest of us newbies this works.

 

Thanks to fenway for helping me with the idea.

[fenway]

I was reading the DATE() function in the php manual and it doesn't show a way to convernet NOW() to DATE(), so am I correct to say that MySQL is not capable of performing this conversion?

This is a MySQL function, not a php function!  But it's possible there's a version issue here...