Json到MySQL运行时导入到数据库中很好。当我手动运行SQL查询时，它运行时不会出现错误。
当我在代码中运行它时，它返回到第1行附近的SQL语法错误。
我删除了神奇的引号，仍然是错误。在我扔毛巾之前，有人能照一下吗。
我知道魔术引号是不推荐使用的，我认为这是导致错误的原因，但这些已经被删除了。
<?php
/**
* Created by PhpStorm.
* User: dave
* Date: 14/11/2018
* Time: 15:26
*/
$connect = mysqli_connect("localhost", "root", "password", "clients");
$query = '';
$table_data = '';
$filename = "test.json";
$data = file_get_contents($filename);
$array = json_decode($data, true);
foreach($array as $set)
{
$tblName = $set['tableName'];
echo $tblName ;
if(sizeof($set['rows']) > 0) {
$query = '';
$colList = array();
$valList = array();
// Get list of column names
foreach($set['rows'][1] as $colname => $dataval) {
// $colList[] = "`".$colname."`";
$colList[] = $colname;
}
$query .= "INSERT INTO ".$tblName." n";
$query .= "(".implode(", ",$colList).")nVALUESn";
// Go through the rows for this table.
foreach($set['rows'] as $idx => $row) {
$colDataA = array();
// Get the data values for this row.
foreach($row as $colName => $colData) {
$colDataA[] = "'".$colData."'";
}
$valList[] = "(".implode(",",$colDataA).")";
}
// Add values to the query.
$query .= implode(",n",$valList)."n";
echo "<p>Insert query:<pre>$query</pre></p>";
$results = mysqli_query($connect, $query);
// if ($connect->query($results) === TRUE) {
// echo "New record created successfully";
// } else {
// echo $results . "<br>" . $query . "<br>" ;
// echo "error" .$connect->error ;
// }
echo "<h1>".mysqli_num_rows($connect)." Rows appeded in $tblName</h1>";
} else {
echo "<p>No rows to insert for $tblName</p>";
}
}
Json文件：
[
{
"tableName":"contacts",
"rows":[
{
"First_Name": "Dave",
"Last_Name": "Frank",
"Company": "Company1",
"Business_Phone": "0115 999999",
"Email_Address": "zvv@zz.com"
},
{
"First_Name": "Dave",
"Last_Name": "Blogs",
"Company": "Company2",
"Business_Phone": "0115 888888",
"Email_Address": "zvv@zz.com"
},
{
"First_Name": "David",
"Last_Name": "frank",
"Company": "Company3",
"Business_Phone": "0115 777777",
"Email_Address": "zvv@zz.com"
}
]
},
{
"tableName":"contacts_old",
"rows":[
{
"First_Name": "Dave",
"Last_Name": "Frank",
"Company": "Company1",
"Business_Phone": "0115 999999",
"Email_Address": "zvv@zz.com"
},
{
"First_Name": "Dave",
"Last_Name": "Blogs",
"Company": "Company2",
"Business_Phone": "0115 888888",
"Email_Address": "zvv@zz.com"
},
{
"First_Name": "David",
"Last_Name": "frank",
"Company": "Company3",
"Business_Phone": "0115 777777",
"Email_Address": "zvv@zz.com"
}
]
}
]
这是根据请求的回波：
contacts
Insert query:
INSERT INTO contacts
(First_Name, Last_Name, Company, Business_Phone, Email_Address)
VALUES
('Dave','Frank','Company1','0115 999999','zvv@zz.com'),
('Dave','Blogs','Company2','0115 888888','zvv@zz.com'),
('David','frank','Company3','0115 777777','zvv@zz.com')
Error: 1
INSERT INTO contacts (First_Name, Last_Name, Company, Business_Phone, Email_Address) VALUES ('Dave','Frank','Company1','0115 999999','zvv@zz.com'), ('Dave','Blogs','Company2','0115 888888','zvv@zz.com'), ('David','frank','Company3','0115 777777','zvv@zz.com')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1contacts_old
Insert query:
INSERT INTO contacts_old
(First_Name, Last_Name, Company, Business_Phone, Email_Address)
VALUES
('Dave','Frank','Company1','0115 999999','zvv@zz.com'),
('Dave','Blogs','Company2','0115 888888','zvv@zz.com'),
('David','frank','Company3','0115 777777','zvv@zz.com')
Error: 1
INSERT INTO contacts_old (First_Name, Last_Name, Company, Business_Phone, Email_Address) VALUES ('Dave','Frank','Company1','0115 999999','zvv@zz.com'), ('Dave','Blogs','Company2','0115 888888','zvv@zz.com'), ('David','frank','Company3','0115 777777','zvv@zz.com')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
警告：mysqli_num_rows((要求参数1为mysqli_result，中给定的布尔值
正如Nigel所指出的，您最初的问题是通过将结果集而不是字符串传递给query()而引起的。由于拼写错误，我预计这个问题将作为离题结束，但在这之前，我想指出您的代码中存在许多问题和效率低下。
最重要的是，您缺乏准备好的语句和数据净化，这意味着一旦出现撇号，您的查询就会崩溃。我对mysqli很生疏，所以我在这里的代码中使用了PDO，因为我可以毫不费力地完成它。无论如何，它是一个更现代的界面，而且远没有那么冗长。您可以在PDO和mysqli中阅读更多关于prepared语句的内容，但需要记住的重要一点是，您要准备语句，使用?作为要插入的值的占位符。然后，执行语句，传入值。数据库负责转义任何讨厌的值，而且这比在循环的每次迭代中重建整个查询的开销要小得多。
您还应该使用PHP的内置函数，如array_keys、array_values或array_columns来提取数据，而不是使用foreach循环。
<?php
$connect = new PDO("mysql:host=localhost;dbname=clients", "root", "password");
$filename = "test.json";
$data = file_get_contents($filename);
$array = json_decode($data, true);
foreach($array as $set)
{
$tblName = $set['tableName'];
if(count($set['rows']) > 0) { /* typical to use count() for measuring array size */
// Get list of column names
$colList = array_keys($set["rows"][0]); /* no loop needed */
$query = "INSERT INTO `$tblName` n";
$query .= "(" . implode(", ", $colList) . ")nVALUESn";
$placeholders = implode(",", array_fill(0, count($colList), "?"));
$query .= "($placeholders)"; /* you should have INSERT INTO xxx (yyy, zzz) VALUES (?, ?) */
$stmt = $connect->prepare($query);
if (!$stmt) {
echo "Prepare error: $query<br/>" . $connect->errorInfo()[2];
continue; /* give up, go to next record set */
}
// Go through the rows for this table.
foreach($set['rows'] as $row) {
$result = $stmt->execute($row); /* hey you can just pass the array directly! */
if ($result === TRUE) {
echo "New record created successfully";
} else {
echo "Execute error: $stmt->queryString<br/>" . $stmt->errorInfo()[2];
}
}
} else {
echo "<p>No rows to insert for $tblName</p>";
}
}