通过PHP使用JSON将excel数据存储在MySQL数据库中

我在一个excel电子表格中有两列数据，我想将其存储在MySQL DB(目前本地托管)上。

我将我的电子表格转换成JSON字符串，我通过XMLHTTP发送到PHP代码。下面是我的VBA代码:

Sub sendjson()
Dim json As String
Dim filed1 As String
Dim i As Integer
Dim j As Integer
Dim data As String
Worksheets("param").Range("C1").Select
data = "{" + Chr(34) + "data" + Chr(34) + ":["
j = 2
Do While Not (IsEmpty(ActiveSheet.Cells(j, 3)))
    j = j + 1
Loop
i = 2
Do While Not (IsEmpty(ActiveSheet.Cells(i, 3)))
    If i < j - 1 Then
    data = data + ActiveSheet.Cells(i, 3) + ","
    Else
    data = data + ActiveSheet.Cells(i, 3) + "]}"
    End If
    i = i + 1
Loop
Worksheets("param").Range("D1").Value = data
json = data
Set objHTTP = CreateObject("Microsoft.XMLHTTP")
objHTTP.Open "POST", "http://localhost/test/jsontomysql.php", False
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objHTTP.send ("field1=" & json)
Set objHTTP = Nothing
End Sub

<?php
try
{
    $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', '');
}
catch(Exception $e)
{
    die('Erreur : '.$e->getMessage());
}
$data = json_decode($json);
foreach ($data as $name => $value) {
    foreach ($value as $entry){
        $req = $bdd->prepare('INSERT INTO param (tck, value) VALUES(:tck, :value)');
        $req->execute(array(
            ':tck'=>$entry->tck,
            ':value'=>$entry->value
        ));     
    }
}
?>

和我的MySQL数据库有以下结构:

参数{tck (VARCHAR255)价值(真正的)}

当我运行代码时，什么都没有发生。我很确定这个问题是围绕:$data = json_decode($json);

为了让事情变得更容易，我直接复制JSON字符串在我的php代码，如下所示:

<?php
try
{
    $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', '');
}
catch(Exception $e)
{
    die('Erreur : '.$e->getMessage());
}
//$phpArray = json_decode($_POST['field1']);
$data = '{
    "u1":{"tck":"EUSA1 Curncy","value":0,005},
    "u2":{"tck":"EUSA2 Curncy","value":0,0049},
    "u3":{"tck":"EUSA3 Curncy","value":0,0048},
    "u4":{"tck":"EUSA4 Curncy","value":0,0047},
    "u5":{"tck":"EUSA5 Curncy","value":0,0046},
    "u6":{"tck":"EUSA6 Curncy","value":0,0045},
    "u7":{"tck":"EUSA7 Curncy","value":0,0044},
    "u8":{"tck":"EUSA8 Curncy","value":0,0043},
    "u9":{"tck":"EUSA9 Curncy","value":0,0042}
    }';
$phpArray = json_decode($data, true);
foreach ($phpArray as $key => $value) { 
    foreach ($value as $entry) { 
        $req = $bdd->prepare('INSERT INTO param (tck, value) VALUES(:tck, :value)');
        $req->execute(array(
            ':tck'=>$entry->tck,
            ':value'=>$entry->value
        ));
    }
}
?>

似乎我在第一个foreach循环的第26行有一个错误…

Warning: Invalid argument supplied for foreach() in C:'wamp'www'finance'jsontomysql.php on line 26

编辑:

问题必须在VBA和PHP之间，因为，当我这样做时，SQL DB更新得很好:

<?php
try
{
    $bdd = new PDO('mysql:host=localhost;dbname=test', 'root', '');
}
catch(Exception $e)
{
    die('Erreur : '.$e->getMessage());
}
//$phpArray = json_decode($_POST['field1']);
$data = '{"u1":{"tck":"EUSA1 Curncy","value":0.005},"u2":{"tck":"EUSA2 Curncy","value":0.0049},"u3":{"tck":"EUSA3 Curncy","value":0.0048},"u4":{"tck":"EUSA4 Curncy","value":0.0047},"u5":{"tck":"EUSA5 Curncy","value":0.0046},"u6":{"tck":"EUSA6 Curncy","value":0.0045},"u7":{"tck":"EUSA7 Curncy","value":0.0044},"u8":{"tck":"EUSA8 Curncy","value":0.0043},"u9":{"tck":"EUSA9 Curncy","value":0.0042}}';
var_dump($data);
$phpArray = json_decode($data, true);
var_dump($phpArray);
foreach ($phpArray as $u) {  
        $req = $bdd->prepare('INSERT INTO param (tck, value) VALUES(:tck, :value)');
        $req->execute(array(
            ':tck'=>$u['tck'],
            ':value'=>$u['value']
        ));
}
?>

有什么问题吗?谢谢你