duanshan1977 2013-08-28 03:09
浏览 42
已采纳

将JSON数组发布到MySQL

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
  • 写回答

1条回答 默认 最新

  • dpxw17759 2013-08-28 05:04
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求.net core 几款免费的pdf编辑器
  • ¥20 SQL server表计算问题
  • ¥15 C# P/Invoke的效率问题
  • ¥20 thinkphp适配人大金仓问题
  • ¥20 Oracle替换.dbf文件后无法连接,如何解决?(相关搜索:数据库|死循环)
  • ¥15 数据库数据成问号了,前台查询正常,数据库查询是?号
  • ¥15 算法使用了tf-idf,用手肘图确定k值确定不了,第四轮廓系数又太小才有0.006088746097507285,如何解决?(相关搜索:数据处理)
  • ¥15 彩灯控制电路,会的加我QQ1482956179
  • ¥200 相机拍直接转存到电脑上 立拍立穿无线局域网传
  • ¥15 (关键词-电路设计)