display and filter by date

[mwjansen]

i'm in the learning phases of php (i guess maybe we all are, but i'm at phase 1).

 

i've gotten everything i want to do so far - with help from here and the books i've purchased - except for this one.

 

i have a db with items that have dates associated with them. dates are added as needed - there are more than one date for each item in the db.

 

i want to display every item, but only the dates associated with each item based on a filter (ie later than curdate). i'm not sure how to go about this. i'm assuming i need a separate cell for each date. do i then run the query to only display the date fields that fit my criteria? or would i need a separate table for just the dates? so confused - any help is appreciated.

[DarkerAngel]

SELECT dates FROM table

[mwjansen]

thanks for the quick reply. by dates, i assume you mean 'date1, date2, date3 etc'.

and then apply a filter to each one?

[DarkerAngel]

well I was just making an educated guess, I don't know your table structure or how you have your data setup.

[mwjansen]

i guess the question was really a two-parter, the first part being what structure would be best suited for what i'm looking to do?

currently its a simple table with two date fields per row.

i'm not sure that's best way to go about it, as i'd have to filter each date field (there could be as many as twelve/row) individually?

[DarkerAngel]

You would have to explain to me exactly what it is your trying to accomplish. There is to much unknown for me to come up with a DB structure for you.

[mwjansen]

sorry.

 

i have a number of events, which happen on various dates.

ie event 1 : 12/30/08, 1/15/09, 2/20/09

event2 : 01/02/09, 2/15/09, 3/21/09

etc etc

 

i want every event to display, but within each event, only the dates which meet criteria, ie 30 days from curdate.

 

that make more sense?

[DarkerAngel]

Honestly if it were my DB I would have this table structure,

 

I would have 2 tables

 

event_description

 

event_dates

 

 

event_description:

id:int(16)

title:varchar(255)

description:text()

 

event_dates:

id:int(16)

event_id:int(16)

date:datetime();

 

something like that

 

I would select it like "SELECT event_dates.date as date, event_description.title as title FROM event_dates, event_description WHERE (NOW() - event_dates.date) <= 2592000 AND event_dates.event_id = event_description.id"

 

Something like that

[mwjansen]

that seems to make more sense than the way i was trying. appreciate the help, i'll give it a go and see how it works for me. thanks so much.

[mwjansen]

trying to thinking ahead, would you have a separate table for each date associated with the event? in other words how do i filter each date field?

[DarkerAngel]

I would have a seperate row for each date, and the dates would link with the

 

event_description.id with the event_dates.event_id

[mwjansen]

so in SELECT i would need this for each date row in the table, yes?

 

(NOW() - event_dates.date) <= 2592000 AND (NOW() - event_dates.date1) <= 2592000 etc etc for date2,date3 ...