[SOLVED] PHP: Only output rows with todays day???

[webmaster1]

Hi All,

 

I want to output all records that match under todays date:

 

select * from tablename where scheduledate <= 'today'

 

My column is in datetime: 00-00-0000 00:00

 

Does anyone know how to go about this?

[premiso]

You would have to format the date using MySQL's date format to remove the hours/minutes. Then use the NOW() and format that to use only the date as well.

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_curdate

[avvllvva]

Try this..

select * from tablename where SUBSTRING(scheduledate,1,10) <= 'today'

 

 

[FezEvils]

if you do not want to work with date, change the date into number@decimal..

example <?php echo $d= date ('Ymd'); ?> will return 20092202...

then use explode() to remove "-" in your date... so your date will be a number

use the sql that your just create earlier..

select * from tablename where scheduledate <= '$d'

 

but it only a suggestion if you had face difficulty using function date in php..

that all..bye

[fenway]

Try this..

select * from tablename where SUBSTRING(scheduledate,1,10) <= 'today'

 

 

Ugh... use DATE() functions for what they were meant for!

[avvllvva]

Yea this is the one....

$today = date("Y-m-d");
select * from tablename where SUBSTRING(scheduledate,1,10) <= '$today'

 

[kickstart]

Hi

 

Or keeping it in SQL:-

 

select * from tablename where scheduledate < date_format(date_add(now(), INTERVAL 24 hour),'%Y-%m-%d 00:00:00')

 

Basically add 24 hours to the date / time now, then just use the time of 00 and check the date is less. Should reduce the need for manipulating all the individual dates on the table.

 

The date formatting can probably be cleaned up.

 

All the best

 

Keith

[fenway]

Really? There's a DATE() function in mysql so that you do'nt need any of this magic.

[kickstart]

Hi

 

Using DATE() on NOW() isn't really going to do much unless you do the same for every date time field you want to compare against which is an unnecessary overhead. Hence my suggestion to convert the current date time to be the very start of tomorrow and then compare that against stored date / time field.

 

All the best

 

Keith