Getting all dates after today from existing table

[jeffmc21]

I have a table with date field 'event_date' and I want to get all records where 'event_date' >= today's date. Basically, I want to retrieve all upcoming events (acutally next 4, but let's shoot for all of them for right now).

Any advice on the SQL syntax to use to accomplish this?

[PremiumBlend]

You'll want to use the CURDATE() function.

SELECT * FROM tableName WHERE event_date >= CURDATE()

[jeffmc21]

Thanks so much! That works perfectly! However, I'm now stuck again...

It obviously returns 4 rows from the table, each of the rows containing an event_id, event_date, and event_name field.

How do I echo or print the results properly? I'm wanting a list, similar to this:

<li>event_date1 - event_name1</li>
<li>event_date2 - event_name2</li>....etc.

Any advice?

[PremiumBlend]

You'll want to loop through all the rows using these lines of code:


while ($row = mysql_fetch_assoc($result))
{
echo '<li>Event Date: ' . $row['event_date'] . ' - '. $row['event_name'] . '</li>';
}


Beware though, I'm typing this from memory.

[jeffmc21]

I actually ended up using this:


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<li>'. $row["event_date"] .' - '. $row["event_name"].'</li>';
}


Any idea on how to re-format the date field? I've been working on it for hours, and I think I'm dancing all around the answer, but can't seem to get the right one.

It comes from the Database as YYYY-MM-DD. Preferably, I'd like to have it as Month, DD. But I'd settle for MM-DD or MM/DD.

[guelphdad]

use DATE_FORMAT directly in the mysql call and you can display it any way you choose. See the manual for details.

[jeffmc21]

My SQL call looks like this currently:

SELECT * FROM `tbl_eventsName` WHERE `event_date` >= CURDATE() ORDER BY event_date LIMIT 4


Where would I add the suggested Date_format('event_date', '%a %b %e') adjustment? I looked at the manual and several online examples, and I understand the format I need for the date and what it's doing, but where would I place it in my call?