How can I rewrite my sql query so that it returns the values in a specific format (using mysql or php)?

Question

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:

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.

Answer 1

Assuming your number has these 6 fixed values that will never change, it is possible by linking 6 copies of the texts table (or create 6 views).

But it will probably be a lot cleaner if you do it in php by tracking when days_ago changes and adding a new JSON record. You will also have to make sure no number_id is skipped and add a zero value for num_texts for any skipped.

Addition, sample pseudo-code:

$days_ago=0;
while ([read_records]) {
    if (record['days_ago']!=$days_ago {
        $days_ago!=0 [Close line]
        $days_ago=record['days_ago'];
        [Add new line]
        [Add days_ago field]  }
    [Add number_id and num_texts fields]
}
[Close line]

Second Edit, solution with views:

If view_1 is the view I described below and view_2 the view for number_id 2, you would link them like this:

SELECT view_1.days_ago, view_1.num_texts AS num_texts1, view_2.num_texts AS num_texts2
FROM view_1 INNER JOIN view_2 ON view_1.days_ago=view_2.days_ago

This willo only work properly if you have entries for all number_id for every days_ago, otherwise you will have to create a view of just the days_ago and use LEFT/RIGHT joins to join the number_id views.

Answer 2

You can do this with a pivot table:

SELECT
   days_ago,
   SUM(IF(number_id = 1, num_texts, 0)) as num_1_texts,
   SUM(IF(number_id = 2, num_texts, 0)) as num_2_texts,
   SUM(IF(number_id = 3, num_texts, 0)) as num_3_texts,
   SUM(IF(number_id = 4, num_texts, 0)) as num_4_texts,
   SUM(IF(number_id = 5, num_texts, 0)) as num_5_texts,
   SUM(IF(number_id = 6, num_texts, 0)) as num_6_texts
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)

GROUP BY days_ago;

If the amount of numbers is arbitrary, you could generate the field list with whatever language you are using.

in php:

$selectFields = '';
$numbers = 10;
$separator = ''
for($i=1; $i<=$numbers; $i++) {
    $selectFields .= $separator . "SUM(IF(number_id = {$i}, num_texts, 0)) as num_{$i}_texts";
    $separator = ',';
}

$query = "
    SELECT
    days_ago,
    $selectFields
    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)
    ORDER BY days_ago      
    GROUP BY days_ago;
";

Of course as someone pointed out, you have duplicate ids in your JSON, but it could easily be represented as:

[{days_ago: "1", numbers: [{"id" : "1", "num_texts" : "12"},...{"id": "n", "num_texts": "999"}],
{days_ago: "2", numbers: [{"id" : "1", "num_texts" : "12"},...{"id": "n", "num_texts": "999"}],
...

Or whatever format Morris.js is expecting. You would have the days on one axis and num_texts on the other. You may also need to handle the case where there was no activity on a specific day.