douliaopan1419
2017-06-11 04:45
浏览 65

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

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>
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douzhi7082 2017-06-11 06:36
    已采纳

    When you fetch the speciality, you can split it using explode to get the separate parts...

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

    Now $specList will be an array of specialities (if there is only one it will be in $specList[0].

    You then can loop around this array, processing each one at a time...

    for ( $specList as $spec )    {
        switch($spec) {
                        case "Family Medicine":
    
    // The rest of the code that deals with specialities here
    
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题