将JSON数组发布到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

Array
)
[0] => Array
    (
        [city] => San Juan
        [address] => 230 Calle Federico Costa Hato Rey
        [name] => Hato Rey
        [lng] => -66.07188
        [postalCode] => 00918
        [phone] => 787-764-4900
        [storeId] => 1118
        [lat] => 18.42684
    )

[1] => Array
    (
        [city] => Bayamon
        [address] => 60 Ave Rio Hondo Ste 60
        [name] => Rio Hondo
        [lng] => -66.16224
        [postalCode] => 00961
        [phone] => 787-522-0999
        [storeId] => 1090
        [lat] => 18.42217
    )

[2] => Array
    (
        [city] => Plaza Carolina
        [address] => Villa Fontana Ave Fragoso
        [name] => Plaza Carolina
        [lng] => -65.9688829
        [postalCode] => 00983
        [phone] => 787-522-4549
        [storeId] => 1496
        [lat] => 18.3912915
    )

[3] => Array
    (
        [city] => Hadley
        [address] => 367 Russell St
        [name] => Hadley
        [lng] => -72.54847
        [postalCode] => 01035
        [phone] => 800-375-1736
        [storeId] => 683
        [lat] => 42.357971
    )

[4] => Array
    (
        [city] => Holyoke
        [address] => 50 Holyoke St
        [name] => Holyoke
        [lng] => -72.643005
        [postalCode] => 01040
        [phone] => 413-533-4443
        [storeId] => 418
        [lat] => 42.169613
    )

[5] => Array
    (
        [city] => Holyoke
        [address] => #B216, 50 Holyoke Street, Ingleside mall
        [name] => Best Buy Mobile - Holyoke Mall
        [lng] => -72.640241
        [postalCode] => 01040
        [phone] => 413-535-2070
        [storeId] => 2843
        [lat] => 42.17108
    )

[6] => Array
    (
        [city] => Lanesboro
        [address] => 655 Cheshire Rd
        [name] => Pittsfield
        [lng] => -73.205688
        [postalCode] => 01237
        [phone] => 413-445-5812
        [storeId] => 548
        [lat] => 42.493664
    )

[7] => Array
    (
        [city] => Leominster
        [address] => 33 Orchard Hill Park Dr
        [name] => Leominster
        [lng] => -71.712425
        [postalCode] => 01453
        [phone] => 978-537-9042
        [storeId] => 1433
        [lat] => 42.527382
    )

[8] => Array
    (
        [city] => Auburn
        [address] => 385 Southbridge Street, S080
        [name] => Best Buy Mobile - Auburn Mall
        [lng] => -71.835952
        [postalCode] => 01501
        [phone] => 508-832-3203
        [storeId] => 2901
        [lat] => 42.203384
    )

[9] => Array
    (
        [city] => Millbury
        [address] => 70 Worcester Providence Turnpike
        [name] => Millbury
        [lng] => -71.77605
        [postalCode] => 01527
        [phone] => 508-421-9149
        [storeId] => 2506
        [lat] => 42.19519
    )
)
Connected to MySQL

Answer 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.