How to Convert MySQL Rows into JSON Format in PHP

Today in this tutorial we have a simple, useful and small piece of code which is "Convert MySQL Rows into JSON Format using PHP", Well JSON is a data exchange format between web/mobile applications and it can easily convert data into plain text in human readable format. JSON is a language-independent data interchanging format. you can also check your JSON Format is valid or not using json validator tool called JSON Lint, and it can easily work with all types of languages,so let's convert to json format.

Download Script

Read Also : How to Parse JSON Data using jQuery Ajax into HTML

Here is the sample MySQL Dump data which are used in this tutorial, run the following sql query in your PHPMyAdmin it will create table with several records.

CREATE TABLE IF NOT EXISTS `tbl_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(25) NOT NULL,
  `last_name` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `tbl_users`
--

INSERT INTO `tbl_users` (`id`, `first_name`, `last_name`) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Doe'),
(3, 'John', 'Cena'),
(4, 'Dwayne', 'Johnson');

ok, now make a connection to mysql database using PDO extension here is the code

<?php
 
 $DBhost = "localhost";
 $DBuser = "root";
 $DBpass = "";
 $DBname = "dbjson";
 
 try{
  
  $DBcon = new PDO("mysql:host=$DBhost;dbname=$DBname",$DBuser,$DBpass);
  $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
 }catch(PDOException $ex){
  
  die($ex->getMessage());
 }

Select Records From MySQL

to convert mysql records first of all fetch all records from the users table .

$query = "SELECT * FROM tbl_users";
 
$stmt = $DBcon->prepare($query);
$stmt->execute();

Convert MySQL Rows to array

Here is the code to convert mysql results to an array

$userData = array();

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
  
      $userData['AllUsers'][] = $row;
 
}

Convert Array to JSON String

In PHP there's a function called json_encode();. is used to convert array to JSON string. just add array name created above and put in this function like this.

echo json_encode($userData);

Here we got JSON Sting

this is the final JSON String we have converted MySQL to Array - Array to JSON, contains root element Allusers

{
    "AllUsers": [
        {
            "id": "1",
            "first_name": "John",
            "last_name": "Doe"
        },
        {
            "id": "2",
            "first_name": "Jane",
            "last_name": "Doe"
        },
        {
            "id": "3",
            "first_name": "John",
            "last_name": "Cena"
        },
        {
            "id": "4",
            "first_name": "Dwayne",
            "last_name": "Johnson"
        }
    ]
}

Final Code

here is the final code of above all small pieces..

<?php

 require_once 'dbconfig.php';
 
 $query = "SELECT * FROM tbl_users";
 
 $stmt = $DBcon->prepare($query);
 $stmt->execute();
 
 $userData = array();
 
 while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
  
  $userData['AllUsers'][] = $row;
 }
 
 echo json_encode($userData);
?>

Hope you like it, actually recently i had a work on JSON Apis, so i have decided share a small piece of json code. and guys please keep visiting and do share it with your dev friends
that's it ...