Return MySQL data in JSON format
<p>I am a novice when it comes to working with JSON/PHP. I have been trying to get this to work based off this answer. </p>

<p><a href="https://stackoverflow.com/questions/2467945/how-to-generate-json-file-with-php">How to generate .json file with PHP?</a></p>

<p>I am trying to query a MySQL database to output it in a JSON format without having it write to a new file I.E. file.json since I am pulling dynamic data. I can create a script that creates a json array but,  I need the output in a JSON format. The script I have been working with below from the example link above connects to the DB but, it is not populating with data. It just gives me this output. I added the DB connection check to check if the script was connecting to the DB.</p>

<p>Connected successfully{"streamers":[]} </p>

<p>This is the code I am currently working with. Is there anyone who could tell me what I am missing and could improve on. DB info removed for security reasons. </p>

<pre><code>&lt;?php

header('Content-type:application/json;charset=utf-8');

//Make connection to database
$db=new PDO('mysql:dbname=streamdb;host=localhost;','root','');  

// Check connection
if ($db-&gt;connect_error) {
    die("Connection failed: " . $db-&gt;connect_error);
} 
echo "Connected successfully";

//Prepare the query for analyzing    
$sql=$db-&gt;prepare('select * from maintable'); 

$response = array();
$streamers = array();
$result=mysql_query($sql);
while($sql=mysql_fetch_array($result)) 
{ 
$displayname=$row['DisplayName']; 
$streamkey=$row['StreamKey']; 

$streamers[] = array('DisplayName'=&gt; $displayname, 'StreamKey'=&gt; $streamkey);

} 

$response['streamers'] = $streamers;

echo stripslashes(json_encode($response));

?&gt;
</code></pre>

<p>-Thanks!</p>