Post JSON Array to MySQL

Question

I am trying to create a PHP Store Locator for a school assignment. I am a complete noob so forgive me for making obvious errors. I am trying to get store information from the bbyopen API and create a web page that will allow me to enter a zipcode and populate the nearest store locations. I have the following code that produces the JSON array but I cannot get the data in MySQL. Connects to database but does not post. Any help is greatly appreciated.

My php code

<?php

$json = file_get_contents('http://api.remix.bestbuy.com/v1/stores?show=storeId,name,address,city,postalCode,phone,lat,lng&format=json&apiKey={apiKey}');

$data = json_decode($json,true);

$stores = $data['stores'];

echo "<pre>";

print_r($stores);

$stores1 = array(
    'storeId' => $json->storeId,
    'name' => $json->name,
    'address' => $json->address,
    'city' => $json->city,
    'postalCode' => $json->postalCode,
    'phone' => $json->phone,
    'lat' => $json->lat,
    'lng' => $json->lng,
    );

$stores2 = '"'.implode(array(
    $stores1['storeId'],
    $stores1['name'],
    $stores1['address'],
    $stores1['city'],
    $stores1['postalCode'],
    $stores1['phone'],
    $stores1['lat'],
    $stores1['lng'],
    ),'","').'"';

$username = "root";
$password = "root";
$hostname = "127.0.0.1:8889"; 

//connection to the eventbase
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

//select a eventbase to work with
$selected = mysql_select_db("bbyopen",$dbhandle) 
  or die("Could not select bbyopen");

// Insert $event array into eventbase

    $db_insert = mysql_query("INSERT INTO markers (storeId, name, address, city, postalCode, phone, lat, lng)
    VALUES ($stores2)");

    if (!$db_insert)
    {
    die('Could not connect - event insert failed: ' . mysql_error());
    }
?>

The produced Array

Larry.Z · Accepted Answer · 2013-08-28 05:04:26Z

1

Modify your codes like this

$json = file_get_contents('http://api.remix.bestbuy.com/v1/stores?show=storeId,name,address,city,postalCode,phone,lat,lng&format=json&apiKey={apiKey}');
$data = json_decode($json,true);

$stores = $data['stores'];

echo "<pre>";

print_r($stores);

$values=array();
$i = 0;
foreach($stores as $store){
    $line="(";
    foreach($store as $key => $value){
        $line = $line. "'". $value . "',";
    }
    $line = substr($line, 0, strlen($line)-1).")";
    $values[$i] = $line;
    ++$i;
}
$values = implode(",", $values);
echo $values;

$username = "root";
$password = "123qwe!@#";
$hostname = "127.0.0.1";

//connection to the eventbase
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

//select a eventbase to work with
$selected = mysql_select_db("test",$dbhandle)
or die("Could not select bbyopen");

// Insert $event array into eventbase

$db_insert = mysql_query("INSERT INTO markers (city, address, name, lng, postalCode, phone, storeId, lat) VALUES" . $values);

if (!$db_insert)
{
    die('Could not connect - event insert failed: ' . mysql_error());
}

Note that I don't konw your table structure. Assume that all table fields typed varchar(255). You need to learn how to write sql statements. I hope this example will help you.

answered Aug 28, 2013 at 5:04

Larry.Z

3,7241 gold badge22 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2019391 Over a year ago

Worked perfect. Thank you. I will definitely study sql statements.

Collectives™ on Stack Overflow

Post JSON Array to MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related