[SOLVED] Multiple dates in one SQL record - adding and sorting.

[r00ttap]

I just need some quick help on how to go about this. I've been working on a project that will post a message to a page by pulling the information from MySQL. This message will disappear on the end_date at the end_time. Simple enough right? Now it seems the users want some flexibility and instead of adding multiple messages manually one by one they want to be able to pick multiple dates from a calendar and the message will echo each of those dates letting people know that the message relates to those dates.

 

So the question is how do I go about adding multiple dates to ONE sql record? Right now I'm using a date array variable that holds all dates selected from this calendar in a space delimited YYYY-MM-DD format so once echoed it looks like this: 2009-06-01 2009-06-02 2009-06-03, etc...

 

Is there and easy method for handling multiple dates with SQL?

 

Thanks a lot.

[gassaz]

If you can change the DB structure add a table to store the dates of the messages...

[r00ttap]

gassaz,

 

I was thinking this also. Link both tables according to ID but what would the date table scheme look like?

[gassaz]

For example:

 

Dates Table

 

Id_dates  INT

Id_message  INT

start_date  DATE

end_date    DATE

 

 

[r00ttap]

Thanks gassaz, now I'm getting somewhere.

 

How would you take the array of dates and add them to the "dates" table so that each date has it's own row and id?

 

Right now the variable for the dates would be: $dateArray = "2009-06-09 2009-06-10 2009-06-10";

[gassaz]

I think it would be done with a php script or some programming language. The mean idea is select row by row on the message table and using the string methods of the programming language to separate the dates and then insert into the new table.

 

 

[r00ttap]

I ended up including the INSERT statement within the dateArray variable itself and it works fine.

 

Thanks for all your help gassaz.