Monthly registrations and registrations with a date
I have a MySQL table containing fixedprice-registrations both monthly and with a certain date, which look like this:
id int(10)
date date
userid int(10)
montly tinyint(1)
price decimal(10,2)
And some data could be
id | date | userid | monthly | price
1 | 01/01 2011 | 1 | 1 | 200
2 | 01/02 2011 | 2 | 1 | 300
3 | 14/01 2011 | 1 | 0 | 400
4 | 15/02 2011 | 3 | 0 | 100
5 | 23/02 2011 | 2 | 0 | 600
6 | 05/03 2011 | 2 | 0 | 700
A monthly registration will always start on the 1st of the month
and if monthly
is 1 it is a monthly registration, otherwise it only happens once.
And a query could be (just an example)
SEL开发者_如何学编程ECT *
FROM `fixedpriceregistrations`
WHERE `date` BETWEEN '01/02 2011' AND '02/04 2011'
where I except this output:
id | date | userid | monthly | price
1 | 01/01 2011 | 1 | 1 | 200
2 | 01/02 2011 | 2 | 1 | 300
4 | 15/02 2011 | 3 | 0 | 100
5 | 23/02 2011 | 2 | 0 | 600
1 | 01/01 2011 | 1 | 1 | 200
2 | 01/02 2011 | 2 | 1 | 300
6 | 05/03 2011 | 2 | 0 | 700
1 | 01/01 2011 | 1 | 1 | 200
2 | 01/02 2011 | 2 | 1 | 300
Can I do that in MySQL or how is the best way to do it in PHP?
In PHP I thought about looping through the registrations with a date and add them to a new array, and when the month changes the monthly registrations are being added.
Do it with php. I would:
Fetch all the records you need (i.e. since start date) from the table into an array, $results
.
$output = Array();
foreach ($results as $row)
{
$output[] = $row;
if ($row['monthly'])
while ( ($row['date'] = add_a_month_to( $row['date'] )) < $end_date )
$output[] = $row;
}
Then sort the resulting $output
array. I leave the sort and the add_a_month_to
functions to you. Obviously it assumes you're using a sensible (sortable) date format, e.g. 2011-01-13
.
精彩评论