Generate JSON with PHP from MySQL DB?

[Texan78]

Hello, 

 

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. 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 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.

Connected successfully{"streamers":[]}

 

 

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.

<?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->connect_error) {
die("Connection failed: " . $db->connect_error);
}
echo "Connected successfully";

//Prepare the query for analyzing 
$sql=$db->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'=> $displayname, 'StreamKey'=> $streamkey);

}

$response['streamers'] = $streamers;

echo stripslashes(json_encode($response));

?>

-Thanks!

[benanamen]

Probably because you are mixing PDO with obsolete Mysql code. 

 

 

Start at this tutorial and learn what is there before you try to move on.

 

https://phpdelusions.net/pdo

Edited October 15, 2016 by benanamen

[Texan78]

I got it sorted with this solution and seems to work great and is lightweight. 

<?php
$pdo = new PDO('mysql:dbname=_streamdb;host=localhost;','root','password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
$result = $pdo->query('SELECT DisplayName, StreamKey, StreamURL, gpsStatus, UserLat, UserLon, UserHeading, UserLocation, UserLocation, streamStatus, CurrentViewers, TimeStamp FROM maintable');
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
header('Content-Type: application/json;charset=utf-8');
echo json_encode(['streamers' => $rows],
        JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK);

?>