If this is your first visit, check our documentation. To post, register or login.
Forums / Setting Up Categories, Products, Attributes / Products orphaned from caregories

Products orphaned from caregories

Started by bella1999 on 22 Aug 2022, 10:28 PM · Views: 2,122

Results 1 to 9 of 9
22 Aug 2022, 10:28 PM
#1
bella1999 avatar

bella1999

New Zenner

Join Date:
Aug 2022
Location:
Adelaide
Posts:
14
Plugin Contributions:
0

Products orphaned from caregories

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)

23 Aug 2022, 12:05 AM
#2
mc12345678 avatar

mc12345678

Totally Zenned

Join Date:
Jul 2012
Posts:
16,908
Plugin Contributions:
2

Re: Products orphaned from caregories

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.

ZC Installation/Maintenance Support <- Site
Contribution for contributions welcome...

23 Aug 2022, 1:29 AM
#3
bella1999 avatar

bella1999

New Zenner

Join Date:
Aug 2022
Location:
Adelaide
Posts:
14
Plugin Contributions:
0

Re: Products orphaned from caregories

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.

23 Aug 2022, 8:55 AM
#4
bella1999 avatar

bella1999

New Zenner

Join Date:
Aug 2022
Location:
Adelaide
Posts:
14
Plugin Contributions:
0

Re: Products orphaned from caregories

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!

23 Aug 2022, 9:37 AM
#5
torvista avatar

torvista

Totally Zenned

Join Date:
Aug 2007
Location:
Gijón, Asturias, Spain
Posts:
2,880
Plugin Contributions:
7

Re: Products orphaned from caregories

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

Steve
github.com/torvista: BackupMySQL, Structured Data, Multiple Copy-Move-Delete, Google reCaptcha, Image Checker, Spanish Language Pack and more...

23 Aug 2022, 11:23 AM
#6
mc12345678 avatar

mc12345678

Totally Zenned

Join Date:
Jul 2012
Posts:
16,908
Plugin Contributions:
2

Re: Products orphaned from caregories

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) {
    

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()

ZC Installation/Maintenance Support <- Site
Contribution for contributions welcome...

23 Aug 2022, 11:52 AM
#7
swguy avatar

swguy

Administrator

Join Date:
Feb 2006
Location:
Tampa Bay, Florida
Posts:
10,760
Plugin Contributions:
56

Re: Products orphaned from caregories

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

That Software Guy. My Store: Zen Cart Support
Available for hire - See my ad in Services
Plugin Moderator, Documentation Curator, Chief Cook and Bottle-Washer.
Do you benefit from Zen Cart? Then please support the project.

24 Aug 2022, 8:03 AM
#8
torvista avatar

torvista

Totally Zenned

Join Date:
Aug 2007
Location:
Gijón, Asturias, Spain
Posts:
2,880
Plugin Contributions:
7

Re: Products orphaned from caregories

$categories_id_zero->count() instead of $categories_id_zero->RecordCount()
Old code, I was young and (more) ignorant.

Steve
github.com/torvista: BackupMySQL, Structured Data, Multiple Copy-Move-Delete, Google reCaptcha, Image Checker, Spanish Language Pack and more...

24 Aug 2022, 4:06 PM
#9
mc12345678 avatar

mc12345678

Totally Zenned

Join Date:
Jul 2012
Posts:
16,908
Plugin Contributions:
2

Re: Products orphaned from caregories

torvista:

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!

ZC Installation/Maintenance Support <- Site
Contribution for contributions welcome...