重写Php访问数据库部分,如何重写我的SQL查询,以便它以特定的格式返回值(使用mysql或php)？...

我有一个叫做数字的表：

number_id | number_name

1 aaaa

2 bbbb

3 cccc

4 dddd

5 eeee

6 ffff

(它只包含6个条目)

我还有一个叫做文本的表格：

text_id | start_time | number_id | text_content

1 some date1 | 2 | blabla1

2 some date2 | 1 | blabla2

3 some date3 | 2 | blabla3

4 some date4 | 3 | blabla4

5 some date5 | 4 | blabla5

6 some date6 | 6 | blabla6

etc.

(它包含很多条目).

现在,当我正在进行这样的查询时：

SELECT

*

FROM

(SELECT

DATEDIFF(now(),

start_time) AS days_ago,

number_id,

COUNT(text_id) AS num_texts

FROM

TEXTS

WHERE

start_time BETWEEN DATE_SUB(NOW(), INTERVAL 100 DAY) AND NOW()

GROUP BY

DATE(start_time),

number_id) AS temp

它使用number_id返回每个地方过去100天每天的文本数量.结果如下：

当我将它编码为json时,我得到：

[{"days_ago":"19","number_id":"1","num_texts":"179"},

{"days_ago":"19","number_id":"5","num_texts":"1"},

{"days_ago":"18","number_id":"1","num_texts":"61"},

{"days_ago":"18","number_id":"2","num_texts":"1"},

{"days_ago":"18","number_id":"5","num_texts":"1"},

{"days_ago":"18","number_id":"6","num_texts":"3"},

{"days_ago":"17","number_id":"3","num_texts":"1"},

{"days_ago":"8","number_id":"1","num_texts":"2"},

{"days_ago":"8","number_id":"2","num_texts":"2"},

{"days_ago":"7","number_id":"4","num_texts":"1"},

我想写一个不同的查询,或更改现有的查询.有没有办法编写一个查询,将以下列方式返回我的数据：

[{"days_ago": "7", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},

{"days_ago": "6", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},

{"days_ago": "5", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},

{"days_ago": "4", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},

{"days_ago": "3", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},

{"days_ago": "2", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},

{"days_ago": "1", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"}]

基本上每行应该有6个number_id的每一个的days_ago和值num_texts的信息.我需要限制此查询仅过去7天,所以我知道我可以在这里修改它：

BETWEEN DATE_SUB(NOW(), INTERVAL 100 DAY) AND NOW()

并将100改为7,但有可能以某种方式得到这种特定格式的其他信息吗？

哦还有一件事 – 我正在从php调用这个查询,所以如果它更容易在php中以某种方式解析它并返回作为json,这也是一个选项.

非常感谢任何提示家伙！

=========

编辑：

正如@AlexBlex指出的那样 – 我提出的json结构可能无效,因为有多次使用相同的键名.为了帮助您更好地理解我的问题 – http://thevectorlab.net/flatlab/morris.html这里是morris js图表的例子.我希望在该示例中将图表生成为名为Quarterly Apple iOS设备单元销售的图表.在水平线上我想要日期(几天前),在垂直线上我需要num_texts.我希望有6行,每行代表不同的number_id.