How to Export MySQL Table to JSON File in CodeIgniter

Hi! In this tutorial, we will see how to export mysql table to json file in codeigniter. Data portability has become more important than ever and a necessary requirement for modern applications. Almost all web services uses JSON format to migrate data from one location to another. So, as a Web Developer, you must know to handle json and various types of applications like Web, Mobile etc.

Exporting mysql into json allows you to easily port data to different platforms. The whole process is quite simple. Just fetch the data from mysql database, covert the query result to json and then write it in a file.

CodeIgniter - Export MySQL Table to JSON File:

Let's create a demo for exporting the database as json. The steps are easy to follow and we possibly need two files, a model and a controller.

Step-1) Create Database

We need a dummy database to use in the example, a database, a table and some sample records.

Run the below sql file in the mysql environment and create them.

CREATE DATABASE `my_demo`;
USE `my_demo`;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `fname` varchar(30) NOT NULL,
  `lname` varchar(30) NOT NULL,
  `email` varchar(60) NOT NULL,
  `city` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6;

INSERT INTO `users` (`id`, `fname`, `lname`, `email`, `city`) VALUES
(1, 'Jim', 'Connor', 'jimconnor@yahoo.com', 'Las Vegas'),
(2, 'Taylor', 'Fox', 'taylorfox@hotmail.com', 'San Francisco'),
(3, 'Daniel', 'Greyson', 'danielgreyson@hotmail.com', 'New York'),
(4, 'Julia', 'Brown', 'juliabrown@gmail.com', 'Los Angeles'),
(5, 'Rose', 'Harris', 'roseharris@gmail.com', 'New York');

Step-2) Create Model

In this step, create a model file in the 'application/models' folder. This will fetch data from the 'users' table, encode and return it as json to the controller.

UserModel.php

<?php
class UserModel extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }
    
    function toJSON()
    {
          $query = $this->db->get('users');
          return json_encode($query->result(), JSON_PRETTY_PRINT);
    }
}
?>

Step-3) Create Controller

Finally, create a controller file in the 'application/controllers' folder. This will communicate with the model, retrieve the json data and write in into a file.

UserController.php

<?php
class UserController extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->helper('file');
        $this->load->database();
    }
    
    public function index()
    {
        $this->load->model('UserModel');
        $result = $this->UserModel->toJSON();
        if(write_file('user_data.json', $result))
             echo 'Successfully exported to json file!';
        else
             echo 'Error exporting mysql data...';
    }
}
?>

Now we have all the required files in place. Run the controller, this will export the mysql data to a json file and show you a success message if all goes well.

You can check for the file 'user_data.json' inside the root directory. This is the exported file I received.

user_data.json

[
    {
        "id": "1",
        "fname": "Jim",
        "lname": "Connor",
        "email": "jimconnor@yahoo.com",
        "city": "Las Vegas"
    },
    {
        "id": "2",
        "fname": "Taylor",
        "lname": "Fox",
        "email": "taylorfox@hotmail.com",
        "city": "San Francisco"
    },
    {
        "id": "3",
        "fname": "Daniel",
        "lname": "Greyson",
        "email": "danielgreyson@hotmail.com",
        "city": "New York"
    },
    {
        "id": "4",
        "fname": "Julia",
        "lname": "Brown",
        "email": "juliabrown@gmail.com",
        "city": "Los Angeles"
    },
    {
        "id": "5",
        "fname": "Rose",
        "lname": "Harris",
        "email": "roseharris@gmail.com",
        "city": "New York"
    }
]

Read Also:

Likewise you can export the mysql table to the json file using codeigniter. The entire process is same as what you do with core php, but some may find it difficult to shift the code to fit with mvc pattern. And I hope this tutorial has clarified it. Please don't forget to share the post in social media if you like it.