[SOLVED] pull current day events from table

[bradkenyon]

I need a query that will only pull today's events out of the db table.

 

I have a field that stores dates, titled: expiredate

 

for example, an event that i want to display today has a expiredate of: 2008-08-19 19:21:00

 

I just want it to check for 2008-08-19

 

So any events w/ a expiredate of 2008-08-19 will be displayed.

 

Obviously it will check for 2008-08-20 tomorrow, so I just want the query to check for the event's YYYY-MM-DD and if it is equal to the current YYYY-MM-DD, then pull it.

 

I was thinking of something like.

 

SELECT * table WHERE expiredate(YYYY-MM-DD) = CURDATE(YYYY-MM-DD)

 

The above query is literal, so you can see what I am trying to achieve.

 

Any help is appreciated.

 

Thanks.

[bradkenyon]

$query = "select * from upcomingevents WHERE expiredate LIKE '$today %'";

 

w/ $today being today's date like YYYY-MM-DD

 

Looks like it is working.

 

I would like to know if there might be a case in which this may not.

 

Thanks.

[Barand]

SELECT * FROM upcomingevents WHERE DATE(expiredate) = CURDATE()

[fenway]

SELECT * FROM upcomingevents WHERE DATE(expiredate) = CURDATE()

I wonder if mysql will still use the index if you leave out the DATE() function and just let MySQL deal with the casting... as written, it definitely won't... maybe I'll go check.

[bradkenyon]

I need to check for YYYY-MM-DD of expiredate and current date, and not factor in the hour, min, secs.

 

This seems to not work:

 

SELECT * FROM upcomingevents WHERE DATE(expiredate) = CURDATE()

 

I was thinking this may do it, but it did not:

 

SELECT * FROM upcomingevents WHERE DATE(expiredate, '%Y-%m-%d') = DATE_FORMAT(CURDATE(),'%Y-%m-%d')

[fenway]

How can Barand's not work? Put those two expressions in the select column list and check them

[bradkenyon]

this is what I have, I have an event for today: 2008-08-20, and it does not display it, but if I have this

$query = "select * from upcomingevents WHERE expiredate LIKE '$today %'";

, it does.

 

and this does not:

<?php
$query = "SELECT * FROM upcomingevents WHERE DATE(expiredate) = CURDATE()";

$result=mysql_query($query);
if($result)
{
	?> <ul> <?php
	while($row = mysql_fetch_array($result))
	{
		$i = 0;
		$t = 1;

		while($i <=0)
		{
			print '<p><strong>'.date("l F j, Y",strtotime($row['expiredate'])).'</strong> - <a href="/events/?id='.$row['id'].'">'.$row['subj'].'</a> - <small>'.$row['location'].'</small></p>';
			//$message.= date("m/d/y",strtotime($row['expiredate']))."\t".$row['subj']."\t".$row['location']."\r\t\t".$row['body']."\r\r";
			$message.= ''.date("l F j, Y",strtotime($row['expiredate']))."\r\r\t".$row['subj']."\r\t".$row['location']."\r\t".$row['body']."\r---------------------------------------------------------------------------------------------------------\r\r";
			$i++;
			$t++;
		}
	}

$email = '[email protected]';

$mail = new htmlMimeMail();

$mail->setText($message);


$mail->setSubject('Faculty/Staff Events');

/**
* Sends the message.
*/
	$mail->setFrom($email);
	$result = $mail->send(array('[email protected]'));
	//echo $result ? 'Mail sent!' : 'Failed to send mail';
?>

 

 

[fenway]

Like I said, let's see what those two expressions are equal to.

[PFMaBiSmAd]

I have a field that stores dates, titled: expiredate

I am guessing that it is not a DATETIME data type, otherwise the suggested query would work.

[bradkenyon]

I have the expiredate in this format:

 

2008-09-05 20:00:00

 

I am guessing I should of put it in like this?

 

20080905200000

 

[PFMaBiSmAd]

Please post your table definition.

[bradkenyon]

hope this is what you're referring to.

 

 

 

[attachment deleted by admin]

[PFMaBiSmAd]

The query with date(...) = curdate() should be working unless curdate() is not what you think it is. Try the following query -

 

SELECT CURDATE()

 

Also, how are you forming the $today variable in your query that does work?

 

My guess is the time zone set on your mysql server is not what you expect.

[bradkenyon]

Stupid me put the format of the datetime as 2008-08-22 09:17:00

 

it should be 20080822091700

 

So $today = 2008-08-22 and it checks the year-month-day part of the expiredate, which does the trick.

 

next time i'll format it correctly.

[fenway]

Stupid me put the format of the datetime as 2008-08-22 09:17:00

That IS the correct format.