How to Generate JSON Data from a MySQL Database using PHP

What is JSON

  1. JSON stands for JavaScript Object Notation and subset of JavaScript Programming Language.

  2. JSON is lightweight data interchange format.

  3. JSON is language independent and familiar to any programming language including C, C++, JAVA, C#, PHP.

  4. JSON is "self-describing" and easy to understand.
Most of the social networking application apis like Facebook,twitter, geolocation service providers like google, REST-SOAP API producers and around hundreds of similar technologies/frameworks use JSON as data exchange format.

Structure of JSON
  1. JSON Object: A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.

    An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).

    Ex:- {"name":"Divya", "Stream":"CS"}

  2. JSON Array: An ordered list of values. In most languages,this is realized as an array, vector, list, or sequence.

    An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).

    Ex: {"Divya", "CS"}.

Now here, I am explanning that how to fetch data from MySQL database and show in JSON format:

Step 1: Create register table in MySQL database

register table

Step 2: Now write the code in PHP file:

  1. <?php  
  2. $username="root";  
  3. $password="";  
  4. $hostname = "localhost";  
  5. //connection string with database  
  6. $dbhandle = mysql_connect($hostname$username$password)  
  7. or die("Unable to connect to MySQL");  
  8. echo "Connected to MySQL<br>";  
  9. // connect with database  
  10. $selected = mysql_select_db("abc",$dbhandle)  
  11. or die("Could not select examples");  
  12. //query fire  
  13. $result = mysql_query("SELECT * FROM register");  
  14. $json_response = array();  
  15. // fetch data in array format  
  16. while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {  
  17. // Fetch data of Fname Column and store in array of row_array  
  18. $row_array['Fname'] = $row['Fname'];  
  19. //push the values in the array  
  20. array_push($json_response,$row_array);  
  21. }  
  22. //  
  23. echo json_encode($json_response);  
  24. ?>  
  25. json_encode() - Returns the JSON representation of a value.  
Step 3: The output is: