This php script below fetches XML job feed from the remote url and map the fields into the Database. The script was working fine, till yesterday when the specialty node values from the XML feed were changed to multiple values separated with comma.
<?php
require("globals.php5");
require("cookies.php5");
define(HPP_ACCT,111);
define(HPP_UID,10788);
error_reporting(E_ALL);
if(isset($_POST["submit"])) {
$filename="http://example.com/xmlfeed";
$ch = curl_init($filename);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$output = curl_exec($ch);
if($output === false) throw new Exception('Curl error: ' . curl_error($ch),__LINE__);
curl_close($ch);
$xml = simplexml_load_string($output);
if($xml) {
$hmacct = HPP_ACCT;
$db = db_career();
$sql = "select sp_code,sp_name from specialties";
$res = $db->query($sql);
if( !$res )
throw new Exception(DEBUG?"$db->error : $sql":'Problem with Specialties Table',__LINE__);
$hamspec = array();
while( list($spc,$sph) = $res->fetch_row() ) {
$hamspec[$sph] = $spc;
}
$res->free();
$sql = "select st_code,st_name from states";
$res = $db->query($sql);
if( !$res )
throw new Exception(DEBUG?"$db->error : $sql":'Problem with States Table',__LINE__);
$hamstadt = array();
while( list($spc,$sph) = $res->fetch_row() ) {
$hamstadt[$sph] = $spc;
}
$res->free();
if(HPP_ACCT!=""){
$sql = "delete from opportunities where o_acct='".HPP_ACCT."' AND status=1 ";
$res = $db->query($sql);
}
$client = new Customer($db,HPP_UID);
$states = array(
'Alabama'=>'AL',
'Alaska'=>'AK',
'Arizona'=>'AZ',
'Arkansas'=>'AR',
'California'=>'CA',
'Colorado'=>'CO',
'Connecticut'=>'CT',
'Delaware'=>'DE',
'Florida'=>'FL',
'Georgia'=>'GA',
'Hawaii'=>'HI',
'Idaho'=>'ID',
'Illinois'=>'IL',
'Indiana'=>'IN',
'Iowa'=>'IA',
'Kansas'=>'KS',
'Kentucky'=>'KY',
'Louisiana'=>'LA',
'Maine'=>'ME',
'Maryland'=>'MD',
'Massachusetts'=>'MA',
'Michigan'=>'MI',
'Minnesota'=>'MN',
'Mississippi'=>'MS',
'Missouri'=>'MO',
'Montana'=>'MT',
'Nebraska'=>'NE',
'Nevada'=>'NV',
'New Hampshire'=>'NH',
'New Jersey'=>'NJ',
'New Mexico'=>'NM',
'New York'=>'NY',
'North Carolina'=>'NC',
'North Dakota'=>'ND',
'Ohio'=>'OH',
'Oklahoma'=>'OK',
'Oregon'=>'OR',
'Pennsylvania'=>'PA',
'Rhode Island'=>'RI',
'South Carolina'=>'SC',
'South Dakota'=>'SD',
'Tennessee'=>'TN',
'Texas'=>'TX',
'Utah'=>'UT',
'Vermont'=>'VT',
'Virginia'=>'VA',
'Washington'=>'WA',
'West Virginia'=>'WV',
'Wisconsin'=>'WI',
'Wyoming'=>'WY'
);
$ar = array();
foreach($xml->Opportunity as $job) {
$jid = "JOB {" .addslashes($job->OpportunityId)."}";
$jobid = "(JOB ID " .addslashes($job->JobNumber).")";
$facility = addslashes($job->facility)." ".$jobid;
$city = $job->city;
$state = $states["$job->state"];
$spec = addslashes(trim($job->specialty));
switch($spec) {
case "Family Medicine":
$spec="Family Practice";
break;
case "Internal Medicine":
$spec="Internal Medicine";
break;
case "Pediatric General":
$spec="Pediatric - General";
break;
}
$spec = trim($spec);
try{
@$spec2 = $spec == 'Emergency Medicine'?'EM':($spec == 'Hospitalist Medicine'?'HOS':$hamspec["$spec"]);
}catch(Exception $e){
echo $e->getMessage().' ('.$e->getCode().')<br>';
}
if(!$spec2){
echo "No specialty mapping for ".$spec.". Will be skipped.<br/>";
} else {
$title=$job->headline;
if($title==''||$title==null)
$title = $spec2." at ".$facility;
$description = html_entity_decode(addslashes($job->description));
$contact = $job->recruiter_name;
$phone = $job->RecruiterPhone;
$email = $job->recruiter_email;
$ldescr = html_entity_decode(addslashes($job->facil_description));
$acity = addslashes($city); $astate = addslashes($state);
$sql = "select l_id from locations where l_acct = $client->acct and status = 1 and l_facility = '$facility' and l_city = '$acity' and l_state = '$astate'";
$res = $db->query($sql);
if( !$res )
throw new Exception(DEBUG?"$db->error : $sql":'Problem with Locations Table',__LINE__);
if( !$res->num_rows ) {
// create new location
$cdescr = html_entity_decode(addslashes($job->city_description));
$sql ="insert into locations (l_facility,l_city,l_state,l_uid,l_acct,l_description,l_commdescr,exp_date) values('$facility','$acity','$astate',$client->uid,$client->acct,'$ldescr','$cdescr',ADDDATE(NOW(), INTERVAL 1 YEAR))";
$result = $db->query($sql);
if( !$result )
throw new Exception(DEBUG?"$db->error: $sql":'Can not insert locations',__LINE__);
$locid = $db->insert_id;
} else
list($locid) = $res->fetch_row();
$res->free();
//create Opportunity
@$opp = new Opportunity($db,0,$locid,$client->uid,$client->acct);
$opp->o_name = $jid;
$opp->o_city = ($acity);
$opp->o_state = ($astate);
$opp->specialty = $spec2;
$opp->o_facility = $facility;
$opp->description = $description;
$opp->exp_date = $client->exp_date;
$opp->practice_type = $spec2 == 'HOS'?'Hosp':($spec == 'EM'?'ER':'');
$opp->o_email = addslashes($email);
$opp->o_phone = addslashes($phone);
$opp->o_contact = addslashes(htmlspecialchars( strip_tags($contact)));
@$opp->save();
$sql = "insert into importrac (jobid,jobacct,jobflag,jobopp) VALUES ('$jid',$client->acct,0,$opp->id) ON DUPLICATE KEY UPDATE jobflag=0";
$db->query($sql);
}
}
print "jobs added!";
}
}
?>
<h1>XML Import</h1>
<form action="import.php" method="post" enctype="multipart/form-data">
<input type="submit" name="submit" value="Run">
</form>
Below is the example of the current XML feed with multiple specialties:
<Opportunity>
<headline>Jobs in Los Angeles, CA</headline>
<specialty>Family Medicine, Internal Medicine, Pediatric - General</specialty>
<city>San Fernando Valley, CA</city>
<city_description></city_description>
<state>California</state>
<facility>Medical Center</facility>
<facil_description></facil_description>
<description></description>
<recruiter_name></recruiter_name>
<recruiter_email></recruiter_email>
<OpportunityId>777</OpportunityId>
<ScanCode></ScanCode>
<recruiter_image></recruiter_image>
<JobNumber>155</JobNumber>
<RecruiterPhone>(503) 555-333</RecruiterPhone>
</Opportunity>
If there is only one specialty value from the XML feed, php script will map it properly: <specialty>Family Medicine</specialty>.
Is it possible to extract multiple specialty values from the XML specialty node above and map it with the current PHP/MySQL code?
UPDATE
Even if I use explode function, couple of jobs were not imported. Please see the list below.
<specialty>Family Medicine, Geriatric Medicine</specialty>
<specialty>Medical Oncology, Surgery - Breast</specialty>
<specialty>Emergency Medicine, Family Medicine</specialty>
<specialty>Internal Medicine, Pediatric - General</specialty>
<specialty>Emergency Medicine, Family Medicine</specialty>
<specialty>Orthopedics - General, Orthopedics - Sports Medicine</specialty>
<specialty>Family Medicine, Urgent Care/Immediate Care</specialty>
<specialty>Family Medicine, Internal Medicine, Pediatric - General</specialty>
<specialty>Family Medicine, Sports Medicine</specialty>
<specialty>Cardiology, Cardiology - Adv Heart Failure/Transplant</specialty>
<specialty>Cardiology, Cardiology - Non-Invasive</specialty>
<specialty>Pediatric - Developmental/Behavioral, Pediatric – Neurodevelopment Disabilities
</specialty>
<specialty>Pediatric - Critical Care Medicine, Pediatric - Intensivist
</specialty>
<specialty>Family Medicine, Internal Medicine, Urgent Care/Immediate Care
</specialty>
<specialty>Family Medicine, Urgent Care/Immediate Care</specialty>
