Ask a Question Register Login

How to Insert JSON into mysql table with PHP

137

I have tried many sites to find how to insert json in mysql but i did not find any proper solution. Can you help me to insert the bellow json in mysql table and also tell me the required names for column in my table.

    {
    "request" :  ` {
        "Target" : "Affiliate_Report",
        "Format" : "json",
        "Service" : "HasOffers",
        "Version" : "2",
        "NetworkId" : "network_id",
        "Method" : "getConversions",
        "api_key" : "api_key",
        "fields" : ["Stat.affiliate_info1", "Offer.name"],
        "limit" : "1"
    },
    "response" : {
        "status" : 1,
        "httpStatus" : 200,
        "data" : {
            "page" : 1,
            "current" : 1,
            "count" : 82,
            "pageCount" : 82,
            "data" : [{
                    "Stat" : {
                        "affiliate_info1" : "aashiq"
                    },
                    "Offer" : {
                        "name" : "App Download - Paytm - Android - IN - Incent"
                    }
                }
            ],
            "dbSource" : "branddb"
        },
        "errors" : [],
        "errorMessage" : null
    }
}

i need to insert the data :aashiq, App Download - Paytm - Android - IN - Incent from the above json

755

Answer

Solution:

I will give a try. Bear with me cause my PHP is a bit rusty... So let's say$mydata is you array object storing the JSON. Then:

$data = $mydata["response"]["data"]; 
$name = $data[0]["Stat"]["affiliate_info1"]; // will store aashiq
$offer = $data[0]["Offer"]["name"]; // will store App Download etc...

Create a MySQL database with a database calledoffers with a tableoffer and three fields: anID that will be autoincrement,affiliate VARCHAR,final_offer VARCHAR

//copying from http://www.kodingmadesimple.com/2014/12/how-to-insert-json-data-into-mysql-php.html?m=1
//connect to mysql db
$con = mysql_connect("localhost","username","password") or die('Could not connect: ' . mysql_error());
//connect to the offers database
mysql_select_db("offers", $con);
//write your Insert query, you omit the autoincrement field and 
//after the keyword VALUES you use your variables enclosed in ''
$sql = "INSERT INTO offer(affiliate, final_offer) VALUES('$name', '$offer'"); 
if(!mysql_query($sql,$con))
{
    die('Error : ' . mysql_error());
}

I believe the code above is more or less correct

People are also looking for solutions to the problem: php - Do failed MySQL queries cause side affects?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.