A new client is leaving GoDaddy's low-quality Quick Shopping Cart product in favor of Drupal and Ubercart. We've stepped in to help them migrate their product data and set up the new site.

Although GoDaddy claims that you can import and export product data easily, as it turns out you can't export images, attributes, or options. Obviously these are important to the client, so we've resorted to cobbling together a custom import tool to automatically scrape the HTML pages of the site, pull down the hi-res images, and insert the product data directly into Ubercart's database tables.

Of course there's no way to export the client's category list, either. But each row in GoDaddy's Excel product spreadsheet does have a column for category, which contains data like this:

  Bottles & Accessories->Tops & Accessories->SIGG Tops (45)

This is a consistent enough format for us to handle automatically.

Ubercart sensibly stores product category information through the Taxonomy module. One Taxonomy category, Catalog, contains a list of terms corresponding to product's type. In the example above, the term Sigg Topps should be a child of the term Tops & Accessories, which should itself be a child of the root term Bottles & Accessories.

It's fairly easy to walk the list of terms, making sure each is in the database and creating it if it's not:

function updateCategory($product, $cat) {
	// First drop the final ID; it's useless to us
	preg_match("/(.*) \([0-9]*\)/", $cat, $res);
	$terms = explode("->", $res[1]);
 
	// Make sure the database has all the entries
	$parent = 0;
	foreach($terms as $term) {
		// Is there an ID for this term already?
		$tid = db_result(db_query("SELECT tid FROM {term_data} WHERE vid=1 AND name='$term'"));
		if(!$tid) {
			// Create a new row in term_data for this term
			$tid = db_next_id('{term_data}_tid');			
			db_query("INSERT INTO {term_data} VALUES(%d, 1, '%s', '', 0)", $tid, $term);
			db_query("INSERT INTO {term_hierarchy} VALUES(%d, %d)", $tid, $parent);
		}  
		// The next term will be a child of this one
		$parent = $tid;			
	}
	// Now simply associate $tid with the product	
	db_query('INSERT INTO {term_node} VALUES(%d, %d)', $product->nid, $tid);
}

Not so hard.

One minor point worth noting: we've hardcoded the Taxonomy category in this script to 1 -- that's just the ID that Category happens to be. Obviously your database might be different.