php - 从 XML 节点中提取多个值并将其映射到数据库

Question

下面的这个 php 脚本从远程 URL 获取 XML 作业源并将字段映射到数据库中。该脚本运行良好，直到昨天，XML 提要中的特殊节点值更改为用逗号分隔的多个值。

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

下面是当前具有多种特性的 XML feed 的示例:

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

如果 XML feed 中只有一个特殊值，php 脚本将正确映射它:<specialty>Family Medicine</specialty> 。

是否可以从上面的 XML 专业节点中提取多个专业值并将其与当前 PHP/MySQL 代码进行映射？

<小时/>

更新

即使我使用爆炸功能，也有几个作业没有导入。请参阅下面的列表。

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

Answer 1

当您获取专业知识时，您可以使用explode将其拆分以获得单独的部分...

$specList = explode(',' addslashes(trim($job->specialty)));

现在 $specList 将是一个专业数组(如果只有一个，它将位于 $specList[0] 中。

然后您可以循环该数组，一次处理每个数组...

for ( $specList as $spec )    {
    switch($spec) {
                    case "Family Medicine":

// The rest of the code that deals with specialities here

}