Migrating PHP Link Directory to Arfooo – import script

I wanted to have a better look at Arfooo directory and was considering to eventually replace my phpLD directory www.fast4u.co.uk which is running on PHP Link Directory 2.1. A good news was there is a migration script which can convert the data from several directories:

  • Categorizator,
  • Rewriteyourphpannuaire addon de Categorizator,
  • RevolutionneYourAnnuaire addon de Categorizator,
  • Freeglobes,
  • 1two,
  • Netref,
  • Linker,
  • MyDir,
  • Utilisable
  • PHP Link Directory Script ( phpLD ),
  • Fractalum

So I have installed Arfooo Directory 2.0.2, uploaded the import to /install/import and gave it a try. Well, a bad news was the import script failed to import categories. In  Arfooo Directory 2.0.2 is a table _categoryparents which was not populated at all and without it Arfooo wasn't able to anything with the imported links and categories.

I have modified install/import/classes/PldConverter.php and now it will import PhpLD up to 4 levels deep (what I needed for my conversion).

Here is the modified PldConverter.php (replace it)

<?php

class PldConverter extends Converter
{
	private static $handle = null;

	function getInstance()
	{
		if( !self::$handle ) self::$handle = new PldConverter;
		return self::$handle;
	}

	function convertSites()
	{
		$this->setOldDb();
		$result = $this->query( 'select * from `PLD_LINK`;' , $this->dbOld );
		/*nom 	url 	actif 	description 	email 	mot 	cat 	root 	compt_in 	compt_out 	id 	langue 	star 	date_inser 	nb_com 	awards*/

		$this->setNewDb();

		$this->query( 'delete from `'.$this->dbNewPrefix.'sites`;' , $this->dbNew );

		/* extra things*/
		//        $this->query( 'delete from `'.$this->dbNewPrefix.'votes`;' , $this->dbNew );
		$this->query( 'delete from `'.$this->dbNewPrefix.'comments`;' , $this->dbNew );
		/*             */

		$this->qObiect->tableName = 'sites';
		$this->qObiect->dbPrefix = $this->dbNewPrefix;
		while( $r = mysql_fetch_assoc($result) )
		{

			if(!$r['STATUS'])continue;

			$this->qObiect->reset();

			$this->qObiect->add('siteId', $r['ID']);
			$this->qObiect->add('categoryId', $r['CATEGORY_ID']);
			$this->qObiect->add('siteTitle', $r['TITLE']);
			$this->qObiect->add('description', strip_tags($r['DESCRIPTION']));
			$this->qObiect->add('url', $r['URL']);

			$this->qObiect->add('webmasterEmail', $r['OWNER_EMAIL']);
			$this->qObiect->add('creationDate', $r['DATE_MODIFIED']);

			switch($r['STATUS'])
			{
				case 2:
					$status = 'validated';
					break;
				default:
					$status = 'waiting';
			}
			//proposalForKeywords
			$this->qObiect->add('status', $status);
			$this->qObiect->add('visitsCount', $r['HITS']);
			$this->qObiect->add('rssTitle', '');
			$this->qObiect->add('returnBond', $r['RECPR_URL']);
			$this->qObiect->add('rssFeedOfSite', '');

			$sql = $this->qObiect->getSQL();
			$sql = iconv('ISO-8859-1','utf-8//ignore', $sql);
			$sql = str_replace("\xc2\x80", "\xe2\x82\xac", $sql);
			$this->query($sql , $this->dbNew);
		}

		return true;
	}

	function convertCategories()
	{
		$this->setOldDb();
		$result = $this->query( 'select * from `PLD_CATEGORY`;' , $this->dbOld );
		/*nom 	attach 	id 	chemin 	description 	actif 	nb_site 	im 	sous_cat 	aff_parent 	colonne 	nb_cat 	nb_tot 	id_advertise 	back_url_rewriting*/

		$this->setNewDb();
		$this->query( 'delete from `'.$this->dbNewPrefix.'categories`;' , $this->dbNew );

		$this->qObiect->tableName = 'categories';
		$this->qObiect->dbPrefix = $this->dbNewPrefix;

		while( $r = mysql_fetch_assoc($result) )
		{
			$this->qObiect->reset();

			$this->qObiect->add('categoryId', $r['ID']);
			$this->qObiect->add('parentCategoryId', $r['PARENT_ID']);
			$this->qObiect->add('name', $r['TITLE']);
			$this->qObiect->add('urlName', $this->sanitize($r['TITLE']));
			$this->qObiect->add('description', $r['DESCRIPTION']);
			$this->qObiect->add('imageSrc', 'defaultCategoryImage.gif');

			switch($r['STATUS'])
			{
				case 2:
					$tender = 1;
					break;
				default:
					$tender = 0;
			}
			$this->qObiect->add('possibleTender', $tender, true);
			$this->qObiect->add('title', '');

			$sql = $this->qObiect->getSQL();
			$sql = iconv('ISO-8859-1', 'utf-8//ignore', $sql);
			$sql = str_replace("\xc2\x80", "\xe2\x82\xac", $sql);
			$this->query($sql , $this->dbNew);
		}


		/// 2010-02-16 Addition by Milan Marchfeld (www.ontimedesign.net)

		//empty table
		$this->query ('delete from `'.$this->dbNewPrefix.'categoryparents`;' , $this->dbNew );

		//set categoryparents for top category
		$sql = 'SELECT `categoryId`, `parentCategoryId` FROM `'.$this->dbNewPrefix.'categories`
				WHERE `parentCategoryId` = \'0\' ';
		echo "<br>#".$sql;
		$result = $this->query($sql, $this->dbNew );

		echo "<br># 1st level sub-categories: ".mysql_num_rows($result);

		$this->qObiect->tableName = 'categoryparents';
		$this->qObiect->dbPrefix = $this->dbNewPrefix;


		while( $r = mysql_fetch_assoc($result) )
		{
			$level[0][]= $r['categoryId'];
			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\''.$r['categoryId'].'\',  \''.$r['categoryId'].'\', \'0\'); ';

			$cp = $this->query($sql , $this->dbNew);
			if(mysql_errno()) {
				echo mysql_errno();
				die();
			}
			echo "<br>".$sql;


			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\''.$r['parentCategoryId'].'\',  \''.$r['categoryId'].'\', \'1\'); ';

			$cp = $this->query($sql , $this->dbNew);
			echo "<br>".$sql;
		}

		$ids = join(',',$level[0]);
		$sql = 'SELECT `categoryId`, `parentCategoryId` FROM `'.$this->dbNewPrefix.'categories`
				WHERE `parentCategoryId` IN ('.$ids.') ';

		echo "<br>#".$sql;
		$result = $this->query($sql, $this->dbNew );

		echo "<br># 2nd level sub-categories: ".mysql_num_rows($result);


		while( $r = mysql_fetch_assoc($result) )
		{
			$level[1][]= $r['categoryId'];

			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\''.$r['categoryId'].'\',  \''.$r['categoryId'].'\', \'0\'); ';

			$cp = $this->query($sql, $this->dbNew );
			echo "<br>".$sql;

			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\''.$r['parentCategoryId'].'\',  \''.$r['categoryId'].'\', \'1\'); ';

			$cp = $this->query($sql , $this->dbNew);
			echo "<br>".$sql;

			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\'0\',  \''.$r['categoryId'].'\', \'2\'); ';

			$cp = $this->query($sql , $this->dbNew);
			echo "<br>".$sql;
		}

		$ids = join(',',$level[1]);
		$sql = 'SELECT `categoryId`, `parentCategoryId` FROM `'.$this->dbNewPrefix.'categories`
				WHERE `parentCategoryId` IN ('.$ids.') ';

		echo "<br>#".$sql;
		$result = $this->query($sql, $this->dbNew );

		echo "<br># 3rd level sub-categories: ".mysql_num_rows($result);

		while ( $r = mysql_fetch_assoc($result)) {
			$level[2][]= $r['categoryId'];
			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\''.$r['categoryId'].'\',  \''.$r['categoryId'].'\', \'0\'); ';

			$cp = $this->query($sql, $this->dbNew );
			echo "<br>".$sql;

			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\''.$r['parentCategoryId'].'\',  \''.$r['categoryId'].'\', \'1\'); ';

			$cp = $this->query($sql , $this->dbNew);
			echo "<br>".$sql;

			$par_of_par = $this->get_parent_for($r['parentCategoryId']);
			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\''.$par_of_par.'\',  \''.$r['categoryId'].'\', \'2\'); ';

			$cp = $this->query($sql , $this->dbNew);
			echo "<br>".$sql;


			$sql = 'insert into `'.$this->dbNewPrefix.'categoryparents` (`parentId`,`childId`,`depth`)
					values(\'0\',  \''.$r['categoryId'].'\', \'3\'); ';

			$cp = $this->query($sql , $this->dbNew);
			echo "<br>".$sql;

		}

		$ids = join(',',$level[2]);
		$sql = 'SELECT `categoryId`, `parentCategoryId` FROM `'.$this->dbNewPrefix.'categories`
				WHERE `parentCategoryId` IN ('.$ids.') ';

		echo "<br>#".$sql;
		$result = $this->query($sql, $this->dbNew );

		echo "<br># 4th level sub-categories: ".mysql_num_rows($result);


		/// end of addition
		return true;
	}

	function convertAll()
	{
		$results = array();

		try
		{
			$results['sites'] = array();
			$this->init();
			$this->convertSites();

			$results['categories'] = array();
			$this->convertCategories();
			$this->buildCategoryParents();
		}
		catch(Exception $e)
		{
			$lastKey = end(array_keys($results));
			$results[$lastKey]['error'] = $e->getMessage();
		}

		return $results;
	}

	/// 2010-02-16 Addition by Milan Marchfeld (www.ontimedesign.net)
	function get_parent_for($id) {
		$sql = 'SELECT `parentCategoryId` FROM `'.$this->dbNewPrefix.'categories` WHERE `categoryId`=\''.$id.'\'';
		$result = $this->query($sql , $this->dbNew);
		$row = mysql_fetch_row($result);
		return $row[0];

	}




	/**
	 * Convert a string to the file/URL safe "slug" form
	 *
	 * @param string $string the string to clean
	 * @param bool $is_filename TRUE will allow additional filename characters
	 * @return string
	 */
	function sanitize($string = '', $is_filename = FALSE)
	{
		// Replace all weird characters with dashes
		$string = preg_replace('/[^\w\-'. ($is_filename ? '~_\.' : ''). ']+/u', '_', $string);

		// Only allow one dash separator at a time (and make string lowercase)
		return mb_strtolower(preg_replace('/--+/u', '-', $string), 'UTF-8');
	}

}
?>

Now the import will print sql on screen in step 4 of the import, which has to be manually executed (to be fixed for next release)

As well I am using Featured links in my PhpLD which is lost during the import. Another thing to look at.

One thought on “Migrating PHP Link Directory to Arfooo – import script

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.