Convert MySQL to JSON using PHP

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is more compact, flexible and easier to use. It is used primarily to transmit data between a server and web application. Today, it get more popular than XML. This can be also easily used with Javascript.

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 on the browser. This file contains code for mysql database connection and json_encode() function to generate JSON, it takes PHP array, string, integer, float, Boolean as 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>

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
How to display PDF file in PHP from database

◀ Previous Article
Calculate the distance between two locations

Next Article ▶
Detect Mobile Devices