Creating a JSON object without multiple SQL queries (PHP)

Question

I'm trying to create this kind of JSON object by using MySQL/ PHP.

[ 
{
  "hours":0
  "vulnerability":867
  "file":166
  "virus":59
}, 
{
  "hours":1
  "vulnerability":400
  "file":14
  "virus":40
}, 
]

I'm trying to reduce the amount of queries sent to the server as my db scales quite large. My query returns these results:

SQL data

So while looping through the data I'm getting this kind of JSON returned:

{
  "hours":0
  "vulnerability":867
}, 
{
  "hours":0
  "file":14
}, 
{
  "hours":0
  "virus":59
}, 
]

I would like to create the desired output without using multiple SQL queries in a foreach loop. Full code below-

$query = "SELECT hour(generated_time) as hours, subtype, count(subtype) as y from description group by subtype, hours order by hours asc, y desc";
$result = mysql_query($query) or die(mysql_error());

$output = array();
$data = array();

while ($row = mysql_fetch_assoc($result)) {
    $data["time"] => $row['hours'];
    $data[$row['subtype']] => $row['y'];
    array_push($output, $data);
}

echo json_encode($output);

Please, DO NOT use mysql_query in new applications. It's deprecated, dangerous if you're not careful to properly escape every value you put in your query, and is being removed from future versions of PHP. A modern replacement like PDO is not hard to learn. A guide like PHP The Right Way outlines the recommended best practices. — tadman
– tadman, Commented Apr 4, 2014 at 19:02
I don't really know what you're representing with this data but it seems you are using an EAV model which might be much more trouble than it's worth. — James
– James, Commented Apr 4, 2014 at 19:10
+1 tadman - Thanks for this, this won't be going into production, it's a personal project, but thanks anyway, will look into this. — mason
– mason, Commented Apr 4, 2014 at 20:08

gen_Eric · Accepted Answer · 2014-04-04 18:56:03Z

2

Just run the SQL query once, and loop over the results and put each row in its correct spot.

Something like this:

$output = array();

while ($row = mysql_fetch_assoc($result)) {
    $key = intval($row['hours']);
    if(!isset($output[$key])){
        $output[$key] = array('hours' => $key);
    }
    $output[$key][$row['subtype']] = intval($row['y']);
}

echo json_encode($output);

answered Apr 4, 2014 at 18:56

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

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

Comments

Explosion Pills · Accepted Answer · 2014-04-04 19:00:40Z

1

If you know all of the subtypes ahead of time you can rewrite the query to format the data the way you want it:

SELECT
    hour(generated_time) as hours,
    SUM(subtype = "vulnerability") AS vulnerability,
    SUM(subtype = "file") AS file,
    SUM(subtype = "virus") AS virus
FROM
    description
GROUP BY hours

answered Apr 4, 2014 at 19:00

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

1 Comment

mason Over a year ago

Thanks for this, but the subtypes are chosen on the fly. +1

donlaur · Accepted Answer · 2014-04-04 18:59:48Z

0

It is only one mySQL query. It is put into the associative array and the loop isn't calling more mySQL queries but instead looking through the entry in the associative array in the loop.

answered Apr 4, 2014 at 18:59

donlaur

1,2871 gold badge12 silver badges22 bronze badges

1 Comment

mason Over a year ago

Should this be a comment?

Collectives™ on Stack Overflow

Creating a JSON object without multiple SQL queries (PHP)

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related