How to insert JSON data in the database

Organizing the task

• First of all we have to take a look at the data in the JSON file, so we know with what we are dealing with.
• Next step is to create the database table ("products"), based on the JSON data.
• Next we have to connect to the MySQL server and the database.
• Next we have to grab the products from the JSON file and decode them, so we can handle them with php.
• Our next step is to insert the products in the "products" table, but we have to use MySQL prepared statements so we are sure that we don't get hacked.
  We use prepared statements because we don't know the creator of the file, thus we can not trust him. In fact we trust NOBODY.
• And our last step will be to check, if all the products from the file are successfully transferred to the database.

The JSON object

Lets see what the JSON data looks like.

All products are stored in an array [ ... ], every product is an object with properties and values.

In this stage we are interested in the properties "id, image, name, price, quantity", because based on them we are going to create the "products" table. We are going to match the column names with the property names.

[
	{
		"id": 1,
		"image": "bike-1.jpeg",
		"name": "Orient Vita Pro 28",
		"price": "284.50",
		"quantity": 11
	},
	//... and 100 more of them
]

The products table

Here is the "products" table that we created based on the products properties. We see that the products properties and the table's columns are the same.

• Let's take a look at the columns.
  The id column is set to AUTO_INCREMENT, the type is set to int, and it is our table's PRIMARY_KEY.
• The image's' column data type is set to varchar with the max value of 255.
  The same attributes are set also for the name column.
• The price column type is set to float. And the quantity type's to int.

Connecting to the MySQL server

Next step after creating the database table is to connect to the database. We are going to use a mysqli-> object, and we will pass-in our database login data.

$mysqli = new mysqli('localhost', 'username', 'password', 'my_store_db');
if($mysqli->connect_errno != 0){
	echo $mysqli->connect_error;
	exit();
}

• In line 1 we create a new mysqli() object and we pass-in the ("server name", "our username", "our password", "and the database we want to use").
• In line 2-5 we perform a check to see if the connection is successful. If NOT, we echo out an error and exit the script.

Get the JSON data from file

To get the data from the JSON file we are going to use two functions.

We are going to use the file_get_contents() function to grab the raw data, which are (as we know) in a JSON format. And the json_decode() function to convert the data in a php array so we can work with them.

$json_data = file_get_contents("products.json");
$products = json_decode($json_data, JSON_OBJECT_AS_ARRAY);

• In line 7 we store the raw data from the JSON file in the $json_data variable.

• And in line 8 we use the json_decode() function with two arguments.

  In the first argument we pass in the raw json data. And in the second argument we use the JSON_OBJECT_AS_ARRAY flag, so the that the function will return a php array.

  We could also set the flag to true, this will also return an array.

  $products = json_decode($json_data, true);

Insert the data in the products table

Now let's see how we insert the decoded data in the database.

Prepare the query

First of all we have to prepare the query, so we avoid the risk of SQL injections.

To do so, we are gonna use the $mysqli->prepare() method of the mysqli object, and we are going to write a typical INSERT query, but instead of the products values, we are going to use question marks (?) as placeholders.

$stmt = $mysqli->prepare("
	INSERT INTO products(image, name, price, quantity) VALUES(?,?,?,?)
");
$stmt->bind_param("ssdi", $image, $name, $price, $quantity);

• In line 11 where we have our query we see that we have omitted the id column.

  That is because the id column in the database is set to AUTO_INCREMENT so we don't have to pass in the values manually. It is important to have the same amount of question marks as columns.

• Now we have to associate the question marks with the products data.

  That is what we are doing in line 13 with the bind_param() method. The first argument "ssdi" of the method is to declare the data type that the products values have. So inside quotes we use letters to define the data types.

  "s" stands for string, "d" for double or float, and "i" for integer. The other arguments ($image, $name, $price, $quantity) are for now empty variables, we will assign later values to them when we loop through the products array.

• If you take a moment to examine the code, you will see that we have four columns, four question marks, four letters to declare the data type, and four variables. This is the pattern that we have in every prepared statement it doesn't matter if we have 4 columns or 40.

Insert the products

In this step we loop trough the products array, and in every iteration we assign the current product values to our variables.

$inserted_rows = 0;
foreach ($products as $product) {
	$image = $product["image"];
	$name = $product["name"];
	$price = $product["price"];
	$quantity = $product["quantity"];

	$stmt->execute();
	$inserted_rows ++;
}

• In line 15 we creating a variable and initialize it by setting the value to zero. We are gonna use this variable to check if we inserted all products from the file.
• In line 16 we run a foreach() loop and we iterate through the products array. In every iteration we assign new values to the variables we bound in the bind_param() method.
  And in line 22 we execute the prepared statement and insert the product to the database table.
• In line 23 we increment the $inserted_rows variable by one.
  When there are no more products to iterate, the $inserted_rows variable will hold the number of the products inserted in the database. This number must match the number of products stored in the file.
  Let's do the check.

  if(count($products) == $inserted_rows){
  	// All products are successfully recorded in the database.
  }else{
  	// There was an error.
  }
  		

• If the condition evaluates as true, we have successfully inserted all products in the database.

Summary

• We saw how to connect to the database.
• How to fetch the data from the JSON file and decode them.
• We used prepared statements to record the data to the database, to minimize the risk of SQL injections.
• And we loop through the products array and insert the data in the database.

Source code

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

Times downloaded: 402

Buy me a coffee