Generate JSON with PHP

Question

I've a PHP with far too many query (like 20) and each one has a few echo with var. I would like to generate a JSON with those vars.. in a single JSON File.

Example:

// QUERY 1

$query = "SELECT * FROM $table1 WHERE DateV = '$dateV' AND StatusV = 'WORKING'";

$result = mysql_query($query) or die("Error : $query. " . mysql_error());

$row = mysql_fetch_array($result);

echo '&Tvar=' . $row['num'];
echo '&Tvarname=' . $row['name'];


// QUERY 2

$query = "SELECT * FROM $table2 WHERE DateV = '$dateV' AND StatusV = 'WORKINGFAST'";

$result = mysql_query($query) or die("Error : $query. " . mysql_error());

$row = mysql_fetch_array($result);

echo '&Bvar=' . $row['something'];
echo '&Bvarname=' . $row['something'];

and since I need to load that data, from an Desktop AIR App, every 2 seconds from multiples intances I don't wanna overload the MYSQL Server. Anyway, I believe the best thing I can do, it's to generate a single file and load that one as many times as I need.

Thank you!

does it work if you put all the variables into an array then `json_encode` the array and print it out? why are you printing out a query string? — Alex Andrei, Dec 11 '15 at 12:41
Yeah, I'm asking that since I don't know how to do that. That's why I was trying to explain what I've so far. — CrazyF8ck, Dec 11 '15 at 12:46

Vishal Kamal · Answer 1 · 2015-12-11T12:58:05.030

1

Try this

// QUERY 1

$query = "SELECT * FROM $table1 WHERE DateV = '$dateV' AND StatusV = 'WORKING'";
$arrfinal = array();
$result = mysql_query($query) or die("Error : $query. " . mysql_error());

$row = mysql_fetch_array($result);



$arrfinal['first'] = array('Tvar'=>$row['num'], 'Tvarname'=>$row['name']);
// QUERY 2

$query = "SELECT * FROM $table2 WHERE DateV = '$dateV' AND StatusV = 'WORKINGFAST'";

$result = mysql_query($query) or die("Error : $query. " . mysql_error());

$row = mysql_fetch_array($result);
$arr = array(
            'Bvar' =>$row['something'],
            'Bvarname' => $row['something']
);
$arrfinal['second'] = $arr;
echo json_encode($arrfinal);

edited Dec 11 '15 at 12:58

answered Dec 11 '15 at 12:42

Vishal Kamal

1,104
2
10
35

1

remove the two echo's at the top, leave just the output of `json_encode` so the json string can be consumed – Alex Andrei Dec 11 '15 at 12:47
Yeah, but that would generate multiple arrays, right? If I do that for each Query the last line should be something like echo json_encode($arr, $arr2, $arr3); – CrazyF8ck Dec 11 '15 at 12:49
Then you have to create miltidimention array for this. – Vishal Kamal Dec 11 '15 at 12:51
Sorry I just missed those. – Vishal Kamal Dec 11 '15 at 12:55
Could you elaborate in multidimention array? – CrazyF8ck Dec 11 '15 at 12:56
Oh sorry, didn't noticed that. I'll try it shortly, thanks! – CrazyF8ck Dec 11 '15 at 13:07

score 1 · Accepted Answer · edited May 23 '17 at 11:59

You can add your values to an array and use json_encode(). If you're worrying about overloading your MySQL server, I can say that this query every 2 seconds is not a problem.

It's a good practice to cache results which doesn't change every few calls. So you can take a look at memcached or just use a simple file cache with file_put_contents() and file_get_contents().

You can try it with a code similar to this below:

$jsonFile = "my.json";// path to your json file
$timeToCache = 1 * 60;// cache time in seconds

// see if the file exists and if the file is older than the time we want to cache
if( file_exists( $jsonFile ) && ( ( time() - filemtime( $jsonFile ) ) > $timeToCache ) ) {
    $toJson = array();
    // your first query goes here
    $row = mysql_fetch_array($result);

    $toJson["Tvar"] = $row['num'];
    $toJson["Tvarname"] = $row['name'];

    // your second query goes here
    $row = mysql_fetch_array($result);
    $toJson["Bvar"] = $row['something'];
    $toJson["Bvarname"] = $row['something'];

    // put your json output to some file
    $jsonOut = json_encode( $toJson );
    file_put_contents( $jsonFile , $jsonOut );
} else {
    $jsonOut = file_get_contents( $jsonFile );
}

echo $jsonOut;

And you should also stop using mysql_* functions, because they are deprecated:

Warning This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used

Take a look at Why should I use prepared statements.

Generate JSON with PHP

2 Answers2