[SOLVED] Convert MySQL to JSON

Hello, i have found this code and i think it can be useful to someone to covert mysql to json

<?php
//open connection to mysql db
$connection = mysqli_connect("hostname","username","password","db_employee") or die("Error " . mysqli_error($connection));

//fetch table rows from mysql db
$sql = "select * from tbl_employee";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

//create an array
$emparray = array();
while($row =mysqli_fetch_assoc($result))
{
    $emparray[] = $row;
}
//this line just show the result can be deleted
echo json_encode($emparray);
//write to json file
$fp = fopen('empdata.json', 'w');
fwrite($fp, json_encode($emparray));
fclose($fp);
//close the db connection
mysqli_close($connection);
?>
2 Likes

very fine, but this doesn’t write the file in correct JSON-format ( produces files with square brackets and not curly brackets) … otherwise a very good example (somewhat useful)

With only a small change you can get a correctly formatted json file:

<?php
//open connection to mysql db
$connection = mysqli_connect("hostname","username","password","db_employee") or die("Error " . mysqli_error($connection));

//fetch table rows from mysql db
$sql = "select * from tbl_employee";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

//create an array
$emparray = new stdClass();
$emparray->arr = array();

while($row =mysqli_fetch_assoc($result))
{
    $emparray->arr[] = $row;
}
//this line just show the result can be deleted
echo json_encode($emparray);
//write to json file
$fp = fopen('empdata.json', 'w');
fwrite($fp, json_encode($emparray));
fclose($fp);
//close the db connection
mysqli_close($connection);
?>
2 Likes

Ok, so anyone who drops by this thread, wanting to use PHP/MySQL and JSON in combination … Leonidas just did it! From here on this thread works with https://developer.playcanvas.com/en/tutorials/loading-json/ - unfortunately I don’t have time to elaborate the details in here myself (have been using 18 to 22 hours to get there)… but stick to it (Game.js, Character Data.json [cf the tut above] and the exported index.html renamed to index.php should guide the way for the medium or above coder :slight_smile: )

2 Likes