I'll give that a shot...
I wrote my own script to use during my catalog update that will take care of my issues.
PHP Code:
<?php
$hostname = "xxxxxxxxxxxxxx";
$username = "xxxxxxxxxxxxx";
$dbname = "xxxxxxxxxxxxxx";
$password = "xxxxxxxxxxxx";
$fail_point = "Connecting to db";
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname;charset=utf8", $username, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try
{
$fail_point = "FIX QUANTITIES EXPONENTIAL AMOUNTS";
$query = ("UPDATE zen_products SET products_quantity = 10 WHERE products_quantity LIKE '%+%'");
$stmt = $dbh->prepare($query);
$stmt->execute();
$fail_point = "RESET QUANTITIES <1 TO 0";
$query = ("UPDATE zen_products SET products_quantity = 0 WHERE products_quantity < 1");
$stmt = $dbh->prepare($query);
$stmt->execute();
$fail_point = "GET QUANTITY TOTALS FROM PRODUCTS TABLE AND ADJUST STATUS";
//THIS HIDES CATEGORIES WITH PRODUCTS IN THEM IN THE BOTTOM CATEGORY LEVEL
foreach ($dbh->query("SELECT DISTINCT parent_id, categories_id FROM zen_categories
WHERE categories_id IN (SELECT master_categories_id FROM zen_products WHERE products_quantity < 1)")as $unique_record)
{
$unique_p_id = $unique_record['parent_id'];
$unique_c_id = $unique_record['categories_id'];
$fail_point = "Counting p2c";
$total = $dbh->query("SELECT SUM(products_quantity) FROM zen_products
WHERE zen_products.master_categories_id = $unique_c_id");
$myQty = $total->fetchColumn();
//echo "Category ID - ".$unique_c_id." Total - ".$myQty."<br>";
$fail_point = "Changing status";
if ($myQty == 0)
{
$query = ("UPDATE zen_categories SET categories_status = 0 WHERE parent_id = $unique_p_id
AND categories_id = $unique_c_id");
$stmt = $dbh->prepare($query);
$stmt->execute();
//echo "Parent ID - ".$unique_p_id." - Status set to 0 <br><br>";
}
else
{
$query = ("UPDATE zen_categories SET categories_status = 1 WHERE parent_id = $unique_p_id
AND categories_id = $unique_c_id ");
$stmt = $dbh->prepare($query);
$stmt->execute();
//echo $unique_p_id." Status set to 1 <br><br>";
}
}
//HIDE EMPTY CATEGORIES
foreach ($dbh->query("SELECT DISTINCT parent_id, categories_id FROM zen_categories
WHERE categories_id NOT IN (SELECT master_categories_id FROM zen_products)")as $unique_record)
{
$unique_p_id = $unique_record['parent_id'];
$unique_c_id = $unique_record['categories_id'];
$test = $dbh->query("SELECT categories_id FROM zen_categories
WHERE $unique_c_id IN (SELECT parent_id FROM zen_categories)");
$subcategories = $test->fetchColumn();
$count = $test->rowCount();
//echo "Category ID - ".$unique_c_id." Subcategories - ".$subcategories."<br>";
$fail_point = "Changing status";
if ($subcategories == 0)
{
$query = ("UPDATE zen_categories SET categories_status = 0 WHERE categories_id = $unique_c_id");
$stmt = $dbh->prepare($query);
$stmt->execute();
//echo "Parent ID - ".$unique_c_id." - Status set to 0 <br><br>";
}
else
{
$query = ("UPDATE zen_categories SET categories_status = 1 WHERE categories_id = $unique_c_id ");
$stmt = $dbh->prepare($query);
$stmt->execute();
//echo $unique_c_id." Status set to 1 <br><br>";
}
}
//HIDE UNCATEGORIZED, NUDES, AND ANY OTHER CATEGORIES YOU DO NOT WANT SHOWING...NOT BASED ON QUANTITY
$fail_point = "Hiding other categories";
$query = ("UPDATE zen_categories SET categories_status = 0 WHERE categories_id = 1539 OR categories_id = 1");
$stmt = $dbh->prepare($query);
$stmt->execute();
}
catch (PDOException $e) {
echo "Hiding categories failed to process." . $fail_point . $e->getMessage() . "<br/>";
die();
}
?>
If you see harm in using this instead, let me know.
Thanks.
Bookmarks