One of the important responsibilities in web development for PHP is the conversion of data from MySQL to JSON format. JSON is favoured over XML as a data interchange format between web applications and has grown in popularity over time.
JSON has its own benefits, such as being lightweight, allowing for the storage of sophisticated data structures in plain text, and being extremely readable by humans. Here, we’ve already spoken about translating JSON data to MySQL. Let’s now examine how to use PHP to translate the MySQL result set to JSON.
PHP Script to convert MySQL to JSON
Step 1: Create Table and Add Demo Data in Database
If you do not have a database table ready, use the following SQL query to create a technologies table with id and tech_name table properties and add some data into the technologies table to check that data using the jQuery live search box in PHP.
CREATE TABLE technologies ( id INT NOT NULL AUTO_INCREMENT , tech_name VARCHAR(255) NOT NULL , PRIMARY KEY (id) );
INSERT INTO technologies VALUES(1,'HTML');
INSERT INTO technologies VALUES(2,'CSS');
INSERT INTO technologies VALUES(3,'JAVASCRIPT');
INSERT INTO technologies VALUES(4,'JQUERY');
INSERT INTO technologies VALUES(5,'PHP');
INSERT INTO technologies VALUES(6,'AJAX');
INSERT INTO technologies VALUES(7,'NODEJS');
INSERT INTO technologies VALUES(8,'EXPRESSJS');
INSERT INTO technologies VALUES(9,'ANGULARJS');
INSERT INTO technologies VALUES(10,'REACTJS');
Step 2: MySQL Database Connection
Inside your PHP project folder, you need to create a db.php file within the directory, inside this file, we will make the PHP database connection with the MySQL database.
Add the following db connection code in db.php file:
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "test";
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if(!$connection){
die('Database connection error : ' .mysql_error());
}
?>
Step 3: Fetch Data from MySQL Database
<?php
require_once "./db.php";
$sql = "SELECT * FROM technologies";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));
?>
Step 4: Convert MySQL Result Set to PHP Array
<?php
//create an array
$techarray = array();
while($row =mysqli_fetch_assoc($result)){
$techarray[] = $row;
}
?>
Step 5: Convert PHP Array to JSON String
<?php echo json_encode($techarray); ?>
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "test";
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if(!$connection){
die('Database connection error : ' .mysql_error());
}
$sql = "SELECT * FROM technologies";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));
//create an array
$techarray = array();
while($row =mysqli_fetch_assoc($result)){
$techarray[] = $row;
}
echo json_encode($techarray);
?> Sample Output
Use JSON Viewer Chrome extension to view the JSON output.
Convert MySQL to JSON File in PHP
<?php
$fp = fopen('data.json', 'w');
fwrite($fp, json_encode($techarray));
fclose($fp);
?>