php - 将XML节点中的数据插入MySQL数据库

我一直在研究以下脚本

$servername = "localhost";
$username = "blah";
$password = "blah";
$dbname = "test";


// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


echo "<form id='post' action='' method='POST'>";
echo "<br />";
echo "<br />";
echo "<input type ='text' value = '' name = 'filepath'/>";
echo "<input type='submit' name='submit_form_1' value='Submit URL' id='submit';'/>";
echo "<br />";
echo "<br />";
echo "</form>";

if(isset($_POST['submit_form_1'])){
     if(empty($_POST['filepath'])) // If the checkbox array called selection is empty
    {
        echo "<p>Please enter a URL that leads to your XML</p>";
    }
    else // If the selection array is not empty we then need to check whether ID has been selected
    {
    $test = $_POST['filepath'];
    print ($test);
    echo "<br />";

    // WE ONLY CONTINUE IF SOME TEXT WAS WRITTEN



// Testing - Get the data from a stored XML file using Simple XML
if (file_exists($test)){ // Check that the file exists
$getxml = simplexml_load_file($test) or die("Error, unfortunately there was an issue");
echo "The stuff from the XML File <br/>";

// Loop through the children within the XML file
foreach($getxml->children() as $element){

    $ID = $element->id; echo $ID;
    $area = $element->area; echo $area;
    $country = $element->country; echo $country;
    $city = $element->city; echo $city;
    $town = $element->town; echo $town;
    $postcode = $element->postcode; echo $postcode;
    $lotarea = $element->lot_area; echo $lotarea;
    $price = $element->price; echo $price;
    $bedrooms = $element->bedrooms; echo $bedrooms;
    $bathrooms = $element->bathrooms; echo $bathrooms;
    $summary = $element->summary; echo $summary;
    $latitude = $element->latitude; echo $latitude;
    $longlitude = $element->longlitude; echo $longlitude;
    $street = $element->street; echo $street;
    $streetno = $element->streetno; echo $streetno;
    $name = $element->name; echo $name;
    $image = $element->image; echo $image;


    $sql = "INSERT INTO importtest (id, area, country, city, town, postcode,
        lotarea, price, bedrooms, bathrooms, summary, latitude, longlitude,
        street, streetno, type, image)
        VALUES($ID, $area, $country, $city, $town, $postcode, $lotarea, $price,
        $bedrooms, $bathrooms, $summary, $latitude, $longlitude, $street, $streetno, $name, $image)";


if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

}



// Insert the data we gathered from the XML file into our database
// PARAMETERIZE THIS AT SOME POINT!


$conn->close();

} else{
    echo "Unfortunately the path you entered: " . $test . " did not return a file";
}


}
}

上面的代码使用SimpleXML从用户指定的源加载XML文件。然后，它获取chld节点中的所有数据并将它们设置为变量。

在这一点上，每次循环“ loops”时，我都尝试执行一条插入语句，但是我收到一条错误消息，而不是插入的数据。

我的问题是我做这种事情的理论是可行的，为什么我的INSERT语句不能按预期运行？

还返回的错误说''附近的错误（我尝试插入的值）

本质上，我正在尝试将一些XML数据导入数据库。

最佳答案

我认为问题是您尝试插入的内部值。

尝试更改您的代码：

 $sql = "INSERT INTO importtest (id, area, country, city, town, postcode,
        lotarea, price, bedrooms, bathrooms, summary, latitude, longlitude,
        street, streetno, type, image)
        VALUES($ID, $area, $country, $city, $town, $postcode, $lotarea, $price,
        $bedrooms, $bathrooms, $summary, $latitude, $longlitude, $street, $streetno, $name, $image)";


if ($conn->query($sql) === TRUE) {

这种：

$stmt = $mysqli->prepare("INSERT INTO importtest (id, area, country, city, town, postcode,
        lotarea, price, bedrooms, bathrooms, summary, latitude, longlitude,
        street, streetno, type, image)
        VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param('issssssiiisssssss', $ID, $area, $country, $city, $town, $postcode, $lotarea, $price,  $bedrooms, $bathrooms, $summary, $latitude, $longlitude, $street, $streetno, $name, $image);

if ($conn->execute() === TRUE) {

并且必须根据表结构类型http://php.net/manual/en/mysqli-stmt.bind-param.php设置bind_param的第一个参数：

i   corresponding variable has type integer
d   corresponding variable has type double
s   corresponding variable has type string
b   corresponding variable is a blob and will be sent in packets

关于php - 将XML节点中的数据插入MySQL数据库，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27851158/