Insert JSON data into MySQL using php

by

Posted on 24 September 2016

JSON (Javascript Object Notation)  JSON is a syntax for storing and exchanging data. Let us see how a JSON file is created and how data get stored in it.

{
"name"          :         "John",
"profession"   :         "programmer",
"Age"             :          20
}

Convert JSON data into MySQL

this is a simple JSON file which stores data as name=John, profession=programmer and age=20

Now we will see how to insert this data into MySQL  database using PHP, we are going to use file_get_contents() and json_decode() function of PHP, see the below picture to understand.

Convert JSON  into MySQL

Let us create the database to store our JSON file data

CREATE TABLE student(
name int(20),
profession varchar(30),
age int(100)
);

We will use the above JSON file example to insert it into the above MySQL table, let us see each step one by one.

Connecting to MySQL database

$user = "username";
$host = "localhost";
$pass = "password";
$db_name = "database name";
$conn = new mysqli($host,$user,$pass,$db_name);

After connecting to the database we will copy the file contents of our JSON file into $file  variable using the PHP file_get_contents() function

$file = file_get_contents("student.json",true);

Now we will decode the JSON file contents which are stored in $file using the json_decode() function

$json = json_decode($file,true);

The json_decode function will decode the content present in $file and store it in the form of array in $json 

We will now insert the JSON data into MySQL

$name = $json["name"];
$profession=$json["profession"];
$age=$json["age"];
$sql = "INSERT INTO student(name,profession,age) VALUES('$name','$profession',$age)";
$result = $conn->query($sql) or die("Cannot write");
if($result){
     echo "Successfully written";
}

Now let's see the whole PHP code

//databse connection
$host = "localhost";
$pass = "password";
$db_name = "database name";
$conn = new mysqli($host,$user,$pass,$db_name);
//converting the file contents 
$file = file_get_contents("student.json",true);
$json = json_decode($file,true);
$name = $json["name"];
$profession=$json["profession"];
$age=$json["age"];
//databse insertion
$sql = "INSERT INTO student(name,profession,age) VALUES('$name','$profession',$age)";
$result = $conn->query($sql) or die("Cannot write");
if($result){
     echo "Successfully written";
}

We have successfully copied our JSON content into MySQL database.

Thanks for reading

Tweet your queries and feedback to @PsychoCodes or leave a message on our Facebook . You can also comment your questions below.

Also, don't forget to subscribe to our Newsletter at top of this page.

If you like this article, then please share it and help us grow.


If You Love this article, You Should Consider:

  • Like us on Facebook
  • Follow us on Instagram
  • Follow us on Twitter
  • Subscribe to our Newsletter.
  • Let us know your suggestions and queries in the comments below.

Thank you for your Love and Support

Share your thoughts

Search
Adverstisement
Adverstisement