Return MySQL data in JSON format

Question

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!

• Derek Pollard: You cannot echo JSON and plain text while sending a JSON header. It is malformed JSON.
• Derek Pollard: And don't use stripslashes, json_encode() will handle that for you.
• Louis Durand: Looks like your "$row" variable is not initialized anywhere. It should probably be: while($row=...)

Answer 1

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.

• Texan78: Thank you for that suggestion. I gave it a try and it appears to be working. Can you tell me if this looks to be formatted correctly? livestormsmedia.com/livestormchasing/api/streaming/json.php There is a couple of small adjustments I need as I only need to display certain information. With your solution how can I only pull certain data?
• Josef Kufner: It looks fine. To pull only certain data enumerate them in SQL query (updated the answer).
• Texan78: Thank you so much for you guidance and helpful explanations. Seems to be working great!

Answer 2

So as I said in a comment, the problem is probably that "$row" is not initialized. It should probably be:

while($row=mysql_fetch_array($result))

Also, you used mysql_ functions which are not compatible with PDO. You should do your request using $db->query.

As you asked for suggestions, I may give you a trick that I use.

I think that it's a lot of code for just retrieving a table that is basically the content of the result of your query (in $result). And I guess you have similar code for almost every request. So what I do in general is that I build a generic function called extractResult that directly makes an array of lines from the result of a query.

Here is the function:

/**
* Extract a php array from the result of a db query.
* This result can be directly parsed in JSON for instance.
*/
function extractResult($res) {
 $arr = array();
 while($line = $res->fetch()) {
 $count = count($line)/2;
 for ($i=0; $i<$count; $i++) {
 unset($line[$i]);
 }
 array_push($arr, $line);
 }
 return $arr;
}

Note: the for loop with the "unset" is made to remove the entries with digits. What's returned by a fetch is something like this:

Array("DisplayName" => "Loulou", 0 =>"Loulou", "StreamKey" => 12, 1 => 12)

So it removes the numerical duplicates.

So you could use it like this and your code becomes way lighter:

<?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";
$result=$db->query('select display_name AS DisplayName, stream_key AS StreamKey from maintable');
$response = array();
$response['streamers'] = extractResult($result);
echo json_encode($response);
?>

Note that you have to specify the columns name that you want directly in the request! :)

Don't know if it's nteresting, but I always use this trick so building JSON from DB is so much easier and lighter. :) Good luck.

Answer 3

Here you can check the working code

<?php
$con=mysqli_connect($servername,$username,$password,$databasename);
if (mysqli_connect_errno())
{
echo "Connection Error" . mysqli_connect_error();
}
$query = "SELECT * FROM TableName";
$result = mysqli_query($con,$query);
$querydata = array();
while($data = mysqli_fetch_array($result)) {
$querydata[] = $data;
}
echo json_encode($querydata);
mysqli_close($con);
?>