I have a table called `number`:

```
number_id  |  number_name
   1            aaaa
   2            bbbb
   3            cccc
   4            dddd
   5            eeee
   6            ffff

```

(it contains only 6 entries)

and also I have a table called `texts`:

```
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.

```

(it contains a lot of entries).

Now, when I'm doing a query like this:

```
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

```

it returns me the number of texts on each day from the past 100 days on each place with number\_id. The result looks like this:

[![enter image description here](https://i.stack.imgur.com/E0Mfd.png)](https://i.stack.imgur.com/E0Mfd.png)

And when I encode it to json, I get:

```
[{"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"},

```

I want to write a different query, or change the existing one. Is there a way of writing a query that will return me this data in a following way:

```
[{"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"}] 

```

Basically each row should have the information about `days_ago` and the value `num_texts` for each one of 6 `number_id`'s. I need to limit this query for only past 7 days, so I know I can modify it here:

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

```

and change 100 to 7, but is there a possibility of getting somehow the rest of the information in this specific format?

Oh and one more thing - I'm invoking this query from php, so if it's easier to parse it somehow in php and return as a json that's also an option.

Thanks a lot for any hints guys!

=========
EDIT:

As @AlexBlex pointed out - the json structure proposed by me might be invalid, because there are the same key names used several times. To help you understand my question better - <http://thevectorlab.net/flatlab/morris.html> here're examples of morris js charts. I want to produce the chart as the one called `Quarterly Apple iOS device unit sales` in that example. On horizontal line I want the dates (`days ago`), and on vertical line I need num\_texts. I want to have 6 lines, each line for different number\_id.