jobberBase Community - XML import in to Jobberbase MySql database tool

Re: XML import in to Jobberbase MySql database tool

2011-04-21T07:49:08Z

Did you make any changes other than the sql file?

Re: XML import in to Jobberbase MySql database tool

2011-04-19T19:59:28Z

Hi ... I have the feeds script installed here: http://www.atheistlist.com/jobberbase/simplefeeds_cronjob.php It runs ok but shows no jobs listed. I have checked to make sure there are jobs listed in at least some of the categories via my rss reader. Any help is appreciated. Thanks Brenda

Re: XML import in to Jobberbase MySql database tool

2010-10-11T11:40:48Z

Hello guys, I am looking for job wrapping tool, that can post/add all the new jobs from the portals everyday with a scheduler. The tool should post the openings in the database. Pls help, its urgently needed. Thanks aishwinv@gmail.com

Re: XML import in to Jobberbase MySql database tool

2010-09-09T18:19:17Z

For jobberbase to jobberbase import code, some changes are needed to make it work on jobberbase 1.9.1. The changes are related to how new version deals with location fields. To avoid extra log post, help you out and shamelesly promote some links I created a small website where you can download the jobberBase to jobberBase rss code for jobberBase version 1.6 and version 1.9.1. Check it out: [url=http://jobfeeds.me]jobfeeds.me[/url]

Re: XML import in to Jobberbase MySql database tool

2010-09-08T15:44:52Z

simple. you use your admin email as the poster email and then you specify 0 on the apply online option. this way, applications will not be sent to your email but rather applicants will use the link on the job post, if they want to apply for a job.

Re: XML import in to Jobberbase MySql database tool

2010-09-08T12:01:42Z

[quote=Mario]For those who talked about jobberbase-to-jobberbase import, how can this be possible while the company email is hidden? It's required for job applications isn't it?[/quote] Looks like my question is quite general and not only about importing from other jobberbase sites. How do you people import jobs from feeds that dont share companies' email? (like indeed's and simplyhired's)

Re: XML import in to Jobberbase MySql database tool

2010-09-08T11:50:03Z

For those who talked about jobberbase-to-jobberbase import, how can this be possible while the company email is hidden? It's required for job applications isn't it?

Re: XML import in to Jobberbase MySql database tool

2010-09-08T09:31:15Z

Hello All, Not posted on here for a long time now. I can provide one time backfills for your new jobboard in the UK via a simple CSV upload. As if you are just looking to kick start your site, then I can upload anything upto about 10,000 jobs in one go. This just saves the hassle of having to recode any of your site. I started up Jobberfeed.com but never really took it to much further. But new sites are reguarly being indexed on there to be included on the feeds. Problem I found with working with XML is the amount of server capacity it took up. So if you are running on a Shared Host, like JustHost, One.Com ,GoDaddy etc. Likelyhood is, at some point they will shut your site down as you have maxed out the capacity of the processor on the server (Trust me this has happened a few times, when I was developing the code that imported and distrubuted the feeds). How I plan on making it work, is simply providing login to Jobberfeed and on a weekly basis uploading the new CSV file on the server ready for download. There will be a small price for the service. One time back fill (download link will last 1 week) - £5 Monthly Unlimited Download (file updated weekly) - £10 a month (Paypal Subscription). On average the files should contain around 15,000 jobs and will be formated in a jobberbase format. Only the Category field will need to be updated. The service will be ready in about 2 weeks. So drop me an email or send a PM if interested. Cheers, SteveSPI

Re: XML import in to Jobberbase MySql database tool

2010-07-25T16:06:28Z

hi, in case anyone is still looking for solution for this...i now have an available add-on to backfill your jobberBase job board with feeds from Indeed.com and SimplyHired.com, which you can read about here: http://www.redjumpsuit.net/2010/07/25/backfill-your-job-board-with-indeed-and-simplyhired/

Re: XML import in to Jobberbase MySql database tool

2010-02-08T15:44:31Z

It's pleasing to see that this discussion is still quite active... I saw this listed elsewhere online (http://www.shearersoftware.com/software/web-tools/wordpress-rss-import/1.0/bootleg-import-rss.php.html) about importing XML into Wordpress... and thought someone else might be able to adapt this for Jobberbase? Any takers?! Thanks, Matt ''); $currentPost = null; $currentText = ''; function parseDateISO8601($input) { // returns the date in SQL (MySQL, at least)-compatible text format return substr($input, 0, 10) . ' ' . substr($input, 11, 8); } function parseDateRFC822($input) { // returns the date in SQL (MySQL, at least)-compatible text format return strftime('%Y-%m-%d %H:%I:%S', strtotime($input)); } function startElement($parser, $name, $attrs) { global $currentPost, $currentText, $currentGuidAttrs; if ($name == 'item') { $currentPost = new post(); $currentPost->categories = array(); } elseif ($name == 'guid') { $currentGuidAttrs = $attrs; } $currentText = ''; } function endElement($parser, $name) { global $currentPost, $currentText; switch ($name) { case 'title': case 'http://www.w3.org/1999/02/22-rdf-syntax-ns# title': $currentPost->title = $currentText; break; case 'content:encoded': case 'http://purl.org/rss/1.0/modules/content/ encoded': $currentPost->content = $currentText; break; case 'description': case 'http://www.w3.org/1999/02/22-rdf-syntax-ns# description': // content:encoded trumps description, so only save the description // if there's no content already if (!isset($currentPost->content) || !strlen($currentPost->content)) { $currentPost->content = $currentText; } break; case 'pubDate': $currentPost->createDate = parseDateRFC822($currentText); break; case 'dc:date': case 'http://purl.org/dc/elements/1.1/ date': $currentPost->createDate = parseDateISO8601($currentText); break; case 'dcterms:modified': case 'http://purl.org/dc/terms/ modified': $currentPost->modDate = parseDateISO8601($currentText); break; case 'category': case 'dc:subject': case 'http://purl.org/dc/elements/1.1/ subject': $currentPost->categories[] = $currentText; break; case 'guid': if (isset($currentGuidAttrs['isPermaLink']) && $currentGuidAttrs['isPermaLink'] == 'true') { $currentPost->permalink = $currentText; } $currentPost->guid = $currentText; break; case 'item': case 'http://www.w3.org/1999/02/22-rdf-syntax-ns# item': processPost($currentPost); $currentPost = null; break; } $currentText = ''; } function characterData($parser, $data) { global $currentText; $currentText .= $data; } // WordPress-specific code $post_author = 'admin'; require_once('../wp-config.php'); require_once(ABSPATH.WPINC.'/template-functions.php'); require_once(ABSPATH.WPINC.'/functions.php'); require_once(ABSPATH.WPINC.'/vars.php'); if ($kCreateModDateField && !$kTakeNoAction) { require_once(ABSPATH.'/wp-admin/install-helper.php'); $res = ''; $tablename = $tableposts; $ddl = "ALTER TABLE $tableposts ADD COLUMN post_modified datetime"; maybe_add_column($tablename, 'post_modified', $ddl); if (check_column($tablename, 'post_modified', 'datetime')) { $res .= $tablename . ' - ok
'."\n"; } else { $res .= 'There was a problem with ' . $tablename . '
'."\n"; //++$error_count; } echo $res; } function processPost(&$post) { global $kSetModDateField, $kUpdatePostsAlways, $kUpdatePostsIfNewer, $kTakeNoAction; //print_r($post); // Filter out (ignore) posts having categories that are all listed as "excluded" // If a post has no categories, or at least one non-excluded category, it is still // included. if (sizeof($post->categories)) { $gotIncludedCategory = false; foreach ($post->categories as $categoryName) { if (!isset($kExcludedCategories[$categoryName])) { $gotIncludedCategory = true; break; } } if (!$gotIncludedCategory) { return; } } global $post_author, $kExcludeCategories; global $wpdb; global $tableusers, $tableposts, $tablepost2cat, $tablecategories; $post_author_ID = $wpdb->get_var("SELECT ID FROM $tableusers WHERE user_login = '$post_author'"); $post_content = $post->content; $post_content = str_replace('
', '
', $post_content); // XHTMLify
tags /* Un-word-wrap the content, because
tags will be added at display time for line breaks, and RSS feeds are often already soft-wrapped. Replace \n and \r with spaces. However, we don't want to remove word wrapping inside

 tags. Stopping short
    of a full HTML parser, we only un-wrap those sections not inside  tag pairs.
    (This code could be misled by things that look like  tags wrapped in HTML comments,
    but oh well.)
    */
    /*$pos = $lastpos = 0;
    while ($lastpos !== false && ($pos = strpos($post_content, '', $lastpos)) !== false) {
        $post_content = substr($post_content, 0, $lastpos)
            . str_replace("\n", ' ', str_replace("\r", ' ', substr($post_content, $lastpos, $pos - $lastpos)))
            . substr($post_content, $pos);
        $lastpos = strpos($post_content, '', $pos);
    }
    if ($lastpos !== false) {
        $post_content = substr($post_content, 0, $lastpos)
            . str_replace("\n", ' ', str_replace("\r", ' ', substr($post_content, $lastpos)));
    }
    */
    
    $post_content = addslashes($post_content);
    
    #$post_content = str_replace("\r", ' ', $post_content);
    #$post_content = str_replace("\n", ' ', $post_content);
    $post_date = addslashes($post->createDate);
    $post_title = addslashes($post->title);
    $post_modified = $kSetModDateField ? addslashes($post->modDate) : '';
    $post_name = '';
    if (isset($post->permalink) && strlen($post->permalink)) {
        $matches = array();
        if (preg_match('|/[0-9]{4}/[0-9]{2}/[0-9]{2}/([A-Za-z0-9_-]*)/?|', $post->permalink, $matches)) {
            $post_name = $matches[1];
            $post_name = mysql_escape_string($post_name);
        }
    }
        
    
    $categoryIDList = array();
    foreach ($post->categories as $categoryName) {
        if (isset($kExcludedCategories[$categoryName])) {
            continue;
        }
        $categoryID = $wpdb->get_var("SELECT cat_ID FROM $tablecategories WHERE cat_name = '".mysql_escape_string($categoryName)."'");
        if (!$categoryID) {
            if ($kTakeNoAction) {
                echo "Would have inserted new category '$categoryName'.";
                $categoryID = 0;
            }
            else {
                $categoryNiceName = sanitize_title($categoryName);
                $wpdb->query("INSERT INTO $tablecategories
                    (cat_name, category_nicename)
                  VALUES
                    ('".mysql_escape_string($categoryName)."','".mysql_escape_string($categoryNiceName)."')");
                $categoryID = $wpdb->get_var("SELECT LAST_INSERT_ID()");
            }
        }
        else {
            // category already exists; could update its nicename here if it tended not to be correct already.
            //$wpdb->query("UPDATE $tablecategories SET category_nicename = '".mysql_escape_string(sanitize_title($categoryName))."' WHERE cat_ID = ".intval($categoryID));
        }
        $categoryIDList[] = $categoryID;
    }
    
    
    // Quick-n-dirty check for dups:
    if ($kUpdatePostsIfNewer) {
        $dupcheck = $wpdb->get_results("SELECT ID,post_date,post_title,post_modified FROM $tableposts WHERE post_date='$post_date' AND post_title='$post_title' LIMIT 1",ARRAY_A);
    }
    else {
        $dupcheck = $wpdb->get_results("SELECT ID,post_date,post_title FROM $tableposts WHERE post_date='$post_date' AND post_title='$post_title' LIMIT 1",ARRAY_A);
    }
    if ($dupcheck[0]['ID']) {
        // post already exists
        if ($kUpdatePostsAlways || ($kUpdatePostsIfNewer && $kSetModDateField && $dupcheck[0]['post_modified'] < $post_modified)) {
            print "
\n\nUpdating post, ID = '" . $dupcheck[0]['ID'] . "'
\n";
            print "Timestamp: " . $post_date . "
\n";
            print "Post Title: '" . stripslashes($post_title) . "'
\n";
            if (!$kTakeNoAction) {
                $postID = $dupcheck[0]['ID'];
                $result = $wpdb->query("
                UPDATE $tableposts 
                    SET post_author = '$post_author_ID', post_date = '$post_date', 
                    ".($kSetModDateField ? "post_modified = '$post_modified', " : "")."
                    post_content='$post_content',
                    post_title = '$post_title', post_name = '$post_name' WHERE ID = ".intval($postID));
                //echo "DELETE FROM $tablepost2cat WHERE post_id = ".intval($postID);
                $result = $wpdb->query("DELETE FROM $tablepost2cat WHERE post_id = ".intval($postID));
                foreach ($categoryIDList as $categoryID) {
                    $result = $wpdb->query("
                        INSERT INTO $tablepost2cat 
                            (post_id, category_id)
                        VALUES 
                            (".intval($postID).",".intval($categoryID).")
                        ");
                }
            }
        }
        else {  
            print "
\n\nSkipping duplicate post, ID = '" . $dupcheck[0]['ID'] . "'
\n";
            print "Timestamp: " . $post_date . "
\n";
            print "Post Title: '" . stripslashes($post_title) . "'
\n";
        }
    }
    else {
        print "
\nInserting new post.
\n";
        print "Timestamp: " . $post_date . "
\n";
        print "Post Title: '" . stripslashes($post_title) . "'
\n";
        if (!$kTakeNoAction) {
            $result = $wpdb->query("
            INSERT INTO $tableposts 
                (post_author,post_date,post_content,post_title,post_name,post_category".($post_modified ? ",post_modified" : "").")
            VALUES 
                ('$post_author_ID','$post_date','$post_content','$post_title','$post_name','1'".($post_modified ? ",'$post_modified'" : "").")
            ");
            $postID = $wpdb->get_var("SELECT LAST_INSERT_ID();");
            if ($postID) {
                foreach ($categoryIDList as $categoryID) {
                    $result = $wpdb->query("
                    INSERT INTO $tablepost2cat 
                        (post_id, category_id)
                    VALUES 
                        (".intval($postID).",".intval($categoryID).")
                    ");
                }
            } 
        }
    }
}

// XML parsing code
function importRSSFile($filePath) {
    if (function_exists('xml_parser_create_ns')) {
        $xml_parser = xml_parser_create_ns('iso-8859-1',' ');    // space sep for namespace URI
    }
    else {
        $xml_parser = xml_parser_create();
    }
    // make sure to turn off case-folding; XML 1.0 is case-sensitive
    xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, false);
    xml_set_element_handler($xml_parser, "startElement", "endElement");
    xml_set_character_data_handler($xml_parser, "characterData");
    if (!($fp = fopen($filePath, "r"))) {
        die("could not open XML input");
    }
    
    while ($data = fread($fp, 4096)) {
        if (!xml_parse($xml_parser, $data, feof($fp))) {
            die(sprintf("XML error: %s at line %d",
                        xml_error_string(xml_get_error_code($xml_parser)),
                        xml_get_current_line_number($xml_parser)));
        }
    }
    xml_parser_free($xml_parser);
    fclose($fp);
}

function importBlogArchive($dirPath) {
    $startYear = 1980;
    $endYear = intval(strftime('%Y'));
    for ($testYear = $startYear; $testYear <= $endYear; $testYear++) {
        for ($testMonth = 1; $testMonth <= 12; $testMonth++) {
            $rssFilePath = $dirPath.'/'.$testYear.'/'.($testMonth < 10 ? '0' : '').$testMonth.'.xml';
            if (is_file($rssFilePath)) {
                importRSSFile($rssFilePath);
            }
        }
    }
}


if (is_dir($path)) {
    importBlogArchive($path);
}
else {
    importRSSFile($path);
}

/*echo '';
print_r($EZSQL_ERROR);
echo '';
*/

?>

Re: XML import in to Jobberbase MySql database tool

2010-01-27T22:09:53Z

Hey all, I thought this discussion was dead but I did something wrong with the instant e-mail notification. Anyway I've received a possible solution for importing XML feeds in your database. The method is Shell scripting and I'm now trying to make it all work with my site. Does anybody got any experience with this? When I've got it all working I will notify you

Re: XML import in to Jobberbase MySql database tool

2009-11-15T11:31:32Z

Currently design of jobberBase is such that it does not support addons. v2.0 will probably contain the plugin system, so you will have to wait until that if you are looking for a addon for this. Until then, you will have to hack the core files to integrate RSS feeds into jobberBase.

Re: XML import in to Jobberbase MySql database tool

2009-11-14T21:40:59Z

I'm a newbie and am looking for a job aggregator - the design of jobberbase is fantastic and would look great as an aggregator. I believe a 'plugin' that would allow RSS feeds to display jobs from other sources (with other features) would be useful. Just an idea for you guys, I know you are discussing this in the thread about hacking the code but something easy to add-on is a must.

Re: XML import in to Jobberbase MySql database tool

2009-11-12T20:27:22Z

How to get content from more than one website? You can already do this, since you can get rss feeds in correct format from any jobberbase powered website, but for sites not powered by jobberbase some parameters will be in different places. For example, you could have the location showing in feed title instead of feed content, this would prevent you from sorting these jobs by city in your jobberbase since the default code would not store this data in your database. The script currently gets location by looking for anything in the description field that is between the "Location:" and the first "
. This may not be the case for all feeds and that is why modifications are needed. One way to work around this is to create a new table in your database, you can use same code as before, just change table name for each new feed format you wish to add, and also make new simplefeeds_cronjob.php file that would be customized to the new feed format you wish to get, you could call it simplefeeds_cronjob2.php, simplefeeds_cronjob3.php What needs changing in the cron job code for getting new feed format? For new table you need a new name so in simplefeeds_cronjob2.php replace this line: $sql = 'SELECT * FROM feed_db WHERE is_active = 1'; with: $sql = 'SELECT * FROM second_feed_db WHERE is_active = 1'; Where you replace the "second_feed_db" with the table name you assigned to the new table. The get_string_between function can get the data you need to find in most cases. Below is the piece of the code from simplefeeds_cronjob2.php that needs modifying. [code] $permalink = $item->get_permalink(); $title = addslashes(trim($item->get_title())); $rssdate = $item->get_date(); $content = addslashes(trim($item->get_description())); $location = trim(strip_tags($todb->get_string_between($content, "Location:", "
"))); $url = $permalink; $desc = strip_tags(trim($content)); [/code] When modification are done just setup your two cron jobs so you can get your two different feeds types. You can see if it works by visiting your cron jobs at: http://example.com/simplefeeds_cronjob.php http://example.com/simplefeeds_cronjob2.php And then you visit your website to see if it worked. Only problems I run into with this code is when I entered same rss feed url in the database table twice, so don't. If something needs clarifying, or someone has a better idea on how to do this, feel free to contribute.

Re: XML import in to Jobberbase MySql database tool

2009-11-08T07:08:04Z

Hi Dre, Relax, It took some time to figure this one out for me, but it was quite simple solution. This code is tested and works on 1.6, and it is for jobberbase to jobberbase transfer. It is not complete or bulletproof as it could use some more improvements. Also can grab any other feed from any website but styling and format may need adjusting. Feel free to ask any questions here if you need help. All instructions from above are the same except the 2 php files. 1 The code below is from simplefeeds_cronjob.php that goes to root app folder change it to: [code] * @license You are free to edit and use this work, but it would be nice if you always referenced the original author ;) * (see license.txt). */ require_once 'config.php'; require_once '_includes/simplepie.inc'; require_once '_includes/class.FeedToDB.php'; // select all feeds that are active $sql = 'SELECT * FROM feed_db WHERE is_active = 1'; $result = $db->query($sql); while ($row = $result->fetch_assoc()) { if (isset($row['url'])) { $url = $row['url']; $feed = new SimplePie(); $todb = new FeedToDB(); $data = array(); $feed->set_feed_url($url); $success = $feed->init(); $feed->handle_content_type(); // default starting item $start = 0; // default number of items to display. 0 = all $length = 0; // if single item, set start to item number and length to 1 /* if(isset($_GET['item'])) { $start = $_GET['item']; $length = 10; } */ // set item link to script uri $link = $_SERVER['REQUEST_URI']; if ($success) { $type_id = $row['type_id']; $category_id = $row['category_id']; $company = $row['company']; $poster_email = $row['poster_email']; $database = $row['database']; // loop through items foreach($feed->get_items($start,$length) as $key=>$item) { // set query string to item number $queryString = '?item=' . $key; // if we're displaying a single item, set item link to itself and set query string to nothing if(isset($_GET['item'])) { $link = $item->get_link(); $queryString = ''; } // display item title and date echo '' . $item->get_title() . ''; echo ' '.$item->get_date('j M Y, H:i:s O').'
'; echo ' '.$item->get_content().'
'; echo ' '.$item->get_permalink().'
'; echo '
'; $permalink = $item->get_permalink(); $title = addslashes(trim($item->get_title())); $rssdate = $item->get_date(); $content = addslashes(trim($item->get_description())); $location = trim(strip_tags($todb->get_string_between($content, "Location:", "
"))); $url = $permalink; $desc = strip_tags(trim($content)); /* echo "loc:" .$location ."
"; echo "url:" .$url ."
"; echo "desc:" .$desc ."
"; */ $data = array('database' => $database, 'type_id' => $type_id, 'category_id' => $category_id, 'title' => $title, 'description' => $desc, 'company' => $company, 'url' => $url, 'location' => $location, 'created_on' => $rssdate, 'poster_email' => $poster_email); $todb->save($data); } } else { // Check to see if there are more than zero errors (i.e. if there are any errors at all) if ($feed->error()) { // If so, start a

element with a classname so we can style it. echo '

' . "\r\n"; // ... and display it. echo '

' . htmlspecialchars($feed->error()) . "

\r\n"; // Close the

element we opened. echo '

' . "\r\n"; } } } echo "Feed upload for ". $row['database'] ." - ". $row['category_label'] ." completed.

"; } ?> [/code] And the class.FeedToDB.php in the _includes folder should be changed to: [code] * * FeedToDB class handles feeding RSS/XML feed to MySQL * Thanks to SimplePie :) * */ class FeedToDB { function __construct() { } public function save($data) { global $db; // category $dbname = $data['database']; $category_id = $data['category_id']; /* if (!is_numeric($category_id) && strstr($category_id, "|")) { $cats = array(); $cats = explode("|", $category_id); $dbname = trim($cats[0]); $category_id = trim($cats[1]); } */ $sqldup = 'SELECT * FROM '. $dbname.'.jobs WHERE url = "'. trim($data['url']) .'"'; $resultdup = $db->query($sqldup); $rowdup = $resultdup->fetch_assoc(); if (!$rowdup) { $sqlcity = 'SELECT id FROM '. $dbname.'.cities WHERE name LIKE "%'. $data['location'] .'%"'; $resultcity = $db->query($sqlcity); $rowcity = $resultcity->fetch_assoc(); if (is_numeric($rowcity['id']) && $rowcity['id'] > 0) { $city_id = $rowcity['id']; $outside_location = ''; } else { $city_id = -1; $outside_location = $data['location']; } $sql = 'INSERT INTO '. $dbname.'.jobs (type_id, category_id, title, description, company, city_id, url, created_on, is_temp, is_active, views_count, auth, outside_location, poster_email, apply_online) VALUES (' . $data['type_id'] . ', ' . $category_id . ', "' . $data['title'] . '", "' . $data['description'] . '", "' . $data['company'] . '", ' . $city_id . ', "' . $data['url'] . '", NOW(), 0, 1, 0, "' . md5(uniqid() . time()) . '", "' . $outside_location . '", "' . $data['poster_email'] . '", 0)'; $db->query($sql); } else { echo "FAILED: This Job already exists!

"; } } public function get_string_between($string, $start, $end) { $string = " ".$string; $ini = strpos($string,$start); if ($ini == 0) return ""; $ini += strlen($start); $len = strpos($string,$end,$ini) - $ini; return substr($string,$ini,$len); } } ?> [/code] It could possibly work on 1.8, but I did not try yet. To see if it works, once you added the valid category number and valid rss feed to your feed_db database table visit your website at http://example.com/simplefeeds_cronjob.php Use at own risk and thanks again to Redjumpsuit for coming up with this simple easy to edit code.