how to insert values from XML string into mysql with PHP, correctly?


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).

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.

Im a little confused any help is appreciated.


$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 =''.$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

    // 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




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 ($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.

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

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

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.

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.

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.

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

People are also looking for solutions to the problem: php - codeigniter not working on linux


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.