开发者

Monthly registrations and registrations with a date

开发者 https://www.devze.com 2023-02-10 23:34 出处:网络
I have a MySQL table containing fixedprice-registrations both monthly and with a certain date, which look like this:

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消