display dates between start date and current date

[mrhankey]

hi,

 

i am trying to display the months between a start date and the current date.

 

the web system is used for payments so they have a start date in the database for first payment and it is marked is paid = N

 

what i need is to display in my query all months from start date until current date so they can then go and click paid = Y and this will insert into database.

 

so nothing will go into the database after start date until they mark it as paid.

 

i have tried this in my query:

DATE_ADD('TrailStartDate', INTERVAL Now() MONTH) as MonthlyDate

but no joy. just trying to display the months between trailstartdate and currentdate so i can display in a table for users to mark as paid or not.

 

any thoughts?

 

SELECT product.PolicyNumber, clients.ClientID, DATE_FORMAT(`TrailStartDate`,'%b') as MonthlyTrail
FROM trail join product on trail.product_ProductID = product.ProductID join clients on product.clients_ClientID = clients.ClientID
WHERE product.TrailYesNo = 'Y'

 

this is the query i am working with.

 

thanks

[mrhankey]

anyone?

[Barand]

I can't work out from what you've told us which table contains the start date and which table contains the dates you are trying to select (ie those between start and now)

[mrhankey]

thats the thing. if it was just simply selecting the dates from another table i could do this as i have done in other applications.

 

the issue is that we only ever have the first date.

 

the start date. then i need to show every month frmo that start date the record so they can mark as paid then it will insert into a table.

 

my issue is i dont know how i can select and show in my query by month from the start date until todays date.

 

so for example.

 

start date is 01/01/2013

 

i would need to show in my query results like this based on just that start date:

 

jan 2013 -

feb 2013 -

mar 2013

 

the i will display this on my php page and they can then update this by marking the months paid which i will insert into another table for to run a report on later.

 

do you see what needs to happen?

 

thanks

[Psycho]

I don't think it is feasible to do this in the query. Just create the "months" in the output using PHP. Just be sure to "normalize" the start date to the first of the month. Because if the start date is on Jan 31 and you increase it using "+1 month" you will get something like  March 3rd.

Edited March 22, 2013 by Psycho

[DavidAM]

Since they don't exist (in the database) you can't SELECT them. Well, you can write some complex code to get what you want, but I think the best approach is to create the monthly dates in the PHP script. However, once you have these dates, and mark them paid, and insert them into the database, you are going to have to include those already Paid dates in the query, and have the PHP script NOT generate them again.

 

Maybe if you tell us the layout of the tables involved (concerning the dates), we can offer an approach that won't create problems latter.

 

Edit: It's crazy how this Psycho keeps beating me to the punch!

Edited March 22, 2013 by DavidAM

[Barand]

One way to get it from the database would be to have a pre-prepared table containing columns year|month to match against,

[mrhankey]

thanks, yeah i thought it would be tricky without the values in the table until they are paid but i was hoping having something like

 

selecting the date as and adding a month until current date.

 

does not have to be future dates to flag up only current and past from the start date. anyway in mysql to do this?

 

not too great with complex php if i am honest. can do the insert update etc code but this seems real tricky. would feel better trying to run the query in mysql to display then i can show the results on my php page.

 

let me know what you think?

 

thanks again

[Barand]

I created a table with all dates from 2012-01-01 to 2014-12-31

 

I had a test event table with start and end dates

 

mysql> SELECT DISTINCT e.event_name, e.event_date, e.end_date,
    -> YEAR(d.thedate) as year, MONTHNAME(d.thedate) as month
    -> FROM event e
    ->   INNER JOIN dates d
    ->   ON d.thedate BETWEEN e.event_date AND e.end_date
    -> WHERE e.id = 8;
+---------------+------------+------------+------+----------+
| event_name    | event_date | end_date   | year | month    |
+---------------+------------+------------+------+----------+
| Test event #8 | 2012-10-10 | 2013-05-15 | 2012 | October  |
| Test event #8 | 2012-10-10 | 2013-05-15 | 2012 | November |
| Test event #8 | 2012-10-10 | 2013-05-15 | 2012 | December |
| Test event #8 | 2012-10-10 | 2013-05-15 | 2013 | January  |
| Test event #8 | 2012-10-10 | 2013-05-15 | 2013 | February |
| Test event #8 | 2012-10-10 | 2013-05-15 | 2013 | March    |
| Test event #8 | 2012-10-10 | 2013-05-15 | 2013 | April    |
| Test event #8 | 2012-10-10 | 2013-05-15 | 2013 | May      |
+---------------+------------+------------+------+----------+
8 rows in set (0.00 sec)