Mysql查询和php（循环）获取相应的值

Question

Assuming I have a table contain pID values and amount values,

|   pID     |   amount  |   date        |
|    1      |    20     |   2014-10-1   |
|    1      |    30     |   2014-12-30  |
|    2      |    40     |   2014-10-10  |
|    2      |     3     |   2014-12-01  |

I want to use both mysql query and php (loop) to get a pID corresponding with its amount for just one pID for different rows,

Below is how I want my answer to be in

For queryAns 1 I select data from 2014-10-1 to 2014-10-30

queryAns 1
|   pID     |   amount  |
|    1      |      20   |
|    2      |      40   |

For queryAns 2 I select data from 2014-12-1 to 2014-12-30

queryAns 2
|   pID     |   amount  |
|    1      |      30   |
|    2      |       3   |

UPDATE Sorry guys, i guess i missed the date in the maintable The condition will be based on a particular month and year. I would also wish to query the data based on a date range, for instance selecting data from 2014-10-1 to 2014-10-10

Thanks.

Answer 1

you can do your select like this:

SELECT
 maintable.pID,
 maintable.amount,
 DATE_FORMAT(maintable.date, '%Y-%m') as date
FROM
 maintable
WHERE
 maintable.date BETWEEN '2014-10-01' AND '2014-10-10'
 OR maintable.date BETWEEN '2014-12-01' AND '2014-12-05'
GROUP BY
 YEAR(maintable.date), MONTH(maintable.date)