How to convert mysql database values into JSON data using PHP?

Sometimes we need to convert mysql database in json values due to project requirement. JSON is JavaScript object notation. It’s very preferred over XML now a days because it is light weight, easily readable, manageable for exchanging data across various platforms. Several social networking website like facebook, twitter, Google etc uses json for data exchange.

JSON schema example –

"id": 1,
"name": "test",
"age": 50,
"gender": male

Now, We will see how to convert mysql data to json format by using PHP. Suppose, we have a mysql database with field like id, name, age and gender.

Lets create a php file json_encode.php.

$mysql_db_hostname = "localhost";
$mysql_db_user = "root";
$mysql_db_password = "";
$mysql_db_database = "employee";

$con = @mysqli_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password,

if (!$con) {
 trigger_error('Could not connect to MySQL: ' . mysqli_connect_error());
$user = array();
 $sql = "SELECT * FROM users";
$result = mysqli_query($con, $sql);

while($obj = mysqli_fetch_object($result)) {
$user[] = $obj;
echo '{"users":'.json_encode($user).'}';

Please add PHP start and end tag. You can customize array output as per your JSON structure need. For any issue or problem, drop a comment. Thanks!!

Leave a Reply