How to Convert MySQL Rows into JSON Format in PHP | Coding Cage

How to Convert MySQL Rows into JSON Format in PHP

By
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.
How to Convert MySQL Rows into JSON Format in PHP


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 ...



6 comments:

  1. Replies
    1. Thanks Chandu, i am glad you liked it...

      Delete
  2. How can i delete one user from the table as well as mysql?

    ReplyDelete
  3. hi thanks love your tutorial really helped me until I saw an android app with this URL https://www.reddit.com/.json?& I got confuse this is the out put really need your help

    {"kind": "Listing", "data": {"modhash": "", "children": [{"kind": "t3", "data": {"domain": "bbc.com", "banned_by": null, "media_embed": {}, "subreddit": "worldnews", "selftext_html": null, "selftext": "", "likes": null, "suggested_sort": null, "user_reports": [], "secure_media": null, "link_flair_text": "Possible nuke test", "id": "51uqma", "gilded": 0, "archived": false, "clicked": false, "report_reasons": null, "author": "MAVERICK910", "media": null, "name": "t3_51uqma", "score": 6111, "approved_by": null, "over_18": false, "hidden": false, "preview": {"images": [{"source": {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?s=2a870b97e45633ded1bf3986a3d5d383", "width": 1024, "height": 576}, "resolutions": [{"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=108&s=b078d7b8bb082b3bd59fa7960ef028c2", "width": 108, "height": 60}, {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=216&s=6abaaa4389a2e021d4558c516e7eb1fc", "width": 216, "height": 121}, {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=320&s=c0fb905b191b85fa5c7f673d79ea0f6e", "width": 320, "height": 180}, {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=640&s=0b3ca28121df90a50ee746e1df011f89", "width": 640, "height": 360}, {"url": "https://i.redditmedia.com/XMXfq4OZA_j1Om7W52K0v6qnhJ3XJnE4BTvJ2uGzLSU.jpg?fit=crop&crop=faces%2Centropy&arh=2&w=960&s=739496920de0b0066b7657bc4927946e", "width": 960, "height": 540}], "variants": {}, "id": "bKGRkfe76SRJo_3UQqDNrETdfz2IR6Jp6DgaFK8tG5I"}]}, "thumbnail": "default", "subreddit_id": "t5_2qh13", "edited": false, "link_flair_css_class": "normal", "author_flair_css_class": null, "downs": 0, "mod_reports": [], "secure_media_embed": {}, "saved": false, "removal_reason": null, "post_hint": "link", "is_self": false, "hide_score": false, "permalink": "/r/worldnews/comments/51uqma/large_quake_detected_in_north_korea_bbc_news/", "locked": false, "stickied": false, "created": 1473411649.0, "url": "http://www.bbc.com/news/world-asia-37314927", "author_flair_text": null, "quarantine": false, "title": "Large quake detected in North Korea - BBC News", "created_utc": 1473382849.0, "ups": 6111, "num_comments": 1850, "visited": false, "num_reports": null, "distinguished": null}}

    ReplyDelete
  4. sorry i deleted some in order to make it postable

    ReplyDelete
  5. how can i create one like this

    {"kind": "Listing", "data": {"modhash": "", "children": [


    {"kind": "t3", "data": {"id": "51xo9m", "gilded": 0, "archived": false, "clicked": false, "report_reasons": null, "author": "The_Fassbender", "media": null, "name": "t3_51xo9m", "score": 5862, "approved_by": null, "over_18": false, "hidden": false, "thumbnail": "http://b.thumbs.redditmedia.com/vibty-efWlK4ARYtyYzERpppUg0mWa0JThxa9NFwt5Q.jpg", "subreddit_id": "t5_2qqjc", "edited": false, "link_flair_css_class": null, "author_flair_css_class": null, "downs": 0, "mod_reports": [], "secure_media_embed": {}, "saved": false, "removal_reason": null, "post_hint": "link", "is_self": false, "hide_score": false,

    "permalink": "/r/todayilearned/comments/51xo9m/til_when_nicolas_cage_was_sleeping_at_his_home_he/", "locked": false, "stickied": false, "created": 1473460125.0, "url": "http://newsfeed.time.com/2011/09/16/so-this-apparently-happened-nicolas-cage-awoken-by-naked-fudgesicle-eating-intruder/", "author_flair_text": null, "quarantine": false, "title": "TIL when Nicolas Cage was sleeping at his home, he was awoken by a presence. Cage awoke to find a naked man before his eyes, eating a fudgesicle and wearing his jacket. \"It sounds funny\" Cage said, \"but it was horrifying\". Cage used \"Verbal Judo\" to get the man to leave. Cage now lives on an island.", "created_utc": 1473431325.0, "ups": 5862, "num_comments": 1741, "visited": false, "num_reports": null, "distinguished": null}}] }}

    ReplyDelete