格式化MYSQL的PHP​​输出以获取morris.js图表​​数据

Question

I have the following query for retrieving data in my MYSQL database:

SELECT a.PARNT_CIVSTATUS,count(a.PARNT_CIVSTATUS) from tbl_parnt a left join tbl_intrvw b 
on a.QN_NUMBR=b.QN_NUMBR left join tbl_barangay c on b.ZONE_NUM=c.BRGY_ZONE_NUM 
group by a.PARNT_CIVSTATUS

This is the output from phpmyadmin:

And this is the output using json_encode in PHP:

[
{"0":"L","PARNT_CIVSTATUS":"L","1":"14","count(a.PARNT_CIVSTATUS)":"14"},
{"0":"LSP","PARNT_CIVSTATUS":"LSP","1":"9","count(a.PARNT_CIVSTATUS)":"9"},
{"0":"M","PARNT_CIVSTATUS":"M","1":"4386","count(a.PARNT_CIVSTATUS)":"4386"},
{"0":"N","PARNT_CIVSTATUS":"N","1":"45","count(a.PARNT_CIVSTATUS)":"45"},
{"0":"NON","PARNT_CIVSTATUS":"NON","1":"1","count(a.PARNT_CIVSTATUS)":"1"},
{"0":"O","PARNT_CIVSTATUS":"O","1":"12","count(a.PARNT_CIVSTATUS)":"12"},
{"0":"S","PARNT_CIVSTATUS":"S","1":"681","count(a.PARNT_CIVSTATUS)":"681"},
{"0":"SGP","PARNT_CIVSTATUS":"SGP","1":"143","count(a.PARNT_CIVSTATUS)":"143"},
{"0":"SP","PARNT_CIVSTATUS":"SP","1":"148","count(a.PARNT_CIVSTATUS)":"148"},
{"0":"SPG","PARNT_CIVSTATUS":"SPG","1":"12","count(a.PARNT_CIVSTATUS)":"12"},
{"0":"W","PARNT_CIVSTATUS":"W","1":"239","count(a.PARNT_CIVSTATUS)":"239"},
{"0":"WGW","PARNT_CIVSTATUS":"WGW","1":"1","count(a.PARNT_CIVSTATUS)":"1"},
{"0":"WW","PARNT_CIVSTATUS":"WW","1":"2","count(a.PARNT_CIVSTATUS)":"2"}
]

I don't know how to format it like this:

{ y: 'L', a: 10 },
{ y: 'LSP', a: 75 },
{ y: 'M', a: 50 },
{ y: 'N', a: 75 },
{ y: 'SGP', a: 50 },
{ y: 'SP', a: 75 },
{ y: 'W', a: 57 }

It's for a morris.js bar chart data. How do I format it this way?

Answer 1

This will require modification in more than just your sql, but it is easily possible; firstly, using the AS keyword, you can create an alias for the columns which you reference:

SELECT a.PARNT_CIVSTATUS AS 'y', count(a.PARNT_CIVSTATUS) AS 'a' 
FROM tbl_parnt a
LEFT JOIN tbl_intrvw b ON a.QN_NUMBR=b.QN_NUMBR 
LEFT JOIN tbl_barangay c ON b.ZONE_NUM=c.BRGY_ZONE_NUM 
GROUP BY a.PARNT_CIVSTATUS

The above, assuming I did things correctly, should give you this output.

However, that still leaves the php side of things. Given you haven't posted your php, I'm going to address this in the two ways I know how; deprecated mysql_* functions, and PDO:

Firstly, the deprecated functions. If you're using these, I highly recommend against doing so, and changing to either PDO or mysqli; anyway, to the code!

$arr = array();
while ($row = mysql_fetch_assoc($result)) {
    $arr[] = $row;
}

The above uses mysql_fetch_assoc(), and the example enclosed within that documentation page. It fetches an associative array of the results, assuming they are like the above SQLFiddle demo I provided, and shoves them into an existing array; this array would of course then be used in json_encode to produce the desired result.

The second method I spoke, PDO, or php Data Objects, is one I stand by; I'm not going to get into a complete demo here, as that's out of the scope of this answer, but will replicate the above code in PDO:

$arr = $stmt->fetchAll(PDO::FETCH_ASSOC);

Here, I use fetchAll() to fetch every row, and use the constant PDO::FETCH_ASSOC to make sure it only gets the column names and their values; otherwise, you get what you had above.

There is of course a third option, or even a fourth. Mysqli, and some custom-made database abstraction layer. I'm not experienced in either, and thus I won't be providing any examples save php.net's mysqli quick start guide.