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 13 of 13
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts

    RSS Feed to Database?

    Hi all,

    I assume this is in the correct forum, if not can an admin please move it...

    I currently have a company who advertise a number of jobs on my site (non-profit) and to ease their load I am looking at having their jobs automatically uploaded from an RSS feed which they already have up and running...

    What I need to do is take this feed and place the data into my database so it can be called back out with the rest of the jobs.

    I don't have the faintest clue where to even begin, so any advice would be greatly appreciated.

    Many thanks,

    Greens85

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    We will need to see their RSS feed.
    Give us the URL to that.

  • #3
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by mlseim View Post
    We will need to see their RSS feed.
    Give us the URL to that.
    Hi mlseim,

    Thanks for the quick response, here you go:

    http://www.axcis.co.uk/rssuk.aspx

  • #4
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    Here is an example of reading the RSS Feed (XML file),
    and displaying the data. You can control how many
    to view, and how much of it to view (number of characters).

    PHP Code:
    <?php  
    // RSS Jobs Page -  
    $feed_url "http://www.axcis.co.uk/rssuk.aspx";

    # INITIATE CURL. 
    $curl curl_init(); 

    # CURL SETTINGS. 
    curl_setopt($curlCURLOPT_URL,"$feed_url"); 
    curl_setopt($curlCURLOPT_RETURNTRANSFER1); 
    curl_setopt($curlCURLOPT_CONNECTTIMEOUT0); 

    # GRAB THE XML FILE. 
    $xmlFile curl_exec($curl); 

    curl_close($curl); 

    # SET UP XML OBJECT.
    # Use either one of these, depending on revision of PHP.
    # Comment-out the line you are not using.
    //$xml = new SimpleXMLElement($xmlFile);
    $xml simplexml_load_string($xmlFile); 

    // How many items to display 
    $count 25

    // How many characters from each item 
    // 0 (zero) will show them all. 
    $char 500

    foreach (
    $xml->channel->item as $item) { 
    if(
    $char == 0){ 
    $newstring $item->description

    else{ 
    $newstring substr($item->description0$char); 

    if(
    $count 0){ 
    //in case they have non-closed italics or bold, etc ... 
    echo"</i></b></u></a>\n"
    echo

    <div style='font-family:arial; font-size:.8em;'>  
    <b>{$item->title}</b><br />
    $newstring ... <a href='{$item->guid}'>read more</a> 
    <br /><br /> 
    </div> 
    "
    ;  

    $count--; 
    }  
    ?>
    This is what the output looks like:
    http://www.catpin.com/jobs.php

    Now, with some tweaking, you can determine what data to collect,
    and how to display it. Then, when that's figured out, instead of
    displaying it, we need to get it into your database ....
    So we'll need information about your database.
    database name
    table name
    column names
    etc.




    .
    Last edited by mlseim; 06-24-2010 at 09:58 PM.

  • #5
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Hi,

    Apologies for the very delayed response...

    The company have now actually given me a pure XML feed.

    What impact does this have, I would imagine it makes things simpler?

    The XML feed can be seen here:

    http://axcis.klenka.com/rss.aspx

    many thanks,

    Greens85

  • #6
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    It's the very same PHP script, with a few modifications:

    Here is my example script:
    PHP Code:
    <?php  
    // RSS Jobs Page -  
    $feed_url "http://axcis.klenka.com/rss.aspx";

    # INITIATE CURL. 
    $curl curl_init(); 

    # CURL SETTINGS. 
    curl_setopt($curlCURLOPT_URL,"$feed_url"); 
    curl_setopt($curlCURLOPT_RETURNTRANSFER1); 
    curl_setopt($curlCURLOPT_CONNECTTIMEOUT0); 

    # GRAB THE XML FILE. 
    $xmlFile curl_exec($curl); 

    curl_close($curl); 

    # SET UP XML OBJECT.
    # Use either one of these, depending on revision of PHP.
    # Comment-out the line you are not using.
    //$xml = new SimpleXMLElement($xmlFile);
    $xml simplexml_load_string($xmlFile); 

    // How many items to display 
    $count 25

    // How many characters from each item 
    // 0 (zero) will show them all. 
    $char 1000

    foreach (
    $xml->job as $item) { 
    if(
    $char == 0){ 
    $newstring $item->description

    else{ 
    $newstring substr($item->description0$char); 

    if(
    $count 0){ 
    //in case they have non-closed italics or bold, etc ... 
    echo"</i></b></u></a>\n"
    echo

    <div style='font-family:arial; font-size:.8em;'>  
    Title: <b>{$item->title}</b><br />
    Location: <b>{$item->location}</b><br />
    Job Type: <b>{$item->Jobtype}</b><br />
    Full/Part: <b>{$item->Full_Part}</b><br />
    Salary: <b>{$item->Salary}</b><br />
    $newstring ... <a href='{$item->url}'>read more</a> 
    <br /><br /> 
    </div> 
    "
    ;  

    $count--; 
    }  
    ?>
    This is the working example of the script output ...
    http://www.catpin.com/jobs.php

    Now, you need to put that data into your database.
    One thing to be aware of ... they cut-off their descriptions.
    That means, you might not ever be able to show the complete
    description for each job ... but a user can click on a link to
    go directly to their site for the full description.



    .

  • #7
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Hi mlseim,

    Many thanks for that, really really useful.

    I have managed to get it into the database with the following code:

    PHP Code:
    $title $item->title;
    $location $item->location;
    $jobtype $item->Jobtype;
    $hours $item->Full_Part;
    $salary $item->Salary;  

    $qc "SELECT jobid FROM jobs ORDER BY jobid DESC";
        
    $rc mysql_query($qc) or die (mysql_error());
            
    $ac mysql_fetch_array($rc);
                
    $jobid $ac[0] + 1;

    $query "INSERT INTO jobs (jobid, employerid, position, country, contract, hour, salary, description) 
              VALUES ('$jobid', '117', '$title', '$location', '$jobtype', '$hours', '$salary', '$newstring')"
    ;
        
    $result mysql_query($query) or die (mysql_error()); 
    I have a slight problem in that some of their values don't match my options.. as an example:

    Their value for a full time position is expressed as Full-Time whereas my is simply Full Time... which would normally be selected from a drop down.

    There are a number of other aswell;

    London - SW (theirs) :: London (mine)
    Contract (theirs) :: Permanent or Temporary (mine)
    Part-Time (theirs) :: Part Time (mine)

    This obviously means that those particular jobs won't display under search results etc. Is this something I can do something about with the PHP? Or would they have to change the values they were sending?

    They are also not providing the same amount of information that they do once you click though to the job, but I guess this is something that they have decided upon, and can't be fixed with PHP?

    Many thanks,

    Greens85

  • #8
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    Do all the adjustments before you save it in your database ...

    Sort of like this ....

    PHP Code:
    $title $item->title;

    // convert Job Type text.
    $jobtype="Permanent";
    $xjobtype $item->Jobtype;
    if(
    $xjobtype == "Contract"){
    $jobtype="Temporary";
    }

    // convert Location text.
    $xlocation $item->location;
    $location=$xlocation;
    if(
    strpos($xlocation,"London")){
    $location="London";
    }
    if(
    strpos($xlocation,"England")){
    $location="England";
    }

    // convert Full/Part time text.
    $xhours $item->Full_Part;
    $hours="Part Time";
    if(
    $xhours == "Full-Time"){
    $hours="Full Time";
    }

    $salary $item->Salary;  

    $qc "SELECT jobid FROM jobs ORDER BY jobid DESC";
        
    $rc mysql_query($qc) or die (mysql_error());
            
    $ac mysql_fetch_array($rc);
                
    $jobid $ac[0] + 1;

    $query "INSERT INTO jobs (jobid, employerid, position, country, contract, hour, salary, description) 
              VALUES ('$jobid', '117', '$title', '$location', '$jobtype', '$hours', '$salary', '$newstring')"
    ;
        
    $result mysql_query($query) or die (mysql_error()); 

    EDIT:
    And yes, you can only deal with the information they are giving you.


    and I made a couple of typos ... so the script above is fixed.




    .
    Last edited by mlseim; 06-29-2010 at 02:54 PM.

  • #9
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Thanks for that...

    Taking the location one as an example, how would I add many other locations to that:

    Basically I need to convert all of the following to simply 'London';

    London - Central
    London - East
    London - North
    London - NW
    London - SE
    London - SW
    London - West

    Would it be something like so:

    PHP Code:
    // convert Location text.
    $xlocation $item->location;
    $location=$xlocation;
    if(
    strpos($xlocation,"London")){
    $location="London";
    }

    if(
    strpos($xlocation,"London - Central")) {
    $location="London";
    }

    if(
    strpos($xlocation,"London - East")) {
    $location="London";

    etc...

    Or have I completely misunderstood that!

  • #10
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    The part below should work as-is.
    What it does is simply look for the string "London" within the location name.
    It doesn't care what else is in the string ... only cares about the word "London".
    If it finds the string (which would make it true), it simply makes it "London".

    Are you saying this isn't working?

    PHP Code:
    // convert Location text.
    $xlocation $item->location;
    $location=$xlocation;
    if(
    strpos($xlocation,"London")){
    $location="London";
    }
    // do the same thing with England ...
    if(strpos($xlocation,"England")){
    $location="England";

    Last edited by mlseim; 06-29-2010 at 05:37 PM.

  • #11
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by mlseim View Post
    The part below should work as-is.
    What it does is simply look for the string "London" within the location name.
    It doesn't care what else is in the string ... only cares about the word "London".
    If it finds the string (which would make it true), it simply makes it "London".

    Are you saying this isn't working?

    PHP Code:
    // convert Location text.
    $xlocation $item->location;
    $location=$xlocation;
    if(
    strpos($xlocation,"London")){
    $location="London";
    }
    // do the same thing with England ...
    if(strpos($xlocation,"England")){
    $location="England";

    Ahhh, no I wasn't saying it wasn't working I was just wondering how I would add several conditions to it (if that makes sense)... I have however got it working this way, which may not be as good as your way.
    PHP Code:
    // create a function to map fields
    function do_replace($replace,$subject){
        foreach(
    $replace as $key=>$val){
            
    $subject str_replace($key,$val,$subject);
        }

        return 
    $subject;
    }

        
    // create an array to convert values
        
    $replace = array(
        
    'Buckinghamshire' => 'South East',
        
    'Essex' => 'Eastern',
        
    'Hertfordshire' => 'South East',
        
    'London - Central' => 'London',
        
    'London - East' => 'London',
        
    'London - North' => 'London',
        
    'London - NW' => 'London',
        
    'London - SE' => 'London',
        
    'London - SW' => 'London',
        
    'London - West' => 'London',
        
    'North West England' => 'North West',
        
    'Surrey' => 'South East',
        
    'Part-Time' => 'Part Time',
        
    'Full-Time' => 'Full Time',
        
    'Contract' => 'Temporary',
        
    'Permanent' => 'Permanent'
        
    );

    $location do_replace($replace,$location);
    $hours do_replace($replace,$hours);
    $jobtype do_replace($replace,$jobtype); 
    As I say it works which will do for me, however I do appreciate it may not be the most efficient way of doing it.

  • #12
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    Your way is good for you ... because you have specific, unique conversions
    that don't follow general string matching. So keep what you have ... if it works,
    then it's good to go.

  • #13
    New to the CF scene
    Join Date
    Jul 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm having issues fetching details from an xml and inserting them to our database.file loaded in the website. i use this for tracking purposes

    Code:
    <?php
    	
    	require_once('db.php');
    	
    	$fileName = 'ems.xml';
    
    	if (file_exists($fileName)) {
    		$xml = simplexml_load_file($fileName);
    	} else {
    		exit('Failed to open ems.xml.');
    	}
    
    	
    	$icounter = 0;
    	$ecounter = 0;
    	$icount = 0;
    	$ecount = 0;
    	$unloaded = array();
    	$ui = 0;
    	
    	$fld1 = array();
    	$fld2 = array();
    
    	
    	foreach($xml->Item as $ikey => $Item) {
    		
    		$item = get_object_vars ($Item);
    		
    		$Id = '';
    		$MailClass = '';
    		$Content = '';
    		$Weight = '';
    		$Currency = '';
    		$Value = '';
    		$OrigCountryCd = '';
    		$DestCountryCd = '';
    		$PostalStatusCd = '';
    		
    		
    		extract($item,EXTR_OVERWRITE);
    		
    		$Id =  $item['@attributes']['Identifier'];
    		
        	$Currency = get_object_vars($Currency);
    		$Value = get_object_vars($Value);
    
    		$Currency = (count($Currency) == 1) ? $Currency[0] : ''; 
    		$Value = (count($Value) == 1) ? $Value[0] : ''; 
    
    
    		$sql = "INSERT INTO `item` (`Id`,`MailClass`,`Content`,`Weight`,`Currency`,`Value`,`OrigCountryCd`,`DestCountryCd`,`PostalStatusCd`)
    				VALUES ('$Id','$MailClass','$Content','$Weight','$Currency','$Value','$OrigCountryCd','$DestCountryCd','$PostalStatusCd')";
    		
    		$result = mysql_query($sql);
    		$icount++;
    		$ItemId = $Id;
    		
    		$afrows = mysql_affected_rows();
    		
    		if ( $afrows > 0 ) {
    			
    			$icounter += $afrows;
    			
    			foreach ($Event as $ekey => $Evt) {
    				
    				$Code = '';
    				$Date = '';
    				$OfficeCd = '';
    				$OperatorName = '';
    				$ConditionCd = '';
    				$RetentionReasonCd = '';
    				$DeliveryAgentCd = '';
    				$NonDeliveryReasonCd = '';
    				$NonDeliveryMeasureCd = '';
    				$SignatoryNm = '';
    				$DeliveryLocation = '';
    				
    				$event = get_object_vars($Evt);
    				extract($event,EXTR_OVERWRITE);
    	
    				$Date = date('YmdHis', strtotime($Date));
    				
    				$sql = "INSERT INTO `event` (`Code`,`Date`,`OfficeCd`,`OperatorName`,`ConditionCd`,`ItemId`,
    					   `RetentionReasonCd`,`DeliveryAgentCd`,`NonDeliveryReasonCd`,`NonDeliveryMeasureCd`,`SignatoryNm`,`DeliveryLocation`) 
    						VALUES ('$Code','$Date','$OfficeCd','$OperatorName','$ConditionCd','$ItemId',
    						'$RetentionReasonCd','$DeliveryAgentCd','$NonDeliveryReasonCd','$NonDeliveryMeasureCd','$SignatoryNm','$DeliveryLocation')";
    				
    				$result = mysql_query($sql);
    								
    				$ecount++;
    				if ( mysql_affected_rows() > 0 ) {
    					$ecounter += mysql_affected_rows();
    				}
    				
    			}
    			
    		} else {
    			
    			if (mysql_errno() != 1062) 
    				$unloaded[$ui++] = $Id;
    			
    		}
    		
    	}
    
    
    	echo "XML File contains $icount items and $ecount events <br> Loaded $icounter items and $ecounter events... <BR><br><hr>";
    	
    	$unloaded = array_unique($unloaded);
    	
    	if (count($unloaded) > 0) {
    		echo 'Unloaded Items <br>';
    	
    		foreach($unloaded as $u) {
    			echo "$u <br>";
    		}
    	}
    ?>


  •  

    Posting Permissions

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