MySQL to JSON - Combining Two Queries / Formatting

Question

I am using PHP/MySQL to run a query and encode it as JSON, but I'm unsure how to get the JSON into the form that I need.

Here is my PHP:

$myquery1 = "select 'links' as type, source, target, value from table";

$myquery2 = "select 'nodes' as type, name from table2";

$query = mysql_query($myquery1);

if ( ! $query ) {
    echo mysql_error();
    die;
}

$data = array();

for ($x = 0; $x < mysql_num_rows($query); $x++) {
    $data[] = mysql_fetch_assoc($query);
}

//(and again for myquery2)

echo json_encode($data); //not sure how to combine queries here

I would like the JSON to be grouped grouped by "type," like this:

{
 "links": [{"source":"58","target":"john","value":"95"},
           {"source":"60","target":"mark","value":"80"}],
 "nodes": 
          [{"name":"john"}, {"name":"mark"}, {"name":"rose"}]
}

Any help is much appreciated. Thank you!

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Nov 17 '16 at 18:31
You're using quotes around `'links'` and `'types'` which will throw a syntax error — Jay Blanchard, Nov 17 '16 at 18:32
@JayBlanchard it doesn't throw a syntax error (for me) it just makes all values 'links'. Use backtick: `\`` instead of singlequote. — Halcyon, Nov 17 '16 at 18:34
Thank you for both of those comments. For some reason, the quotes haven't thrown an error, but I will look into what you said. However, given the structure above, how can I get to the JSON structure that I'm after? — Phoebe, Nov 17 '16 at 18:34
@Hacyon you are correct, it's a "dummy variable" so that I have something to group on. Open to suggestions. Thank you! — Phoebe, Nov 17 '16 at 18:35

score 3 · Accepted Answer · answered Nov 17 '16 at 18:38

You can do:

$data = array(
    "links" => array(),
    "nodes" => array()
);
..
// for each link
$data["links"][] = mysql_fetch_assoc($query);
..
// for each node
$data["nodes"][] = mysql_fetch_assoc($query);

I think mysql_fetch_assoc adds each column twice, once by it's name and once by it's index so you will want to do some trimming. ie:

$row = mysql_fetch_assoc($query);
$data["links"][] = array(
    "name" => $row["name"],
    .. etc
)

Doing mysql_num_rows($query) in the for-loop condition might be a problem. The value never changes but PHP has to run the function every loop. Cache the value or use:

while (($row = mysql_fetch_assoc($res)) !== false) { .. }

This is exactly what I needed. Thank you, Halcyon! – Phoebe Nov 17 '16 at 19:43 — Phoebe, Nov 17 '16 at 19:43

MySQL to JSON - Combining Two Queries / Formatting

1 Answers1