How to return data fetched from MySQL into a php file as JSON?

Question

I have to return data fetched from MySQL table into a php file as JSON. Here is my code to connect to mysql & get data from it. How could now I return it as JSON.

<?php
    $username = "user";
    $password = "********";
    $hostname = "localhost";    
    $dbh = mysql_connect($hostname, $username, $password) 
        or die("Unable to connect to MySQL");
    print "Connected to MySQL<br>";
    $selected = mysql_select_db("spec",$dbh) 
        or die("Could not select first_test");
    //$rows = array();  

    $query = "SELECT * FROM user_spec"; 
    $result=mysql_query($query);

    //mysql_close($dbh);
    ?>

Under is full stack that I have to implement. For step 3, I am rendering the list dynamically using user inputs though its not using any engine but directly using input values so I have to see it how to do once I get JSON data. I put the stack so that you people can kindly see it what I have to do when possibly helping me.

1. the user load an HTML page
2. the page make an ajax call and get the options as a JSON(either it exists already in the database, or a new option set is generated)
3. the json is rendered using a JS templating engine (PURE in our case)
4. the user change something
5. the same JSON is modified and sent by a POST to the server
6. the server read that JSON and store it in the database(you would write the data to your file). And then go back to the step 4, to wait for another user change.

Answer 1

Given that only one user_spec row is returned you can use the built in json_encode function:

<?php
$username = "user";
$password = "********";
$hostname = "localhost";    
$dbh = mysql_connect($hostname, $username, $password) 
    or die("Unable to connect to MySQL");

//print "Connected to MySQL<br>";

$selected = mysql_select_db("spec",$dbh) 
    or die("Could not select first_test");

$query = "SELECT * FROM user_spec"; 
$result=mysql_query($query);

echo json_encode(mysql_fetch_assoc($result));

?>

Should do the trick.

Even if you are using an older version of PHP, you can find a suitable function in the user comments at the json_encode PHP Manual page to use in it's place.

Answer 2

Unless you're like me using a painfully old version of Symfony (for your sake, I hope not!) your answer is json_encode Zend and CodeIgniter have similar other easy methods.

If you're not lucky enough to have access to that, you can build it manually via foreach loop.

You can check your JSON strings using JSONlint

Answer 3

I think what your looking for is json_encode