How to convert MYSQL data to JSON using PHP

Last updated on 06th April 2016

Many web applications expect external data in JSON format, hence converting MySQL data to JSON is something that web developers encounter on a regular basis. JSON (JavaScript Object Notation) is fast becoming the most popular data format in server/browser communication. It is light weight, human readable and easy to generate and parse.

Data from MySQL database can be easily converted into JSON format using PHP. The below example uses Sakila sample database that comes with standard MySQL installation. It fetches the first 3 rows of actor table into an associative array using mysqli_fetch_assoc(). Then the array is encoded into JSON using json_encode.

<?php

// Initialize variable for database credentials
$dbhost = 'hostname';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'sakila';

//Create database connection
  $dblink = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

//Check connection was successful
  if ($dblink->connect_errno) {
     printf("Failed to connect to database");
     exit();
  }

//Fetch 3 rows from actor table
  $result = $dblink->query("SELECT * FROM actor LIMIT 3");

//Initialize array variable
  $dbdata = array();

//Fetch into associative array
  while ( $row = $result->fetch_assoc())  {
	$dbdata[]=$row;
  }

//Print array in JSON format
 echo json_encode($dbdata);
 
?>

Result:


[
	{
	"actor_id":"1",
	"first_name":"PENELOPE",
	"last_name":"GUINESS",
	"last_update":"2006-02-15 04:34:33"
	},
	{	
	"actor_id":"2",
	"first_name":"NICK",
	"last_name":"WAHLBERG",
	"last_update":"2006-02-15 04:34:33"
	},
	{
	"actor_id":"3",
	"first_name":"ED",
	"last_name":"CHASE",
	"last_update":"2006-02-15 04:34:33"
	}
]

As you can see, the result is a valid JSON. You may use this in combination with Jquery/AJAX to pass database data to a web application that expects data in JSON.


Post a comment

Comments

ravi | September 27, 2023 9:42 PM |

it is useful. but it is all giving in string format even the boolean values. how to get as it is

jonkimsr | October 1, 2022 7:56 AM |

json_encode($text, JSON_INVALID_UTF8_SUBSTITUTE); UTF-8 invalid char can cause error. Above will replace the offending char.

Shijil Thachilodi | May 13, 2020 9:50 AM |

How can I get result "NICK", by using echo $firstname2;

Santhosh | May 13, 2020 12:48 PM |

You need to use a foreach loop... Check this post - How to parse JSON in PHP

jkm-droid | April 16, 2020 12:33 PM |

Hello, have tried your code but am seeing only a blank page when i run

Leane | April 21, 2020 7:44 PM |

add the following lines to the beginning of your code. 

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
That will display any errors in your code.

Tamil | January 29, 2020 9:22 AM |

i have only blank page result ..what can i do

z03 | February 24, 2020 4:02 PM |

Try running echo json_last_error_msg(); in your script.

Jack | November 1, 2019 5:36 AM |

How do i pass them into javascript. Is that achievable?

Riley | November 4, 2019 11:49 AM |

ofcourse it is achievable and it is very easy too.. 

echo '<script>';
echo 'var name = ' . json_encode($dbdata) . ';';
echo '</script>';

Mo | November 4, 2019 11:46 AM |

you have to use AJAX

Muhammad Jaury | August 7, 2019 9:59 AM |

How are the code if I want the result only show firstname and lastname of all actor?

Patrick | September 3, 2019 7:31 AM |

you can use a foreach loop as below: 

foreach($dbdata as $key => $value) {
   if($key == "first_name" || $key == "last_name") {
    echo $value;
   }

}

Sathish | July 20, 2019 10:18 AM |

This article helped my project. thank you

kamran khan | May 26, 2019 8:00 AM |

Great Job Well Done Keep It Up

Anjali | April 2, 2019 7:07 AM |

Thanks alot this ready helped me with my project.