Texan78 Texan78 - 1 year ago 105
JSON Question

Return MySQL data in JSON format

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.

How to generate .json file with PHP?

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.

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!

Answer Source

First, use PDO only. No mysql_* functions.

Then your code should look like this:

$pdo = new PDO('mysql:host=...', 'username', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
$result = $pdo->query('SELECT DisplayName, StreamKey FROM ...');
$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);

The PDO::ERRMODE_EXCEPTION sets PDO to throw all errors as exceptions, so they can be handled in an easy and consistent way.

The PDO::FETCH_ASSOC sets fetchAll() to return rows as arrays where column names are used as array keys.

The json_encode() will take care of producing a valid JSON output. Since you are not embedding JSON into HTML, there is no need for escaped slashes and we will make it nicely indented for easier debugging.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download