Updating DB with Daily XML Feed

PHP
1

I have an XML parsing script that’s operating a bit quirky. It parses a daily xml feed containing jobs to be featured on my career board. The most urgent problem I need fixed is that old jobs are not dropping off. If a job in my db is not present in the new feed it needs to be deactivated. Here is my preliminary code I was hoping to get some outside thoughts on. -Thanks

$doc = new DOMDocument(); //create a new domdocument to sort through xml tree
$doc->load( ‘/path/daily_jobs.xml’ ); //load the daily job feed
$jobAds = $doc->getElementsByTagName( “jobAd” ); //create a new instance for every incurrance of a jobAd

foreach( $jobAds as $jobAd ) { //loop through each job in feed
$externalJobAdIds=$jobAd->getElementsByTagName( “externalJobAdId” ); //capture externalJobAdId nodes and set them to a variable to be used as unique identifier

$newJobs = array(); //create array to hold id’s of the new jobs
$externalJobAdIds = $newJobs; //push id’s into array
$query = “SELECT externalJobAdId FROM jobs WHERE company_id = 8060”; //run query to capture job id’s already present in db
$oldJobs = mysql_query($query); //store results

foreach($oldJobs as $job) {//loop through results
if(!in_array($newJobs)) { //check if old jobs are still present in today’s feed
$query = “DELETE * FROM jobs WHERE externalJobAdId = '” . $job . “'”; //delete job if it is not in today’s feed
mysql_query($query);
}
}
}

2

Sticking my head into the lion’s mouth again… wouldnt it be quicker to gather all the ID’s together - ($newjobs = $job) and then do “DELETE FROM jobs WHERE externalJobAdId NOT IN(<implodedstringofjobtitleshere>)”?

3

Interesting. I wasn’t aware of the NOT IN command. This would make things a lot easier. Here’s my new revision. Any better?

<?php

$doc = new DOMDocument(); //create a new domdocument to sort through xml tree
$doc->load( ‘/path/daily_jobs.xml’ ); //load the daily job feed
$jobAds = $doc->getElementsByTagName( “jobAd” ); //create a new instance for every incurrance of a jobAd

foreach( $jobAds as $jobAd ) { //loop through each job in feed
$externalJobAdIds=$jobAd->getElementsByTagName( “externalJobAdId” ); //capture externalJobAdId nodes and set them to a variable to be used as unique identifier

$newJobs = array(); //create array to hold id's of the new jobs
$externalJobAdIds = $newJobs[]; //push id's into array
$implodedJobs = implode(",",$newJobs);

}

$query = "DELETE FROM `jobs` WHERE `company_id`= 8060 AND `externalJobAdId` NOT IN($implodedJobs)";

?>

4

I went ahead and modified the file…

I’m now getting a fatal error whenever I run my cron manually…

Cannot use [] for reading

It’s caused by this line inside my while loop:

$externalJobAdIds = $newJobs;

I’m not sure how else I can populate my array with the needed values.