problem updating mySQL via PHP using UPDATE

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 portion of code I can't seem to make work is

$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();

PHP:

Majority of the code is below for context:

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();
				}
											
			}	
			
		}

PHP:

Many thanks in advance

 

Enable MySQL query logging, execute that page, and read through the logs to see what all the queries are. Chances are, the Update query is returning false because there is a part of the statement that's not existing in the table records.

 

At first glance, it looks like you are simply missing a comma between the bolded terms being updated:

$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'");