从垂直数据库水平获取数据以进行制图

Question

I'm looking to create a chart by querying a table in our database.

I am having trouble understanding how to get the data horizontally from the database so it plugs into the chart nicely.

How do people normally get data prepared for a chart which excepts the data to be inputted horizontally?

I'm using PHP and mysql.

My->Mysql Query =:

SELECT SUM( production_net ) AS prodnet, production_shift, CONCAT( WEEK( production_date ) , '-', YEAR( production_date )) AS weekno
    FROM production
    WHERE production_shift IN('1','2','3')
    AND product_id = '1319'
    AND production_date
    BETWEEN '2013-01-01'
    AND '2013-06-30'
    GROUP BY production_shift, weekno
    ORDER BY production_shift, CASE weekno
    WHEN '0-2013' THEN 1
    WHEN '1-2013' THEN 2
    WHEN '2-2013' THEN 3
    WHEN '3-2013' THEN 4
    WHEN '4-2013' THEN 5
    WHEN '5-2013' THEN 6
    WHEN '6-2013' THEN 7
    WHEN '7-2013' THEN 8
    WHEN '8-2013' THEN 9
    WHEN '9-2013' THEN 10
    WHEN '10-2013' THEN 11
    WHEN '11-2013' THEN 12
    WHEN '12-2013' THEN 13
    WHEN '13-2013' THEN 14
    WHEN '14-2013' THEN 15
    WHEN '15-2013' THEN 16
    WHEN '16-2013' THEN 17
    WHEN '17-2013' THEN 18
    WHEN '18-2013' THEN 19
    WHEN '19-2013' THEN 20
    WHEN '20-2013' THEN 21
    WHEN '21-2013' THEN 22
    WHEN '22-2013' THEN 23
    WHEN '23-2013' THEN 24
    WHEN '24-2013' THEN 25
    WHEN '25-2013' THEN 26
    WHEN '26-2013' THEN 27
    WHEN '27-2013' THEN 28
    WHEN '28-2013' THEN 29
    WHEN '29-2013' THEN 30
    WHEN '30-2013' THEN 31
    WHEN '31-2013' THEN 32
    WHEN '32-2013' THEN 33
    WHEN '33-2013' THEN 34
    WHEN '34-2013' THEN 35
    WHEN '35-2013' THEN 36
    WHEN '36-2013' THEN 37
    WHEN '37-2013' THEN 38
    WHEN '38-2013' THEN 39
    WHEN '39-2013' THEN 40
    WHEN '40-2013' THEN 41
    WHEN '41-2013' THEN 42
    WHEN '42-2013' THEN 43
    WHEN '43-2013' THEN 44
    WHEN '44-2013' THEN 45
    WHEN '45-2013' THEN 46
    WHEN '46-2013' THEN 47
    WHEN '47-2013' THEN 48
    WHEN '48-2013' THEN 49
    WHEN '49-2013' THEN 50
    WHEN '50-2013' THEN 51
    WHEN '51-2013' THEN 52
    WHEN '52-2013' THEN 53
    WHEN '53-2013' THEN 54
    WHEN '54-2013' THEN 55
    END

producation_date    shift   part_net
2013-01-12          1   810
2013-01-13          1   1537
2013-01-14          1   731
2013-01-15          1   75
2013-01-16          1   326
2013-01-17          1   786
2013-01-18          1   1586
2013-01-19          1   1739
2013-01-20          1   1740
2013-01-21          1   1215
2013-01-22          1   1004
2013-01-12          2   978
2013-01-13          2   721
2013-01-14          2   736
2013-01-15          2   593
2013-01-16          2   1560
2013-01-17          2   617
2013-01-18          2   1540
2013-01-19          2   458
2013-01-20          2   259
2013-01-21          2   1419
2013-01-22          2   66
2013-01-12          3   551
2013-01-13          3   1643
2013-01-14          3   1244
2013-01-15          3   376
2013-01-16          3   1540
2013-01-17          3   1381
2013-01-18          3   472
2013-01-19          3   194
2013-01-20          3   2043
2013-01-21          3   1345
2013-01-22          3   97

What I need is this data like this

producation_date    shift_1_net shift_2_net shift_3_net
2013-01-12          810         2078            1060
2013-01-13          344         1839            922
2013-01-14          1720            1771            1665
2013-01-15          969         61          733
2013-01-16          117         596         1356
2013-01-17          1716            826         55
2013-01-18          1442            463         1597
2013-01-19          763         1895            571
2013-01-20          1633            148         1916
2013-01-21          67          1503            573
2013-01-22          85          763         800

I've been trying array_merge/push/ and chunk but can't get the data correct.

Answer 1

I think this should be what you're looking for:

SELECT
    CONCAT(WEEK(production_date), '-', YEAR(production_date)) weekno,
    SUM(CASE WHEN production_shift = 1 THEN production_net ELSE 0 END) shift_1_net,
    SUM(CASE WHEN production_shift = 2 THEN production_net ELSE 0 END) shift_2_net,
    SUM(CASE WHEN production_shift = 3 THEN production_net ELSE 0 END) shift_3_net,
WHERE production_shift IN(1, 2, 3) AND product_id = 1319 AND
    production_date BETWEEN '2013-01-01' AND '2013-06-30'
GROUP BY weekno
ORDER BY YEAR(production_date), WEEK(production_date)

BTW, I think the ORDER BY returns rows in the same order as yours and is a bit simpler :)