Hi David.
Since day one with PT, I have being using Category Hierarchy Mapping. This way, I can build my own hierarchy, making my tree mapping from each merchant.
However, over time, some merchants are gone, and request for a specific category throws empty results.
Is any script I can use to look for this orphan categories and delete without any consequences?
Thank you.
Hi,
Here's the equivalent purge script e.g. chpurge.php
<?php
require("includes/common.php");
$sql = "SELECT id FROM `".$config_databaseTablePrefix."categories_hierarchy` WHERE
id NOT IN (SELECT parent FROM `".$config_databaseTablePrefix."categories_hierarchy`)
AND
id NOT IN (SELECT categoryid FROM `".$config_databaseTablePrefix."products`)
";
database_querySelect($sql,$rows);
$ins = array();
foreach($rows as $row)
{
$ins[] = "'".$row["id"]."'";
}
$in = implode(",",$ins);
$sql = "DELETE FROM `".$config_databaseTablePrefix."categories_hierarchy` WHERE id IN (".$in.")";
database_queryModify($sql,$result);
print "Done.";
?>
Make sure that you have good backups in place first of course (e.g. using Tools > Backup and Restore) and then running the script will purge the categories_hierarchy table of the categories listed by chtest.php.
Bear in mind that this may leave new empty what were previously higher level categories, which chtest.php would then reveal if run again...
Cheers,
David.
--
PriceTapestry.com
Thank you David, this is exactly what I was looking for and it works like a charm.
Hi,
Sure - firstly as a test, could you run the following script (save as e.g. chtest.php and browse to from the top level of your Price Tapestry installation).
This will display all lowest level (no sub-category) categories to confirm that this is the list that you would like to purge (the categories are linked but should all be empty)...
<?phprequire("includes/common.php");
$sql = "SELECT id FROM `".$config_databaseTablePrefix."categories_hierarchy` WHERE
id NOT IN (SELECT parent FROM `".$config_databaseTablePrefix."categories_hierarchy`)
AND
id NOT IN (SELECT categoryid FROM `".$config_databaseTablePrefix."products`)
";
database_querySelect($sql,$rows);
$ids = array();
foreach($rows as $row)
{
$ids[] = $row["id"];
}
$ch = tapestry_categoryHierarchyArray($ids);
print "<ul>";
foreach($ch as $c)
{
print "<li><a href='".tapestry_indexHREF("category",$c)."'>".$c."</a></li>";
}
print "</ul>";
?>
If that all looks good, or otherwise of course; let me know and I'll amend for actual deletion (which may need to be run iteratively...)
Cheers,
David.
--
PriceTapestry.com