Convert MySQL to JSON using PHP
In this article, you will learn a simple process to convert MySQL query results to JSON using the PHP programming language.
JSON (JavaScript Object Notation) is a lightweight, open standard file format. It is an array data type and consisting of attribute–value pairs. It is easy to read and write for humans. It is used primarily to transmit data between a web application and a server. JSON is popular among developers for data serialization. It is so popular that every modern programming language has methods to generate and parse JSON formatted data.
Create the Database
First we created a MySQL table and inserted some records into it. You can use the records if you already have otherwise, you can create them manually or copy and paste the following queries in your database.
CREATE TABLE IF NOT EXISTS `employee` (
`emp_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`age` int(11) NOT NULL,
`department` varchar(100) NOT NULL
)
INSERT INTO `employee` (`emp_id`, `name`, `age`, `department`) VALUES
(1, 'John', 30, 'Software'),
(2, 'Smith', 32, 'Hardware'),
(3, 'Gaga', 33, 'Software'),
(4, 'Mary', 29, 'Purchase');
Convert MySQL to JSON using PHP
These are the steps for converting mysql to json string using the PHP programming language.
Open MySQL Database Connection in PHP
First, establish the MySQL database connection using the mysqli_connect() function. Make sure to replace hostname, username, password and database with your database credentials and database name.
$connection = mysqli_connect("hostname","username","password","database")
or die("Error " . mysqli_error($connection));
Fetch Data from MySQL Database
After opening the connection, fetch the required table data from the database using the PHP function mysqli_query().
//fetch table rows from mysql db
$sql = "select * from employee";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));Convert MySQL Result Set to PHP Array
//create an array
$emparray = array();
while($row = mysqli_fetch_assoc($result))
{
$emparray[] = $row;
}Convert PHP Array to JSON String
Next, we use the PHP function json_encode() to convert the PHP array to a JSON string. This function takes PHP array, string, integer, float, Boolean as an input value and converts it to JSON String.
$data = json_encode($emparray, JSON_PRETTY_PRINT);
echo '<pre>'; print_r($data);
Complete code: PHP MySQL to JSON
Here, we have merged the above codes to convert the MySQL to JSON.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Convert MYSQL Data to JSON</title>
</head>
<body>
<?php
$connection = mysqli_connect("hostname","username","password","database")
or die("Error " . mysqli_error($connection));
//fetch table rows from mysql db
$sql = "select * from employee";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));
//create an array
$emparray = array();
while($row = mysqli_fetch_assoc($result))
{
$emparray[] = $row;
}
$data = json_encode($emparray, JSON_PRETTY_PRINT);
echo '<pre>'; print_r($data);
//close the db connection
mysqli_close($connection);
?>
</body>
</html>
Related Articles
Python JSON Tutorial- Create, Read, Parse JSONConvert MySQL query result to JSON in Python
Parsing JSON in Javascript
PHP Create Word Document from HTML
Simple pagination in PHP with MySQL
How to reverse a number in Javascript
Create Dynamic Pie Chart using Google API, PHP and MySQL
jQuery loop over JSON result after AJAX Success
PHP get IP address of visitor
Preventing Cross Site Request Forgeries(CSRF) in PHP
PHP code to send email using SMTP
Simple pagination in PHP
Simple PHP File Cache
PHP Connection and File Handling on FTP Server
Sending form data to an email using PHP
Recover forgot password using PHP and MySQL
PHP multiple file upload
How to display PDF file in PHP from database
Calculate the distance between two locations
Detect Mobile Devices
