how to insert values from XML string into mysql with PHP, correctly?
44
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.
<?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);
}
?>
Answer
Solution:
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:
The outer iteration over
$xml->office->candidateresulting in$itemrepresenting 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.
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.