Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Feb 2011
    Location
    Melbourne, Australia
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Exclamation problem updating my database via PHP

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

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

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    izrafel (05-12-2011)

  • #3
    New Coder
    Join Date
    Feb 2011
    Location
    Melbourne, Australia
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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)...

  • #4
    New Coder
    Join Date
    Aug 2011
    Location
    Melbourne, Brighton 3186
    Posts
    17
    Thanks
    1
    Thanked 2 Times in 2 Posts
    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.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Then look up mysql_error (in the php manual) at a minimum and then do a web search on programming and error handling.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •