如何使用PHP将XML字符串中的值插入到mysql中？

I am attempting to read and parse an XML file with the following code however I'm running int several issues the first issue is that the state variable and other known populated variables are not importing into the MYSQL table, not sure why. All my tables and columns are varchar(255).

我正在尝试使用以下代码读取和解析XML文件,但是我正在运行int几个问题,第一个问题是状态变量和其他已知的填充变量没有导入到MYSQL表中,不确定原因。我的所有表和列都是varchar(255)。

The second issue is that the total number of records stops at 50, with no error or anything. Not sure why the CA state alone has hundreds of records and this script is supposed to loop through all the states then loop through all the items in the XML string and insert them into a MYSQL table.

第二个问题是记录总数停在50,没有任何错误或任何错误。不确定为什么CA状态单独有数百条记录,并且该脚本应该循环遍历所有状态,然后循环遍历XML字符串中的所有项并将它们插入到MYSQL表中。

Im a little confused any help is appreciated.

我有点困惑任何帮助表示赞赏。

<?php

$arr = array("AK", "AL", "AR", "AZ", "CA", "CO", "CT", "DC", "DE", "FL", "GA", "GU", "HI", "IA", "ID", "IL", "IN", "KS", "KY", "LA", "MA", "MD", "ME", "MI", "MN", "MO", "MS", "MT", "NC", "ND", "NE", "NH", "NJ", "NM", "NV", "NY", "OH", "OK", "OR", "PA", "PR", "RI", "SC", "SD", "TN", "TX", "UT", "VA", "VI", "VT", "WA", "WI", "WV", "WY");
foreach ($arr as $value) {

    $url ='http://api.votesmart.org/Address.getOfficeByOfficeState?key=04c935d4337616f104e5fc905e9fef2d&officeId=9&stateId='.$value.'&officeTypeId=C';
        $ch = curl_init();
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_URL, $url); //getting url contents

    $data = curl_exec ($ch); //execule curl request
    curl_close($ch);

    // read XML data string
    $xml = simplexml_load_string($data) or die("ERROR: Cannot create SimpleXML object");
    // open MySQL connection
    $connection = mysqli_connect("localhost", "xxx", "xxx", "xxx") or die ("ERROR: Cannot connect");
    // process node data
    // create and execute INSERT queries

    foreach ($xml->office->candidate as $item) {
        foreach ($xml->office->address as $item2) {
            foreach ($xml->office->phone as $item3) {
                foreach ($xml->office->notes as $item4) {

                    $candidateId = $item->candidateId;
                    $title = mysqli_real_escape_string($connection, $item->title);
                    $firstName = mysqli_real_escape_string($connection, $item->firstName);
                    $middleName = mysqli_real_escape_string($connection, $item->middleName);
                    $nickName = mysqli_real_escape_string($connection, $item->nickName);
                    $lastName = mysqli_real_escape_string($connection, $item->lastName);
                    $suffix = mysqli_real_escape_string($connection, $item->suffix);

                    $type = $item2->type;
                    $typeId = $item2->typeId;
                    $street = $item2->street;
                    $city = $item2->city;
                    $state = $item->state;
                    $zip = $item2->zip;

                    $phone1 = $item3->phone1;
                    $phone2 = $item3->phone2;
                    $fax1 = $item3->fax1;
                    $fax2 = $item3->fax2;
                    $tollFree = $item3->tollFree;
                    $ttyd = $item3->ttyd;
                    $cellphone = $item3->cellphone;

                    $contactName = $item4->contactName;
                    $contactTitle = $item4->contactTitle;

                    $sql = "INSERT INTO address (candidateId, title, firstName, middleName, nickName, lastName, suffix, type, typeId, street, city, state, zip, phone1, phone2, fax1, fax2, tollFree, ttyd, cellphone, contactName, contactTitle) VALUES ('$candidateId', '$title', '$firstName', '$middleName', '$nickName', '$lastName', '$suffix', '$type', '$typeId', '$street', '$city', '$state', '$zip', '$phone1', '$phone2', '$fax1', '$fax2', '$tollFree', '$ttyd', '$cellphone', '$contactName', '$contactTitle') ON DUPLICATE KEY UPDATE title = '$title', firstName = '$firstName', middleName = '$middleName', nickName = '$nickName', lastName = '$lastName', suffix = '$suffix', type = '$type', typeId = '$typeId', street = '$street', city = '$city', state = '$state', zip = '$zip', phone1 = '$phone1', phone2 = '$phone2', fax1 = '$fax1', fax2 = '$fax2', tollFree = '$tollFree', ttyd = '$ttyd', cellphone = '$cellphone', contactName = '$contactName', contactTitle = '$contactTitle'";

                    mysqli_query($connection, $sql) or die ("ERROR: " .mysqli_error($connection) . " (query was $sql)");
                }
            }
        }
    }

    // close connection
    mysqli_close($connection);
}

?>

1 个解决方案

#1

I can't say on first glance if there is a problem with the data you have, but for the foreach-iteration you do, there is an issue:

我不能一眼就看出你的数据是否有问题,但是对于你做的foreach迭代,有一个问题:

foreach ($xml->office->candidate as $item) {
        foreach ($xml->office->address as $item2) {

The outer iteration over $xml->office->candidate resulting in $item representing all candidate elements of the first office element. That's perhaps most likely the number stops at 50 as there are 54 states and perhaps not all states have even an office.

$ xml-> office-> candidate的外部迭代导致$ item表示第一个office元素的所有候选元素。也许最有可能这个数字停在50,因为有54个州,也许并非所有州都有办公室。

A quick check indeed reveals that there are 50 candidate entries in the first office elements of those 54 files.

快速检查确实显示在这54个文件的第一个办公室元素中有50个候选条目。

So you more likely want to stack the foreach-es differently.

因此,您更可能希望以不同方式堆叠foreach。

foreach ($xml->office as $office) {
    foreach ($office->candidate as $candidate) {
        ...
    }
}

This will already for this level alone reveal 3383 candidate elements within all office elements (and not only the first) in those 54 XML documents alone.

仅这个级别就已经在这54个XML文档中的所有办公元素(而不仅仅是第一个)中显示了3383个候选元素。

As you stack multiple levels of those foreach-es into each other, this can become quite complex so you should build this up from the outer to the inner - which requires some planning and some step-by-step work.

当你将这些foreach-es的多个级别堆叠在一起时,这可能变得非常复杂,所以你应该从外部到内部构建它 - 这需要一些规划和一些一步一步的工作。

If you prefer to build from the inner to the outer, I suggest to make use of Xpath instead to first get the element nodes interested in and take the parent's information with other relative xpath queries then. But this requires knowledge in xpath which I don't know if you heard of it or not.

如果您更喜欢从内部构建到外部,我建议使用Xpath代替首先获取感兴趣的元素节点,然后使用其他相对xpath查询获取父代的信息。但这需要xpath中的知识,如果你听说过,我不知道。

Anyway, taking care with the foreach-es and most likely picking better variable names should avance you to your destination already.

无论如何,关注foreach-es并且最有可能选择更好的变量名称应该让你到达目的地。

#1