php mysql crosstable with variable fields -

php mysql crosstable with variable fields -

i'm struggling making pivot table/cross table. @ end edit inline, first i'd create @ to the lowest degree table.

in table ' tarifs' have id, tarifcode , tarifdescr like:

1, a, overnight 2, p, room 3, v, adult no discount etc.

at point in application fill out start date, end date , applicable tarifcodes value (amount). like:

2012-02-05, 2012-02-09, a:1, p:0, v:2

after submit sql query fills table 'occupacion', exists of id, date, tarifcode, value, like:

1, 2012-02-05, a, 1 2, 2012-02-05, v, 2 3, 2012-02-06, a, 1 4, 2012-02-06, v, 2 5, 2012-02-07, a, 1 6, 2012-02-07, v, 2 7, 2012-02-08, a, 1 8, 2012-02-08, v, 2 9, 2012-02-09, a, 1 10, 2012-02-09, v, 2

here question: how can create query (or view) gives me next output:

-- 2012-02-05 | 2012-02-06 | 2012-02-07 | 2012-02-08 | 2012-02-09 1 1 1 1 1 v 2 2 2 2 2

in of posts related topic values known. in case there no tarifcode used or there new tarifcode created.

at end create in json-style can utilize inline editing in grid. maybe has experience or can point me tutorial?

many in advance helping me out!

roy

if wanted perform using sql, can pivot info in mysql aggregate function , case expression. take date values , convert them columns:

select tarifcode, max(case when date = '2012-02-05' value end) `2012-02-05`, max(case when date = '2012-02-06' value end) `2012-02-06`, max(case when date = '2012-02-07' value end) `2012-02-07`, max(case when date = '2012-02-08' value end) `2012-02-08`, max(case when date = '2012-02-09' value end) `2012-02-09` yourtable grouping tarifcode

see sql fiddle demo

if dates unknown, can utilize prepared statement similar this:

set @sql = null; select group_concat(distinct concat( 'max(case when date = ''', date, ''' value end) `', date, '`' ) ) @sql yourtable; set @sql = concat('select tarifcode, ', @sql, ' yourtable grouping tarifcode'); prepare stmt @sql; execute stmt; deallocate prepare stmt;

see sql fiddle demo. result of both queries is:

| tarifcode | 2012-02-05 | 2012-02-06 | 2012-02-07 | 2012-02-08 | 2012-02-09 | ------------------------------------------------------------------------------ | | 1 | 1 | 1 | 1 | 1 | | v | 2 | 2 | 2 | 2 | 2 |

edit, if want bring together table, can utilize similar this:

select t.tarifcode, max(case when date = '2012-02-05' value end) `2012-02-05`, max(case when date = '2012-02-06' value end) `2012-02-06`, max(case when date = '2012-02-07' value end) `2012-02-07`, max(case when date = '2012-02-08' value end) `2012-02-08`, max(case when date = '2012-02-09' value end) `2012-02-09` tarifs t left bring together yourtable y on t.tarifcode = y.tarifcode grouping t.tarifcode

see sql fiddle demo

php mysql table