How to create a JSON file from a MySQL database using php

Updated: 22-Mar-2022 / Tags: PHP and JSON / Views: 1366 - Author: George

Introduction

Hello everyone, in this article we are gonna see how to fetch data from a mysql database and store them in a JSON file as json data. Actually it's very easy to do as you will see further down the road.

The products table

In the database i have a table called products, and as you can see i have six products stored in the table. We are going to fetch those products, and put them in a JSON file.

id image name price quantity
1 bike-1.jpeg Orient Vita Pro 28 284.5 11
2 bike-2.jpeg Orient S-400 26 198 12
3 bike-3.jpeg Bullet Bora 20 240.99 4
4 bike-4.jpeg Speed king 26 345.99 7
5 bike-5.jpeg Speed king 26 420.5 14
6 bike-6.jpeg Offroad X STS 26 399 9

The php code

This is the php file, and the first thing that we have to do is to connect to the database.

<?php
	$mysqli = new mysqli('localhost', 'username', 'password', 'database name');
	if($mysqli->connect_errno != 0){
		echo $mysqli->connect_error;
		exit();
	}
  • In line 2 i create a new mysqli() object and i pass in my mysql credentials to connect to the database. The $mysqli variable, now is holding the mysqli object with all its properties and methods.
  • In line 3 if have an if statement and i check the connect_error_number (connect_errno) property to see if there is an error, and our connection failed. The number that the connect_errno should hold is zero.
  • If the number is anything else then zero, then, there was an error and i echo out the connect_error property which will tell us what that error was.
  • And in line 5 i stop the execution of the script with the exit() function.

Next i am going to write the SQL query and fetch the products from the products database table.

	$sql = "SELECT * FROM products";
	$result = $mysqli->query($sql);
	while($product = $result->fetch_assoc()){
		$products[] = $product;
	}
  • In line 8 i write the sql query and i will select everything from the products table. The asterisk * means everything.
  • In line 9 i use the query() method of the $mysqli object and i execute the sql statement. The $result variable is holding now the returned result set.
  • In line 10-12 i am using a while loop to fetch every row from the result set as an associative array.
    The $result->fetch_assoc() ... will fetch the every row (product), as an associative array.
  • In line 11 i create a new array called $products[], in which i will store every product inside. Now the $products array is holding all products from the database table.

Our last step is to encode the products and put them in a JSON file.

   $encoded_data = json_encode($products);
	file_put_contents('data.json', $encoded_data);
  • In line 14 i use the json_encode function to convert the products to a json string so we can put them in the JSON file. The json_encode() function takes as an argument the data that we want to encode.
  • In line 15 i use the file_put_contents function to write the data to the file. The function takes two arguments. The first argument is the file's name (data.json), in which we want to write the data to.
    And in the second argument we have the data we want to write to the file.

    If the data.json file exists it will be overridden, but if the file doesn't exists the file will be created. So always be careful with the file_put_contents function.

Summary

In this tutorial, we demonstrated how to connect to the database using the MySQLi object. Following that, we executed a query and stored the resulting data in an array. This array was then processed using the json_encode function, allowing us to write the data into a JSON file efficiently

I hope you find the article helpful. Thanks for reading.

Source code

You can download the source code and use it in any way you like.

Times downloaded: 277

Buy me a coffee

If you like to say thanks, you can buy me a coffee.

Buy me a coffee with paypal