how to retrieve data from database in json format using php?

how to retrieve data from database in json format using PHP Script for fetching data from mysql and store in JSON file.

how to retrieve data from database in json format using php

To generate json string we will use php function json_encode(). i am going to learn, how to extract data from MySQL database and export it to a file in JSON format, using PHP.

How to Convert Data from MySQL to JSON using PHP?

Create MySQL Database

CREATE TABLE IF NOT EXISTS `tbl_member` (
  `member_id` int(4) NOT NULL AUTO_INCREMENT,
  `member_name` varchar(60) NOT NULL,
  `nominate` varchar(30) NOT NULL,
  `join_dt` date NOT NULL,
  `earning` int(10) NOT NULL,
  PRIMARY KEY (`member_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `tbl_member` (`member_id`, `member_name`, `nominate`, `join_dt`, `earning`) VALUES
(1, 'Dipti', 'tester', '2022-08-01', 60000),
(2, 'Rekha', 'Bolwer', '2029-10-09', 20000),
(3, 'Kavita', 'Batsmen', '2022-08-20', 40000),
(4, 'Sejal', 'Bolwer', '2022-06-01', 25000),
(5, 'Meera', 'Player', '2029-10-01', 10000);

Convert MySQL to JSON String in PHP

Step 1: Open MySQL Database Connection in PHP
using mysqli_connect() function.

<?php
    //open link to mysql db
    $link = mysqli_connect("hostname","username","password","db_member") or die("Error " . mysqli_error($link));
?>

Step 2: Fetch Data from MySQL Database

<?php
    //fetch table rows from mysql db
    $sql = "select * from tbl_member";
    $result = mysqli_query($link, $sql) or die("Error in Selecting " . mysqli_error($link));
?>

Step 3: Convert MySQL Result Set to PHP Array

<?php
    //create an array
    $members_array = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $members_array[] = $row;
    }
?>

Don’t Miss : Display JSON Data From Database Using Php Using Jquery Ajax

Step 4: Convert PHP Array to JSON String

<?php 
    echo json_encode($members_array);
?>

<?php
    //open link to mysql db
    $link = mysqli_connect("hostname","username","password","db_member") or die("Error " . mysqli_error($link));

    //fetch table rows from mysql db
    $sql = "select * from tbl_member";
    $result = mysqli_query($link, $sql) or die("Error in Selecting " . mysqli_error($link));

    //create an array
    $members_array = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $members_array[] = $row;
    }
    echo json_encode($members_array);

    //close the db link
    mysqli_close($link);
?>

Convert MySQL to JSON File in PHP

<?php
    //write to json file
    $fp = fopen('members_data.json', 'w');
    fwrite($fp, json_encode($members_array));
    fclose($fp);
?>

I hope you get an idea about how to retrieve data from database in json format using php.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.