Converting a mysql query to a JSON object using php?

Question

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();

?>

But I get this output:

[{"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}

Any ideas?

Please explain in detail. What does 3,4,5 stands for??

Kamall A Joshi
– Kamall A Joshi

2012-04-06 15:21:37 +00:00
Commented Apr 6, 2012 at 15:21 — Kamall A Joshi
– Kamall A Joshi, Commented Apr 6, 2012 at 15:21

xdazz · Accepted Answer · 2012-04-06 15:11:13Z

2

It should be:

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

answered Apr 6, 2012 at 15:11

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kamall A Joshi Over a year ago

i guess eoin is not looking to accept $r['type']. May be he wants to add 3,4,5,etc. not 121,130,etc.(i.e. type), if i am not wrong.

eoin Over a year ago

Hey that's brilliant, thanks a million. One more thing, is there anyway to easily make the value into an integer i.e. to get rid of the double quotes around the values. I am using this script to pass the sql values into the javascript based d3 framework.

xdazz Over a year ago

@eoin just use type cast (int)$r['type'];

eoin Over a year ago

thanks xdazz, i actually just got it with intval($r['type']). I've been going around in circles for hours. Thanks so much.

chapkom · Accepted Answer · 2012-04-06 15:08:44Z

0

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

Should be

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

answered Apr 6, 2012 at 15:08

chapkom

541 silver badge3 bronze badges

2 Comments

eoin Over a year ago

that brings me closer alright but not quite what I need:[["Carlow","121"],["Cavan","130"],["Clare","112"],["Cork","833"],["Donegal","264"],["Dublin","2457"],["Galway","287"],["Kerry","227"],["Kildare","300"],["Kilkenny","139"],["Laois","123"],["Leitrim","39"],["Limerick","370"],["Longford","85"],["Louth","257"],["Mayo","231"],["Meath","268"],["Monaghan","136"],["Offaly","97"],["Roscommon","115"],["Sligo","113"],["Tipperary","249"],["Waterford","205"],["Westmeath","118"],["Wexford","246"],["Wicklow","235"]]

eoin Over a year ago

Is it possible to get rid of the sqaure brackets, have everything between 1 pair of curly brackets and have double quotes around the county not the the value?

Collectives™ on Stack Overflow

Converting a mysql query to a JSON object using php?

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related