CODEWITHSUNDEEP
phpmysqlarrays
AAA
AAA

Reputation: 2450

Posting data from a JSON object to MySQL

I have a JSON object that I am returning in a PHP page and can view by using the following line:

return json_decode($jsondata);

The output for this on my page displays the following:

object(stdClass)#2 (6) { 
    ["offset"]=> int(0) 
    ["results"]=> array(1) { 
        [0]=> object(stdClass)#3 (6) { 
            ["status/_text"]=> string(11) "Available" 
            ["price"]=> string(6) "$9.99" 
            ["status/_source"]=> string(80) "/store/store/en_US/buy/SKU.123123123/ThemeID.123123123/Currency.USD/mktp.US" 
            ["status/_title"]=> string(11) "Available" 
            ["title"]=> string(25) "Mouse" 
            ["status"]=> string(109) "http://www.domain.com/store/store/en_US/buy/SKU.123123123/ThemeID.123123123/Currency.USD/mktp.US" 
        } 
    } 
    ["cookies"]=> array(8) { 
        [0]=> string(112) "VISITOR_ID="1D4E8DFA72ADBFE2B570FD25A6EE06E";Path="/";Domain="www.domain.com";Port="80"" 
        [1]=> string(445) "SESSION="SessionID";Path="/";Domain="www.domain.com";Port="80"" 
        [2]=> string(70) "X-DR-LOCALE="en_US";Path="/";Domain="www.domain.com";Port="80"" 
        [3]=> string(70) "X-DR-CURRENCY="USD";Path="/";Domain="www.domain.com";Port="80"" 
        [4]=> string(72) "X-DR-THEME="123123123";Path="/";Domain="www.domain.com";Port="80"" 
        [5]=> string(96) "BIGipServerp-dynamicpool="841066.260.0000";Path="/";Domain="www.domain.com";Port="80"" 
        [6]=> string(80) "USE_ALTERNATE_GC_SITE="False";Path="/";Domain="www.domain.com";Port="80"" 
        [7]=> string(111) "BIGipServerp-c033-prd-proxy-active="9626.65057.0000";Path="/";Domain="www.domain.com";Port="80"" 
    } 
    ["connectorVersionGuid"]=> string(36) "2a1ec4c-8b02-c8bb1cce" 
    ["connectorGuid"]=> string(36) "d243-9b57-3f41729f76a3" 
    ["pageUrl"]=> string(97) "http://www.domain.com/store/store/en_US/pdp/Mouse/SKU.123123123" 
}

The output looks like the following with var_dump:

string(1743) "{"offset":0,"results":[{"status/_text":"Available","price":"$9.99","status/_source":"/store/store/en_US/buy/productID.123123123/ThemeID.33363200/Currency.USD/mktp.US","status/_title":"Available","title":" Mouse","status":"http://www.domain.com/store/store/en_US/buy/productID.123123123/ThemeID.33363200/Currency.USD/mktp.US"}],"cookies":["VISITOR_ID=\"971D4E8DFAED4367DC9214BBB4AA2CC692F62AA82773DBA\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","SESSION=\"dr7jLmcwTV+++yiyGVurgIDs80OKxywxJHPEEPVmi44pEZlC1QXhiTSCFldtMXQ8+TH4SX8ykhfrhf11LFlS+/jeqt127gO0LoBoDlVeMMZCGn2kAYIR/H1KG4DVAtTuyh05Nzq02KSiQK4/4rYVRIA2//PzGdQef/1XWS2SPQqId583XdI6LEvyMLeAv94Ag4LgpsoFlDFvXrkXJ+ \";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","X-DR-LOCALE=\"en_US\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","X-DR-CURRENCY=\"USD\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","X-DR-THEME=\"33363200\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","BIGipServerp-dynamicpool=\"762720522.260.0000\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","USE_ALTERNATE_GC_SITE=\"False\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","BIGipServerp-c033-drx-prd-proxy-active=\"226177290.65057.0000\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\""],"connectorVersionGuid":"46d7-8b02 ","connectorGuid":" 3ff76a3","pageUrl":"http://www.domain.com/store/store/en_US/pdp/Mouse/productID.123123123"}" NULL

That part appears to be working great. I can also write data to a MySQL database by using code like this:

mysqli_query($con,"INSERT INTO Table (URL, Product, Price, Status)
VALUES ('URL', 'Product','1','Yes')");
mysqli_close($con);

I am now trying to take data from $jsondata and post specific fields into the MySQL Table table using the SQL above as a template. How do I swap out the hard coded values for URL, Product, Price and Status with the correct data from the JSON object?

This is the mapping that I believe should take place:

URL = pageUrl
Product = title
Price = price
Status = status/_text

Thank you!

Upvotes: 0

Views: 136

Answers (2)

Kevin
Kevin

Reputation: 41885

You already decoded the response, then just normally insert them accordingly:

$jsondata = json_decode('{"offset":0,"results":[{"status/_text":"Available","price":"$9.99","status/_source":"/store/store/en_US/buy/productID.123123123/ThemeID.33363200/Currency.USD/mktp.US","status/_title":"Available","title":" Mouse","status":"http://www.domain.com/store/store/en_US/buy/productID.123123123/ThemeID.33363200/Currency.USD/mktp.US"}],"cookies":["VISITOR_ID=\"971D4E8DFAED4367DC9214BBB4AA2CC692F62AA82773DBA\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","SESSION=\"dr7jLmcwTV+++yiyGVurgIDs80OKxywxJHPEEPVmi44pEZlC1QXhiTSCFldtMXQ8+TH4SX8ykhfrhf11LFlS+/jeqt127gO0LoBoDlVeMMZCGn2kAYIR/H1KG4DVAtTuyh05Nzq02KSiQK4/4rYVRIA2//PzGdQef/1XWS2SPQqId583XdI6LEvyMLeAv94Ag4LgpsoFlDFvXrkXJ+ \";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","X-DR-LOCALE=\"en_US\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","X-DR-CURRENCY=\"USD\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","X-DR-THEME=\"33363200\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","BIGipServerp-dynamicpool=\"762720522.260.0000\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","USE_ALTERNATE_GC_SITE=\"False\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\"","BIGipServerp-c033-drx-prd-proxy-active=\"226177290.65057.0000\";Path=\"/\";Domain=\"www.domain.com\";Port=\"80\""],"connectorVersionGuid":"46d7-8b02 ","connectorGuid":" 3ff76a3","pageUrl":"http://www.domain.com/store/store/en_US/pdp/Mouse/productID.123123123"}');
$results = $jsondata->results;

$db = new mysqli('localhost', 'username', 'password', 'database_name');
$insert = $db->prepare('INSERT INTO `table_name` (URL, Product, Price, Status) VALUES(?, ?, ?, ?) ');
foreach($results as $val) {
    $insert->bind_param('ssss', $jsondata->pageUrl, $val->title, $val->price, $val->{'status/_text'});
    $insert->execute();
}

Upvotes: 1

Darren
Darren

Reputation: 13128

You simply need to decode the json string with json_decode().

$o = json_decode($json);

And set the values up like this:

$pageUrl = $o->pageUrl;
$product = $o->results[0]->title;
$price = $o->results[0]->price;
$status = $o->results[0]->{"status\_text"};

Example

Note: this example assumes that you're only getting 1 result from the returned json. ($o->results[0]->etcc.....)

Upvotes: 1

Related Questions