Convert MySQL to JSON using PHP

In this article, you will learn a simple process to convert MySQL query result to JSON using PHP programming.

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.

For this, first we have created a MySQL table and inserted some records in it. You can use the records if you already have otherwise, you can create it 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');

This is the main file, that we will call in the browser. This file contains code for the MySQL database connection and json_encode() function to generate JSON, it takes PHP array, string, integer, float, Boolean as an input value and converts to JSON String.

Make sure to replace hostname, username, password and database with your database credentials and database name.

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

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

◀ Previous Article
Calculate the distance between two locations

Next Article ▶
Detect Mobile Devices