Reorganizar o resultado mysql em um formato esperado para hansontable

Eu vou reorganizar o resultado do mysql no json com um formato a ser usado com hansontable. De acordo com esta questão: Encontre a posição da string e junte-se à linha de outra tabela . Mr.Olaf tem seu talento. Eu tentei fazê-lo sozinho. Sim, novamente – não é trabalho. Aqui está o meu código:

$sql_rCost="select date, substring_index(cost, '-', 1) type_a, case when cost regexp '.*-' then substring_index(substring_index(cost, '-', 2), '-', -1) else '' end type_b, case when cost regexp '.*-.*-' th substring_index(substring_index(cost, '-', 3), '-', -1) else '' end type_c, case when cost regexp '.*-.*-.*-' then substring_index(substring_index(cost, '-', 4), '-', -1) else '' end type_d, case when cost regexp '.*-.*-.*-.*-' then substring_index(substring_index(cost, '-', 5), '-', -1) else '' end type_e from room_rate_cost where rate='2' and hotel='1' and date between '2012-11-01' and '2012-11-31' order by date asc "; $result_rCost=mysql_db_query($dbname,$sql_rCost); while($rec_rCost=mysql_fetch_array($result_rCost)){ $_rCostDBdate=$rec_rCost['date']; $_rCostDBtypea=$rec_rCost['type_a']; $_rCostDBtypeb=$rec_rCost['type_b']; $_rCostDBtypec=$rec_rCost['type_c']; $_rCostDBtyped=$rec_rCost['type_d']; $_rCostDBtypee=$rec_rCost['type_e']; $_rCostDBcost[]=$rec_rCost['cost']; $_array[]=array( $_rCostDBdate,$_rCostDBcost); } echo "{\"data\": [".json_encode($_array)."]}"; 

Depois de executá-lo, os retornos são:

 {"data": [[["2012-11-01",[null]],["2012-11-02",[null,null]],["2012-11-03",[null,null,null]],["2012-11-04",[null,null,null,null]],["2012-11-05",[null,null,null,null,null]],["2012-11-06",[null,null,null,null,null,null]],["2012-11-07",[null,null,null,null,null,null,null]],["2012-11-08",[null,null,null,null,null,null,null,null]],["2012-11-09",[null,null,null,null,null,null,null,null,null]],["2012-11-10",[null,null,null,null,null,null,null,null,null,null]],["2012-11-11",[null,null,null,null,null,null,null,null,null,null,null]],["2012-11-12",[null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-13",[null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-14",[null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-15",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-16",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-17",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-18",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-19",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-20",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-21",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-22",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-23",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-24",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-25",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-26",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-27",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-28",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-29",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]],["2012-11-30",[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]]]]} 

O formato esperado é:

  { "data": [ ["", "type_a", "type_b", "type_c", "type_d", "type_e"], ["2011-11-01", 4700, 5500, 6100, 6600, 7300], ["2011-11-02", 4700, 5500, 6100, 6600, 7300], ["2011-11-03", 4700, 5500, 6100, 6600, 7300], ... ["2011-11-30", 4700, 5500, 6100, 6600, 7300], ] } 

Eu mudaria a criação da matriz de:

 $_array[]=array($_rCostDBdate,$_rCostDBcost); 

para:

 $_array[]=array($_rCostDBdate, $_rCostDBtypea, $_rCostDBtypeb, $_rCostDBtypec, $_rCostDBtyped, $_rCostDBtypee); 

e deixaria para fora a linha:

 $_rCostDBcost[]=$rec_rCost['cost']; 

uma vez que no resultado da consulta, não há nenhum cost coluna.

Se você alterar a consulta para mysql_fetch_array($result_rCost, MYSQL_NUM) , você poderia reduzir o loop para apenas:

 while ($rec_rCost = mysql_fetch_array($result_rCost, MYSQL_NUM)) { $_array[] = $rec_rCost; } 

Atualização : para adicionar a linha de header, basta inserir antes do loop:

 $_array[] = array('', 'type_a', 'type_b', 'type_c', 'type_d', 'type_e');