Products orphaned from caregories

[bella1999]

Hi,

I am using v 1.5.5f, responsive classic, this was current version when I downloaded last month. https://yarnpig2-au.stackstaging.com/

Just fixed issue with main page not displaying and now I realise that items showing on the storefront have disappeared from the admin/catalog/categories. I wanted to amend some of their details. But there are several missing.

If I click on one of these orphans in the shopfront featured products, I can add to my cart. Also search doesnt find them. Search does find the other products.

Looking at a backup file, the sql for that table seems to have all my missing data. Categories 3, 67, 66, 75 are missing.
--
-- Dumping data for table `categories_description`
--

LOCK TABLES `categories_description` WRITE;
/*!40000 ALTER TABLE `categories_description` DISABLE KEYS */;
INSERT INTO `categories_description` VALUES (3,1,'Notions','Accessories/bits n bobs - For all your non-yarn needs'),(79,1,'Misc','Yarns that dont belong in other categories!'),(10,1,'Knitting Needles','<p>Straight Needles, sets of needles or circular needles<br /><br /></p>'),(67,1,'Misc things','Blocking wires, yarn needles, stitch counters & other bits n pieces'),(66,1,'Crochet Hooks','Crochet hooks and hook sets'),(21,1,'Gift Certificates','Send a Gift Certificate today!<br /><br />Gift Certificates are good for anything in the store.'),(75,1,'Azteca','Beautiful 10 ply mix of 50% wool and 50% (........cut off, whole thing not needed)

[mc12345678]

Really should try to identify why some of that information has been lost otherwise will be back to trying to again restore it or other data. Realize that have only called out the categories_description table; however, have also indicated that there are or were problems with other aspects of product information. There are several tables involved with a product appearing in a category especially when comparing catalog side to admin side.

[bella1999]

Hi,

THanks for the response. However, I dont know where to start looking, or what might need fixed. I thought all my product adding was done in the admin panel, so dont know how it has got stuffed up.
I did read some tthread about lost categories, but the fact that it exists in the code would indicate its not lost, just hiding.

[bella1999]

I have gone with re-adding the product/sub product folders in the admin section; then editing the products in products & products-to-categories tables to point to the new category id.

THis has allowed the products to then appear in the category/products list in admin; they are searchable in the storefront.

I would love to know how this could have happened - its been rather inconvenient!

[torvista]

In the product table, a product has a master category id.
In the products_to_categories table, this same category id must exist + (optionally) other category ids that are the linked categories.

Sounds like the product master category id is missing from the products_to_categories table.

This could have arisen from a move/change to that product + a bug in the code, or an import that did not work 100%.

I have had this in the past and added a check in the admin header for this.

If you want to do that, open admin\includes\header.php IN YOUR DEVELOPMENT/TEST SERVER
and find

} // hide when other language dropdown is used

// display alerts/error messages, if any

and add this in between:

} // hide when other language dropdown is used

//steve check for zero entry in P2C table
$categories_id_zero = $db->Execute("SELECT products_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE categories_id = 0");
if ($categories_id_zero->count() > 0) {
    define('ERROR_PRODUCT_P2C_CATEGORY_ZERO', '%s had category_id=0 in table "' . TABLE_PRODUCTS_TO_CATEGORIES . '". This entry has been deleted.');
    foreach ($categories_id_zero as $category_id_zero) {
        $db->Execute("DELETE FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE products_id = " . (int)$category_id_zero['products_id'] . " AND categories_id = 0");
        $messageStack->add(sprintf(ERROR_PRODUCT_P2C_CATEGORY_ZERO,
            (zen_get_products_model($category_id_zero['products_id']) !== '' ? zen_get_products_model($category_id_zero['products_id']) . ' - ' : '') . '"'
            . zen_get_products_name($category_id_zero['products_id']) . '"', 'error'));
    }
}

//steve check for master_category in table products_to_categories
//if ($current_page == 'index.php') {
$products_master_category_check_sql = "SELECT p.products_id, master_categories_id, products_model, products_name
                                           FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                                           WHERE master_categories_id NOT IN
                                           (SELECT categories_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc WHERE ptc.products_id = p.products_id)
                                           AND pd.products_id = p.products_id
                                           AND pd.language_id = " . (int)$_SESSION['languages_id'];
$products_master_category_check = $db->Execute($products_master_category_check_sql);


if ($products_master_category_check->count() > 0) {

    define('ERROR_MASTER_CATEGORY_MISSING_P2C', '%s: master_category_id entry not found in table "' . TABLE_PRODUCTS_TO_CATEGORIES . '".');
    define('MASTER_CATEGORY_INSERT_P2C', '%1$s: master_category_id (#%2$u  - %3$s) has been automatically added to table "' . TABLE_PRODUCTS_TO_CATEGORIES . '".');

    foreach ($products_master_category_check as $product_row) {
        $product = ($product_row['products_model'] !== '' ? $product_row['products_model'] . ' - ' : '') . $product_row['products_name'] . ' (#' . $product_row['products_id'] . ')';
        $messageStack->add(sprintf(ERROR_MASTER_CATEGORY_MISSING_P2C, $product), 'error');
        $db->Execute("INSERT INTO " . TABLE_PRODUCTS_TO_CATEGORIES . " (products_id, categories_id)
            VALUES (" . (int)$product_row['products_id'] . ", " . (int)$products_master_category_check->fields['master_categories_id'] . ")");

        // reset products_price_sorter for searches etc.
        zen_update_products_price_sorter($product_row['products_id']);
        zen_record_admin_activity($product . ' master_category_id auto-added to ' . TABLE_PRODUCTS_TO_CATEGORIES, 'info');
        $messageStack->add(sprintf(MASTER_CATEGORY_INSERT_P2C, $product, $products_master_category_check->fields['master_categories_id'],
            zen_output_generated_category_path($products_master_category_check->fields['master_categories_id'])), 'success');
    }
}
//}
//eof steve

// display alerts/error messages, if any

[mc12345678]

In the product table, a product has a master category id.
In the products_to_categories table, this same category id must exist + (optionally) other category ids that are the linked categories.

Sounds like the product master category id is missing from the products_to_categories table.

This could have arisen from a move/change to that product + a bug in the code, or an import that did not work 100%.

I have had this in the past and added a check in the admin header for this.

If you want to do that, open admin\includes\header.php IN YOUR DEVELOPMENT/TEST SERVER
and find

} // hide when other language dropdown is used

// display alerts/error messages, if any

and add this in between:

} // hide when other language dropdown is used

//steve check for zero entry in P2C table
$categories_id_zero = $db->Execute("SELECT products_id FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE categories_id = 0");
if ($categories_id_zero->count() > 0) {

Somewhat beyond the scope of this thread, though I'm curious about the design consideration of using the likes of $categories_id_zero->count() instead of $categories_id_zero->RecordCount()

[swguy]

> I am using v 1.5.5f, responsive classic, this was current version when I downloaded last month.
The version you are using is quite old. Not sure how you got it, but here's how to get the current version:

https://www.zen-cart.com/index.php

Older versions did have issues like the ones you are describing, but these should be fixed in the latest version.

To track down database issues, you can use the Audit plugin.

https://www.zen-cart.com/downloads.php?do=file&id=2261

[torvista]

$categories_id_zero->count() instead of $categories_id_zero->RecordCount()

Old code, I was young and (more) ignorant.

[mc12345678]

Old code, I was young and (more) ignorant.

Well what's somewhat interesting is that 1) that worked but 2) is that really that's new code. New because I think it was 1.5.5 when sql results came back as arrays/countable objects.

In a way it was ingenious!