Texan78 Texan78 - 1 year ago 63
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.



//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();

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


$response['streamers'] = $streamers;

echo stripslashes(json_encode($response));



Answer Source

First, use PDO only. No mysql_* functions.

Then your code should look like this:

$pdo = new PDO('mysql:host=...', 'username', 'password', [
$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],

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.