I'm trying to build a multidimensional array in JSON using data from MySQL. I need my JSON to looks like this:
[
{
"name": "test name",
"dates": {
"Feb 26 2019": 2,
"Feb 27 2019": 5,
"Feb 28 2019": 8
}
},
{
"name": "test name 2",
"dates": {
"Feb 22 2019": 2,
"Feb 24 2019": 5,
"Feb 28 2019": 8
}
}
]
The dates are how many times the name appears in the MySQL table. For example, "test name" shows up 2 times with the date of "Feb 26 2019".
Here is my php:
$query = $wpdb->get_results( "SELECT date, count(date) AS `count` FROM $table_name group by date" );
foreach ($query as $row) {
$valid_date = date( 'M d Y', strtotime($row->date));
$data[] = array('date' => $valid_date, 'count' => $row->count);
}
$query1 = $wpdb->get_results( "SELECT DISTINCT cuname FROM $table_name ORDER BY cuname" );
foreach ($query1 as $row) {
$names[] = array('name' => $row->cuname, 'date' => $data);
}
print json_encode($names);
Here is the JSON that is getting returned:
[
{
"name":"test name",
"date":[
{
"date":"Feb 22 2019",
"count":"9"
},
{
"date":"Feb 23 2019",
"count":"14"
},
{
"date":"Feb 24 2019",
"count":"9"
},
{
"date":"Feb 25 2019",
"count":"7"
},
{
"date":"Feb 26 2019",
"count":"1"
}
]
},
{
"name":"test name 2",
"date":[
{
"date":"Feb 22 2019",
"count":"9"
},
{
"date":"Feb 23 2019",
"count":"14"
},
{
"date":"Feb 24 2019",
"count":"9"
},
{
"date":"Feb 25 2019",
"count":"7"
},
{
"date":"Feb 26 2019",
"count":"1"
}
]
}
]
The issue is that all dates and the number of times the dates appear in the table are showing up for each "name". I only need the dates for how many times each name appears.