Pruning event dates using mysql

[generic]

I've got a little chunk of code that displays events by date but only showing upcoming dates. It *should* display any dates for today as well, but only displays dates starting tomorrow. I'm sure there's an easy fix but I can't get it working right. Any help would be greatly appreciated.

$result = mysql_query("SELECT * FROM tour WHERE eventdate > CURDATE() ORDER BY eventdate asc LIMIT 4");
while($row = mysql_fetch_array($result)) {
$row['eventdate'] = date('M.d',strtotime($row['eventdate']));echo '<li>&#187; '. $row['eventdate'] .' / <a href="tour.php?id='. $row['id'] .'"> '. $row['city'] .'</a></li>';
}
echo '</ul>';

Thanks in advance!

gen

[jatar_k]

[dev.mysql.com...]

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

so tomorrow is greater than today

you could create a date for yesterday in php and use that to compare in your query

[generic]

Thanks for the quick reply jatar_k, but how would I go about creating a date for yesterday? Doesn't CURDATE only grab the current date? I'm not sure I'm seeing the logic or how to even put that together unfortunately.

[rocknbil]

jatar_k posted the link to it, it's all there, date math . . . .

Take your selects out of the command so you can experiment (I have reasons for saying this, read on.) It's simple, > means "greater than." Probably been staring at it too long.

$limit = 4;
$order = 'eventdate';
$direction = 'desc';
//$range = 'eventdate > curdate()'; //future
//$range = 'eventdate < curdate()'; //past
$range = 'eventdate = curdate()'; //today
//$range = 'eventdate = date_sub(curdate(), interval 1 day)'; //yesterday
// From yesterday to 7 days into the future
//$range = 'eventdate >= date_sub(curdate(), interval 1 day) and eventdate <= date_add(curdate(), interval 7 day)';

$query = "select * from tour where $range order by $order $direction $limit";
$result = mysql_query("$query");

Now . . .experiment with that, see what each of those do. Be sure to comment out some as you uncomment others. Change desc to asc, watch the effect.

Then go back and put controls into your form that you parse out into $limit, $order, $direction, and $range, get the form values from the form to those variables . . . and you have something you can use. :-)

[generic]

That's excellent, thanks to both of you. The PHP site is like reading another language if you have trouble understanding PHP at all. It all depends on how you learn and I'm definitely a hands on guy. Anyway, I will give it a try and see what I can come up with. Thanks again for the help!

[rocknbil]

Just to add . . . keep in mind all of that is mysql, all of it, being stored in PHP variables, it applies to any language. Often I see coders working to the bone to do something in PHP that's inherent in mySQL. Keep the mySQL site bookmarked. :-P