I draw a googlechart from json string format. I need to append a second array values to the first array values.
This is the query of the json format.
///sql query list of alumni applicants by race
$sql = 'SELECT lir.race_id, count(lja.application_id) as count,
SUM(CASE WHEN lja.application_status = 0 THEN 1 ELSE 0 END) as pendingcount,
SUM(CASE WHEN lja.application_status = 1 THEN 1 ELSE 0 END) successcount,
SUM(CASE WHEN lja.application_status = 2 THEN 1 ELSE 0 END) rejectedcount, SUM(CASE WHEN lja.application_status = 9 THEN 1 ELSE 0 END) deletedcount,
lir.race_name FROM {local_info_race} lir INNER JOIN
{local_info_userprofile} liu ON lir.race_id = liu.userprofile_raceid
INNER JOIN {cohort_members} cm ON liu.userprofile_userid = cm.userid
INNER JOIN {local_jobs_application} lja ON cm.userid = lja.application_applicantid
INNER JOIN {local_jobs_job} ljj ON lja.application_jobid = ljj.job_id
LEFT JOIN {local_jobs_level} ljl ON ljj.job_levelid = ljl.level_id
LEFT JOIN {local_cohortrole} lc on cm.cohortid = lc.cohortid
LEFT JOIN {role} r ON lc.roleid = r.id WHERE shortname = "graduates"
GROUP BY lir.race_name';
//get the query into record
$data = $DB->get_records_sql($sql);
//put the query into array
$rows = array();
$rows = array_map(function($item) {
return (object) ['c' => [
(object) ['v' => $item->race_name, 'f' => null],
(object) ['v' => intval($item->pendingcount), 'f' => null],
(object) ['v' => intval($item->successcount), 'f' => null],
(object) ['v' => intval($item->rejectedcount), 'f' => null]
]];
}, array_values($data));
//sql query list of alumni applicants by category
$sql2 = 'SELECT liu.userprofile_userid, count(liu.userprofile_userid) as count,
SUM(CASE WHEN lja.application_status = 0 THEN 1 ELSE 0 END) as pendingcount,
SUM(CASE WHEN lja.application_status = 1 THEN 1 ELSE 0 END) successcount,
SUM(CASE WHEN lja.application_status = 2 THEN 1 ELSE 0 END) rejectedcount, SUM(CASE WHEN lja.application_status = 9 THEN 1 ELSE 0 END) deletedcount,
liu.userprofile_raceid, liu.userprofile_race FROM {local_info_userprofile} liu
INNER JOIN {local_jobs_application} lja ON liu.userprofile_userid = lja.application_applicantid
INNER JOIN {local_jobs_job} ljj ON lja.application_jobid = ljj.job_id
LEFT JOIN {local_jobs_category} ljc ON ljj.job_categoryid = ljc.category_id
LEFT JOIN {cohort_members} cm ON lja.application_applicantid = cm.userid
LEFT JOIN {local_cohortrole} lc on cm.cohortid = lc.cohortid
LEFT JOIN {role} r ON lc.roleid = r.id
WHERE r.shortname = "graduates" AND liu.userprofile_raceid = 0';
//get the query into record
$data2 = $DB->get_records_sql($sql2);
//change the label name for undefine race
foreach($data2 as $k => $val) {
if($data2[$k]->{'userprofile_raceid'} == 0) {
$data2[$k]->{'userprofile_race'} = 'Undefined';
}
}
$rows2 = array();
foreach($data2 as $k => $i) {
$rows2[]=(object) ['c' => [
(object) ['v' => $i->userprofile_race, 'f' => null],
(object) ['v' => intval($i->pendingcount), 'f' => null],
(object) ['v' => intval($i->successcount), 'f' => null],
(object) ['v' => intval($i->rejectedcount), 'f' => null]
]];
}
array_push($rows, $rows2);
$cols = [
(object) ['id' => '', 'label' => 'RACE', 'pattern' => '', 'type' => 'string'],
(object) ['id' => '', 'label' => 'Pending', 'pattern' => '', 'type' => 'number'],
(object) ['id' => '', 'label' => 'Success', 'pattern' => '', 'type' => 'number'],
(object) ['id' => '', 'label' => 'Rejected', 'pattern' => '', 'type' => 'number']
];
$returndata = new stdClass;
$returndata->cols = $cols;
$returndata->rows = $rows;
echo json_encode($returndata);
I use array push to combine the array but the json format doesn't acceptable by the googlechart because the second value is attached as an array.
My output is
{"cols":[{"id":"","label":"RACE","pattern":"","type":"string"},
{"id":"","label":"Pending","pattern":"","type":"number"},
{"id":"","label":"Success","pattern":"","type":"number"},
{"id":"","label":"Rejected","pattern":"","type":"number"}],
"rows":[{"c":[{"v":"Malay","f":null},{"v":1,"f":null},{"v":1,"f":null},
{"v":1,"f":null}]},[{"c":[{"v":"Undefined","f":null},{"v":3,"f":null},
{"v":1,"f":null},{"v":1,"f":null}]}]]}
note: the undefined values is a new array.
I want it to be like this so that googlechart can read the json format:
{"cols":[{"id":"","label":"RACE","pattern":"","type":"string"},
{"id":"","label":"Pending","pattern":"","type":"number"},
{"id":"","label":"Success","pattern":"","type":"number"},
{"id":"","label":"Rejected","pattern":"","type":"number"}],
"rows":[{"c":[{"v":"Malay","f":null},{"v":1,"f":null},{"v":1,"f":null},
{"v":1,"f":null}]},{"c":[{"v":"Undefined","f":null},{"v":3,"f":null},
{"v":1,"f":null},{"v":1,"f":null}]}]}
How to append the values of $rows2 into $rows?
you are pushing an object in the array hence the extra square bracket. Try the following:
$rows2 = array('c' => [
(object) ['v' => $i->userprofile_race, 'f' => null],
(object) ['v' => intval($i->pendingcount), 'f' => null],
(object) ['v' => intval($i->successcount), 'f' => null],
(object) ['v' => intval($i->rejectedcount), 'f' => null]
]);
Hope this helps.