This is terrible to hear about Conor :( I just found the CEON URI module this morning and it's a pretty good tool - after SSU had problems with V1.5 this has picked up the slack.
I didn't want to go through products 1-by-1 to add them to the database, so I put together this script in the past hour to do it automatically.
Open index.php, save it as update-ceon-uri.php and replace the content of <body>...</body> with the following: (make sure to keep the require header.php)
<div style='width:1000px;margin:0 auto;'>
<p>Categories must be updated manually through the regular store interface before running this script.</p><hr/>
<?php // display categories to select from
$categories = mysql_query("SELECT * FROM zen_categories LEFT JOIN zen_categories_description ON zen_categories.categories_id = zen_categories_description.categories_id WHERE zen_categories.categories_status='1'");
echo "<p>Click on a category to update it's products: <br/>";
while ($showthiscat = mysql_fetch_assoc($categories)){
echo "<a href='update-ceon-uri.php?updatecategory=".$showthiscat['categories_id']."'>".$showthiscat['categories_name']."</a>, \n";
}
echo "</p><hr/>";
?>
<?php
$replacechars = array("(",")","'","\""); // specify all characters to be eliminated
// update all products in category
$updateuris = mysql_query("SELECT * FROM zen_products WHERE master_categories_id='".$_GET['updatecategory']."'");
while ($thisuri = mysql_fetch_assoc($updateuris)) {
$productsid = $thisuri['products_id'];
$productsname = implode(mysql_fetch_assoc(mysql_query("SELECT products_name FROM zen_products_description WHERE products_id='".$productsid."'")));
$catname = implode(mysql_fetch_assoc(mysql_query("SELECT uri FROM zen_ceon_uri_mappings WHERE main_page='index' AND associated_db_id='".$thisuri['master_categories_id']."'")));
$newuri = $catname."/".strtolower(str_replace(" ","-",$productsname));
$newuri = str_replace($replacechars,"",$newuri); // remove characters from array above
$today = date ('Y-m-d G:i:s'); // today
$exists = implode(mysql_fetch_assoc(mysql_query("SELECT * FROM zen_ceon_uri_mappings WHERE main_page='product_info' AND associated_db_id='$productsid'"))); // used to check if product already exists to do update vs insert
if ($exists) { // if already in zen_ceon_uri_mappings, update
mysql_query("UPDATE zen_ceon_uri_mappings SET uri='$newuri' WHERE main_page='product_info' AND associated_db_id='$productsid'");
$result = "Updated... ".mysql_error();
} else { // else, insert
mysql_query("INSERT IGNORE INTO zen_ceon_uri_mappings
(`uri`, `language_id`, `current_uri`, `main_page`, `query_string_parameters`, `associated_db_id`, `alternate_uri`, `redirection_type_code`, `date_added`) VALUES
('$newuri','1','1','product_info',NULL,'$productsid',NULL,NULL,'$today')");
$result = "Inserted... ".mysql_error();
}
echo "<p>$newuri - $result</p>\n"; // display whether updated or inserted and any problems encountered
}
?>
</div><!-- end center div -->
NOTE: It always sets language_id to 1 (english in my case).
NOTE: You'll have to specify all the characters you want removed directly in the $replacechars[] array.
NOTE: You'll have to manually type in http://yoursite.com/admin/update-ceon-uri.php to run the script.
