problem updating my database via PHP

[izrafel]

CMS driven site that I've written a cronjob for to read latest xml file and then insert data into a MySQL database - everything works fine in terms of inserting new entries BUT i can't get it to update old ones - any help appreciated! The RED text in the code below is the portion of code in question that doesn't seem to work...

function display(){		
	
		//die($_SERVER['SCRIPT_FILENAME']);
		
		$file_dir	= JPATH_SITE . DS . 'xml';
		$files_arr	= array();
		
		if ($handle = opendir($file_dir)) {
			//echo "Directory handle: $handle\n";
			//echo "Files:\n";
		
			/* This is the correct way to loop over the directory. */
			while (false !== ($file = readdir($handle))) {
				$files_arr[]	= $file;
			}
		
			closedir($handle);
		}
		
		rsort($files_arr);
		
		$this->saveDataFromXML($files_arr[0]);
		exit();
	}

function saveDataFromXML($fname)
	{
		include (JPATH_COMPONENT . DS . 'assets' . DS . 'xml2array.php');
		
		$file		= JPATH_SITE . DS . 'xml' . DS . $fname;
		
		
		
		$database	=& JFactory::getDBO();

		$contents	= file_get_contents($file);
		$result 	= xml2array($contents, 1, 'attribute');
				
		$database->setQuery("UPDATE #__tst_jglist_postings set `published`='0'");
		$database->query();
		
		## Get the Company/Advertiser
		$company	= $result['Jobs']['attr']['advertiser'];
		if($company){
			$database->setQuery("SELECT * FROM #__tst_jglist_companies WHERE company_code='$company'");
			$row	= $database->loadObject();
			if(count($row)){
				
				$company_id	= $row->id;
			} else {
				$database->setQuery("INSERT INTO #__tst_jglist_companies (`id`,`company_code`,`company`,`published`) VALUES ('','$company','$company',1);");
				$database->query();
				$company_id	= $database->insertid();
			}
		}
		
		$success 	= 0;
		$errors		= 0;
		
		$params		=  "pre_article=0\n".
						"post_article=0\n".
						"display_company_in_header=1\n".
						"display_jobtitle_in_header=1\n".
						"display_posted_on=1\n".
						"display_closing_on=1\n".
						"display_location=1\n".
						"display_loc_description=1\n".
						"display_loc_address=1\n".
						"display_category=1\n".
						"display_department=1\n".
						"display_shift=1\n".
						"display_job_type=1\n".
						"display_education=1\n".
						"display_pay_rate=1\n".
						"display_duration=1\n".
						"display_travel=1\n".
						"display_job_description=1\n".
						"display_preferred_skills=1";
		
		foreach($result['Jobs']['Job'] as $jobs){
			$job_id		= $jobs['attr']['jid'];
			$job_ref	= $jobs['attr']['reference'];
			$job_date	= $jobs['attr']['datePosted'];
			
			$job_title		= mysql_escape_string($jobs['Title']['value']);
			$job_sum		= mysql_escape_string($jobs['Summary']['value']);
			$job_desc		= mysql_escape_string($jobs['Description']['value']);
			
			if(count($jobs['Apply'])){
				
				$job_email		= mysql_escape_string($jobs['Apply']['EmailTo']['value']);
				$job_url		= mysql_escape_string($jobs['Apply']['Url']['value']);
				$contact_id		= $this->checkContactExistAdd($job_email, $company_id, $company);
			}				
			
			if(count($jobs['Salary'])){
				$job_salary		= $jobs['Salary']['MinValue']['value'];
				$job_salary		= $job_salary . " - " . $jobs['Salary']['MaxValue']['value'];
				$job_salary		= mysql_escape_string($job_salary . " " . $jobs['Salary']['attr']['period']);
			}
			
			if(count($jobs['Classifications']['Classification'])){
				foreach($jobs['Classifications']['Classification'] as $class){

					if($class['attr']['name'] == "Category"){
						$job_cat	= $class['value'];
						$job_catid	= $this->checkCategoryExistAdd($job_cat, $company_id);
					}
					
					if($class['attr']['name'] == "Sub Category"){
						$job_subcat	= $class['value'];						
					}
					
					if($class['attr']['name'] == "Location"){
						$job_loc	= $class['value'];
						$job_locid	= $this->checkLocationExistAdd($job_loc, $company_id);
					}
					
					if($class['attr']['name'] == "Work Type"){
						$job_wtype		= $class['value'];
						$job_wtypeid	= $this->checkWorkTypeExistAdd($job_wtype, $company_id);
					}					
				}
			}
			
			if(count($jobs['BulletPoints']['BulletPoint'])){
				$job_addinfo	= "<ul>";
				foreach($jobs['BulletPoints']['BulletPoint'] as $addinfo){
					$job_addinfo	.= "<li>".$addinfo['value']."</li>";				
				}
				$job_addinfo	.= "</ul>";
				$job_addinfo	= mysql_escape_string($job_addinfo);
			}

			$database->setQuery("SELECT * FROM #__tst_jglist_jobs WHERE id='$job_id' AND job_reference='$job_ref'");
			$row	= $database->loadObject();
			
			if(count($row)){
				$database->setQuery("UPDATE #__tst_jglist_jobs SET title='$job_title', category_id='$job_catid', subcategory='$job_subcat', location_id='$job_locid', pay_rate='$job_salary', job_description='$job_desc', summary='$job_sum', published='1' preferred_skills='$job_addinfo' WHERE job_id='$job_id' AND job_reference='$job_ref'");
				$database->query();
								
				$database->setQuery("SELECT * FROM #__tst_jglist_postings WHERE job_id='$job_id'");
				$postrow	= $database->loadObject();
				
				if(count($postrow)){
					
					$database->setQuery("UPDATE #__tst_jglist_postings SET published='1' WHERE job_id='$job_id' AND company_id='{$postrow->company_id}'");
					$database->query();
					
				} else {
					$postingQuery	= "INSERT INTO #__tst_jglist_postings (`id`,`job_id`,`company_id`,`location_id`,`summary`,`posting_date`,`params`,`published`,`contact_id`)".
									  " VALUES ('','$job_id','{$row->company_id}','{$row->location}','{$row->summary}','$job_date','$params','1','$contact_id')";
					$database->setQuery($postingQuery);
					$database->query();
				}
				
				
			} else {
				
				
				$insertQuery	= "INSERT INTO #__tst_jglist_jobs (`id`, `title`, `category_id`, `subcategory`, `location_id`, ".
								  "`jobtype_id`, `company_id`, `pay_rate`, `job_description`, `summary`, `preferred_skills`, ".
								  "`email`, `url`, `published`,`job_reference` ) VALUES ('$job_id', '$job_title', '$job_catid', '$job_subcat', '$job_locid', ".
								  "'$job_wtypeid', '$company_id', '$job_salary', '$job_desc', '$job_sum', '$job_addinfo', ".
								  "'$job_email', '$job_url', '1', '$job_ref')";
				$database->setQuery($insertQuery);
				if($database->query()){
													
					$postingQuery	= "INSERT INTO #__tst_jglist_postings (`id`,`job_id`,`company_id`,`location_id`,`summary`,`posting_date`,`params`,`published`,`contact_id`)".
									  " VALUES ('','$job_id','$company_id','$job_locid','$job_sum','$job_date','$params','1','$contact_id')";
					$database->setQuery($postingQuery);
					$database->query();
				}
											
			}	
			
		}

Many thanks in advance

[Fou-Lu]

Where's the error handling on this?
There is far too much custom stuff in here to determine what the problem is from what you have. You need to handle the errors first to determine why its failing.
Also, MySQL specific there is a REPLACE syntax as well. It will insert when non-existent keys are found, and update on collision (actually, I believe it works by deleting and recreating, so that will affect any triggers for those I'd think). Very handy for shortcuts.

[izrafel]

Thanks for the REPLACE tip
in regards for error handling - i have no idea how to do it in this case (i'm mostly self taught PHP/SQL)...

[M1Creative]

I tend to agree. You need to try and simplyfy this or break it down into more readable functions then there might be some help available that isn't so vague.

[guelphdad]

Then look up mysql_error (in the php manual) at a minimum and then do a web search on programming and error handling.