Programming Help

Web Development



What's Here?

  • Members: 150,136
  • Replies: 507,702
  • Topics: 80,045
  • Snippets: 2,668
  • Tutorials: 707
  • Total Online: 2,224
  • Members: 96
  • Guests: 2,128

Welcome to Dream.In.Code
Become an Expert!

Join 150,136 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,224 people online right now. Registration is fast and FREE... Join Now!




Getting all dates after today from table.

 
 Reply to this topicStart new topic

Getting all dates after today from table.
jeffmc21
22 Jul, 2008 - 07:05 PM
Post #1

New D.I.C Head
*

Joined: 27 Nov, 2007
Posts: 48


My Contributions
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?
User is offlineProfile CardPM
+Quote Post

mocker
RE: Getting All Dates After Today From Table.
23 Jul, 2008 - 10:59 AM
Post #2

D.I.C Regular
Group Icon

Joined: 14 Oct, 2007
Posts: 290



Thanked: 17 times
Dream Kudos: 25
My Contributions
You basically answered your question..

SELECT * FROM YOUR_TABLE WHERE event_date >= date(now())

If you want to limit it to the next 4 events add
ORDER BY event_date LIMIT 4
User is offlineProfile CardPM
+Quote Post

jeffmc21
RE: Getting All Dates After Today From Table.
23 Jul, 2008 - 11:58 AM
Post #3

New D.I.C Head
*

Joined: 27 Nov, 2007
Posts: 48


My Contributions
QUOTE(mocker @ 23 Jul, 2008 - 11:59 AM) *

You basically answered your question..

SELECT * FROM YOUR_TABLE WHERE event_date >= date(now())

If you want to limit it to the next 4 events add
ORDER BY event_date LIMIT 4



Thanks. 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?
User is offlineProfile CardPM
+Quote Post

jeffmc21
RE: Getting All Dates After Today From Table.
23 Jul, 2008 - 06:06 PM
Post #4

New D.I.C Head
*

Joined: 27 Nov, 2007
Posts: 48


My Contributions
I actually ended up using this:

CODE

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.
User is offlineProfile CardPM
+Quote Post

jeffmc21
RE: Getting All Dates After Today From Table.
24 Jul, 2008 - 06:00 PM
Post #5

New D.I.C Head
*

Joined: 27 Nov, 2007
Posts: 48


My Contributions
Figuring this out slowly!

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 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?
User is offlineProfile CardPM
+Quote Post

Trogdor
RE: Getting All Dates After Today From Table.
28 Jul, 2008 - 04:29 AM
Post #6

D.I.C Addict
Group Icon

Joined: 6 Oct, 2006
Posts: 549



Thanked: 4 times
Dream Kudos: 125
My Contributions
instead of select * , you should specify the fieldnames that you want to select.
One of those should be Date_format('event_date', '%a %b %e')
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic
Time is now: 1/9/09 01:58AM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month