重新排列mysql结果以hansontable的预期格式 [英] Re-arrange mysql result in an expected format for hansontable
本文介绍了重新排列mysql结果以hansontable的预期格式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我将用hansontable使用的格式将mysql结果重新排列为json. 根据此问题:找到字符串位置并加入另一个表的行.奥拉夫先生很有才华.我已经尝试过自己做.是的-这是行不通的.这是我的代码:
I'm going to re-arrange mysql result into json with a format to use with hansontable. According to this issue : Find string position and join another table's row. Mr.Olaf has his talent. I've tried to do it on my own. Yeah again - it's not work. Here's my code:
$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)."]}";
执行后返回:
{"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]]]]}
预期格式为:
{
"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],
]
}
推荐答案
我将从以下位置更改数组创建:
I would change the array creation from:
$_array[]=array($_rCostDBdate,$_rCostDBcost);
收件人:
$_array[]=array($_rCostDBdate, $_rCostDBtypea, $_rCostDBtypeb, $_rCostDBtypec,
$_rCostDBtyped, $_rCostDBtypee);
，将省略该行:
$_rCostDBcost[]=$rec_rCost['cost'];
由于查询结果中没有名为cost的列.
since in the query result, there is no column named cost.
如果将查询更改为mysql_fetch_array($result_rCost, MYSQL_NUM)，则可以将循环减少为:
If you change the query to mysql_fetch_array($result_rCost, MYSQL_NUM), you could reduce the loop to just:
while ($rec_rCost = mysql_fetch_array($result_rCost, MYSQL_NUM)) {
$_array[] = $rec_rCost;
}
更新: 要添加标题行，只需在循环之前插入:
Update: To add the header line, just insert before the loop:
$_array[] = array('', 'type_a', 'type_b', 'type_c', 'type_d', 'type_e');
这篇关于重新排列mysql结果以hansontable的预期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
