duanpoqiu0919
duanpoqiu0919
2014-08-14 02:35

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

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:

phpmyadmin output

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • doure8758 doure8758 7年前

    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.

    点赞 评论 复制链接分享

相关推荐