PSYCHOCODES

How to Convert MySQL database table into JSON using PHP

Hello Psychos, 

In this tutorial, we are going to learn how you can convert MySQL database table into JSON but before learning that learn how you can convert JSON into MySQL.

Things you will require to follow this tutorial

  • Good understanding of PHP and MySQL
  • Should have worked with a cross-platform web server such as XAMPP, WAMPP etc.

So let's start by creating a simple database and a table in it the table will consist of three columns id, firstname, lastname.

CREATE DATABASE psychocodes;
CREATE TABLE data(

id int primary key auto_increment,
firstname varchar(10),
lastname varchar(10));

Now let's insert some values into our newly created tables.

INSERT INTO data VALUES(null,'Alex','Mercer'),(null,'Nathan','Drake'),(null,'Sam','Fisher');

We have successfully created our database and table now let's write our PHP script to convert the data in our MySQL table into JSON. The PHP script is shown below.

<?php
$host = "localhost";
$db = "psychocodes";
$user = "your username";  //enter your database username
$pass = "your password";  //enter your database password
$conn = new mysqli($host,$user,$pass,$db); 
$rows = array();

$sql = "SELECT * FROM data";
$result = $conn->query($sql) or die("cannot write");
while($row = $result->fetch_assoc()){
	$rows[] = $row;
}

echo "<pre>";
print json_encode(array('serverres'=>$rows));
echo "</pre>";

?>

Below is the result generated by above PHP script.

MySQL data to JSON php

In the above script, we have to use the json_encode() method which will simply encode the given data present in $rows array into JSON format having name 'serverres'.

Thank you for reading.

Tweet your queries and feedback to @PsychoCodes or leave a message on our Facebook page. You can also comment your questions below.

Also, don't forget to subscribe to our Newsletter.

If you like this article, then please share it and help us grow.

Share your thoughts