This is a follow up to my previous question How can I rewrite my sql query so that it returns the values in a specific format (using mysql or php)? . I wanted to change my sql query so that I would get a results in a specific way, but one of the users suggested me to leave the query as it is and parse everything in php accordingly.
So to keep the question short, I have an sql query:
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 7 DAY) AND NOW() GROUP BY DATE(start_time),
number_id) AS temp
and it returns me the values like in a form like this:
[{"days_ago":"7","number_id":"1","num_texts":"179"},
{"days_ago":"7","number_id":"5","num_texts":"1"},
{"days_ago":"6","number_id":"1","num_texts":"61"},
{"days_ago":"6","number_id":"2","num_texts":"1"},
{"days_ago":"6","number_id":"5","num_texts":"1"},
{"days_ago":"5","number_id":"6","num_texts":"3"},
{"days_ago":"5","number_id":"3","num_texts":"1"},
{"days_ago":"4","number_id":"1","num_texts":"2"},
{"days_ago":"2","number_id":"2","num_texts":"2"},
{"days_ago":"1","number_id":"4","num_texts":"1"},
Now, I want to parse it in php so that the result looks like this:
[{"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.
One user suggested to build it in php:
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]
and I have no idea how to expand this pseudo-code in php..
I started writing some basic php code, but to be honest I don't know how to proceed based on his suggestions:
$myArray = array();
if ($result = $mysqli->query("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 7 DAY) AND NOW() GROUP BY DATE(start_time),
number_id) AS temp")) {
$days_ago = 0;
while($row = $result->fetch_array(MYSQL_ASSOC)) {
echo $row['days_ago'];
if($row['days_ago'] !=$days_ago){
}
$myArray[] = $row;
}
// echo json_encode($myArray);
}
Could you please help me with that?
The code below should generate JSON close to what you want:
if ($result = $mysqli->query("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 7 DAY) AND NOW() GROUP BY DATE(start_time),
number_id) AS temp")) {
$days_ago = 7;
$data = array();
$num_ids = array(1,2,3,4,5,6); // in case your id's are not sequential. otherwise you can just use a for loop.
// create empty array to fill gaps for days with no activity
for($i=0; $i<=$max_days_ago) {
$data[$i] = array();
foreach($num_ids as $id) {
$data[$i]['days_ago'] = array();
$data[$i]['days_ago'][$id] = array('number_id' => $id, 'num_texts' => 0);
}
}
// Put in the data you DO have
while($row = $result->fetch_array(MYSQL_ASSOC)) {
$data[$row['days_ago']]['days_ago'][$row['number_id'] = array('number_id' => $row['number_id'], 'num_texts' => $row['num_texts']);
}
echo json_encode($data);
}
Should generate JSON similar to this
[{'days_ago': '0', [{'number_id': '1', 'num_texts' : '1'},{'number_id': '2', 'num_texts' : '99'},...]},
{'days_ago': '1', [{'number_id': '1', 'num_texts' : '1'},{'number_id': '2', 'num_texts' : '99'},...]},
{'days_ago': '2', [{'number_id': '1', 'num_texts' : '1'},{'number_id': '2', 'num_texts' : '99'},...]},...]
This was typed up in notepad and is untested - it may contain some bugginess and typos. Your original JSON example contained duplicate array keys, so my output is a little different to your original request.