使用php将mysql查询转换为JSON对象? [英] Converting a mysql query to a JSON object using php?

问题描述

我正在尝试将mySQL查询转换为JSON对象.这有效:

I am trying to convert a mySQL query into a JSON object. This works:

<?php
// load in mysql server configuration (connection string, user/pw, etc)
include 'mysqlConfig.php';
$year = $_GET['year'];
// connect to the database
@mysql_select_db($dsn) or die( "Unable to select database");

// outputs the db as lines of text.
$result = mysql_query("SELECT COUNTY, COUNT(TYPE) AS 'type'  from data WHERE DATE between '2000-01-01' and '2000-12-31' GROUP BY COUNTY");
$rows = array();

while($r = mysql_fetch_assoc($result)) {
     $rows[] = $r;
}

echo json_encode($rows);
mysql_close();

?>

但我得到以下输出:

[{"COUNTY":"Carlow","type":"121"},{"COUNTY":"Cavan","type":"130"},{"COUNTY":"Clare","type":"112"},{"COUNTY":"Cork","type":"833"},{"COUNTY":"Donegal","type":"264"},{"COUNTY":"Dublin","type":"2457"},{"COUNTY":"Galway","type":"287"},{"COUNTY":"Kerry","type":"227"},{"COUNTY":"Kildare","type":"300"},{"COUNTY":"Kilkenny","type":"139"},{"COUNTY":"Laois","type":"123"},{"COUNTY":"Leitrim","type":"39"},{"COUNTY":"Limerick","type":"370"},{"COUNTY":"Longford","type":"85"},{"COUNTY":"Louth","type":"257"},{"COUNTY":"Mayo","type":"231"},{"COUNTY":"Meath","type":"268"},{"COUNTY":"Monaghan","type":"136"},{"COUNTY":"Offaly","type":"97"},{"COUNTY":"Roscommon","type":"115"},{"COUNTY":"Sligo","type":"113"},{"COUNTY":"Tipperary","type":"249"},{"COUNTY":"Waterford","type":"205"},{"COUNTY":"Westmeath","type":"118"},{"COUNTY":"Wexford","type":"246"},{"COUNTY":"Wicklow","type":"235"}]

这是我正在寻找的格式:

whereas this is the format I am looking for:

{"Carlow":3,"Cavan":4,"Clare":5,"Cork":3,"Donegal":4,"Dublin":5,"Galway":4,"Kerry":5,"Kildare":5,"Kilkenny":12,"Laois":4,"Leitrim":4,"Limerick":4,"Longford":4,"Louth":4,"Mayo":5,"Meath":3,"Monaghan":5,"Offaly":4,"Roscommon":3,"Sligo":3,"Tipperary":4,"Waterford":2,"Westmeath":2,"Wexford":4,"Wicklow":2}

有什么想法吗?

推荐答案

应为:

while($r = mysql_fetch_assoc($result)) {
     $rows[$r['COUNTRY']] = $r['type'];
}

这篇关于使用php将mysql查询转换为JSON对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！