Migrated site to v1.5.4 and having a small issue with categories

Hey guys,

I've migrated my site to the latest version of Zen about 6 months ago and everything works great except one thing I noticed this morning. When creating a new category / sub category, then adding a few products my products don't show. If I set the master category to an old category / sub category they show fine ? Also if I put a single product in the sub category it shows as well, Just a multiple product issue.

I've looked at the obvious things like errors in the database etc and everything seems fine. The category is being created with a unique identifier. Also products are set to the correct category ID.

Bit of a head scratcher :( Not asking to be walked through the issue, just maybe some pointer on where to look within the core or something it could be.

Cheers guys !

Please provide the information in posting tips at the top when you reply

From 1.3.9g - v1.5.4. I've upgraded manually also migrating the DB manually. No plugins installed that would effect the category system. I have my own server so installed via SSH. MySQL 5.6.25-4 PHP Version: 5.6.12-1 (Zend: 2.6.0). http://www.norfolklights.com. Like I said just looking for a pointer in the right direction really.

When creating the new category / sub-category and adding the product, is the product in the category by itself, or is it in the category with a sub-category?

Does this only happen in newly created categories or does a new product in an existing category also have the same problem?

No products are in categories with subcategories. So if I create a new category (test), then a subcategory of test (subtest), then add a product (test_prod) with the master category ID subtest it shows as a single product. Now I add some more (test_prod1, test_prod2) with the master category ID subtest nothing shows. If I go and manually change the Master category ID to the last category set by the old site (old_cat) they show ?

I've been through the database and nothing jumps out when comparing the structure of the old entries to the new entries. I'm checking in the product section, category section & product_to_category section and everything looks the same at first glance.

I'm guessing this got to be a database error because there's nothing else that would cause this sort of error ?

Still can't get to the bottom of this. Trying to work my way through the code but can't figure it out. When I create a category I get the correct information in the database. Categories & Categories_description are populated with the correct information and nothing is different to anything else. Then I allocate a couple of products and the product exists in the database with no information missing but still if I allocate the product to a new created category the products are not shown.

What makes me think maybe a template issue or something ?. Does anyone know where to look for code relating to how the product list is being generated ? I've used the developers tools included to search and found this but I can't see how it wouldn't function.

// Construct a category path to the product
// TABLES: products_to_categories
  function zen_get_product_path($products_id) {
    global $db;
    $cPath = '';

    $category_query = "select p.products_id, p.master_categories_id
                       from " . TABLE_PRODUCTS . " p
                       where p.products_id = '" . (int)$products_id . "' limit 1";


    $category = $db->Execute($category_query);

    if ($category->RecordCount() > 0) {

      $categories = array();
      zen_get_parent_categories($categories, $category->fields['master_categories_id']);

      $categories = array_reverse($categories);

      $cPath = implode('_', $categories);

      if (zen_not_null($cPath)) $cPath .= '_';
      $cPath .= $category->fields['master_categories_id'];
    }

    return $cPath;
  }

////

If I search using DTK through the template files I see loads of references so was just wondering if someone knows what files responsible for generating the list of products ? Zen cart is huge nowa days :D

Try switching to the classic template to see if they display

How many languages do you have installed/configured?

Have you applied the relevant bug fixes to your site? See: Known Bugs (and fixes) with v1.5.4

Does the same problem happen with a fresh install in a new database (ie: with test data)? By using the demo database it may help rule out whether the problem is related to something unique to the server or to any plugins installed.

drgr33n:

No plugins installed that would effect the category system.
Please list them anyway.

Hey guys,

Only one language installed. Fresh install of zen cart works fine. Been through the bug fixes and nothing relevant there. The template was from template monster but didn't have any core modifications. Only mod really is a bit of javascript to scroll out text over 100 words and the banner scroller. Oh and few modifications for COWOA. I'm convinced this is a database issue might do a dump of the old site and the new sites DB and run diff to see if I can spot anything. But does anyone know where in the zen cart system is responsible for generating the product list ?

In what view(s) are the multiple products not showing and in what view(s) do the newly added product show? There are multiple locations where such a list of product is generated (typically in the includes/modules/pages subfolder for the applicable page being displayed) if in the upgrade of the site these files were renamed leaving behind a file with the extension of .php intact, then that could cause an issue.

They don't show up when they are allocated a new category / subcat when there are multiple products in that category / subcat. If I manually edit the master_category_id in the database they show. If I have only one product in the category / subcat the one product shows as if you clicked on the product. There must be something telling zen not to show the products. I will run diff on the database dumps later today but I'm convinced after looking over the database it must be a code issue.

Sorry to bump again but still cannot find anything that would affect the categories in question.

1. I've upgraded from 1.3.9g - v1.5.4.
2. I've upgraded manually also migrating the DB manually.
3. Plugins installed are FAQ manager, Google RECapacha, Small self wrote plugin to display and scroll out category descriptions, Zen Cart Multisite module.
4. I have my own server so installed via SSH.
5. MySQL 5.6.25-4 PHP Version: 5.6.12-1 (Zend: 2.6.0).
6. Domain http://www.norfolklights.com.

Old site works fine. New site does not display multiple products if they have been allocated a master category ID from a new category. So if I create a new category then create a product in that category it shows as a single product view. If I add another product they all disappear. If I copy them over to an old category (migrated from the old database) They show in the new and old category.

Database tables I know are being called that could be affecting.

products
categories
categories_to _products

Database table structure matches everything for these tables from the old to the new. When the category is being created all the relevant information is being created within the database. Admin sees everything as it should and the categories are being displayed correctly.

Reason why I don't think it's a server issue; Because when the product master ID changes it appears instantly.

I'm struggling to find where these lists are being generated. I've been though a few classes and other parts but nothing jumps out from the old site to the new ? I'm convinced this is a database issue. I need to see what's going on to debug as no errors are being generated when products fail to appear. I've had another friend who works with Zen Cart have a look and he sat their scratching his head too :( Any pointers would be much appreciated. I'm not asking to be spoon fed just if anyone knows the internals of Zen enough to point me to the right files I should be able to add my own debugging and see what the fook is going on.

This is defo not a product issue because the product exists with any master_id allocated it's just when the product is allocated a master_category_id from a newly created category it's not displayed in any category. So for some reason that product is not being passed over into the product list.

Example1:

http://www.norfolklights.com/trays-tanks-c-66_360/

This category has three products. Two have old category master_cat_id's and one has been allocated a new one. Only two products show.

Example2:

http://www.norfolklights.com/pots-c-66_27/

This category has three products all with new category master_cat_id's

Example3:

http://www.norfolklights.com/test-c-361/test1-p-2418.html

Here a new product was created in a new category. As you can see a single product with a new master_cat_id is being displayed ? What makes me think it may be a template issue maybe a function somewhere ?

Gone over the time frame to edit sorry again. Here's an example of the old site working on the same server. Again this is a new category with new products but is displaying as it should.

Ok progress :D I've tracked it down to the Zen Cart Multi site module and this mess

<?php
/**
 * Multisite Module function
 * Modify the queries of Zen-cart in order to filter the products/categories
 *
 * @param String $sql
 * @return String
 */
function cat_filter($sql) { // only allow selected products/categories
  if(!defined('ALLOW_CAT_FILTER')||(ALLOW_CAT_FILTER!='no')) {
    // define('ALLOW_CAT_FILTER','no') will desactivate the category filter
    $sql = preg_replace('/\s\s+/', ' ', $sql);
    $sql2 = $sql;
    $sql_lower = strtolower($sql);
    $str_pos_products = strpos($sql_lower,TABLE_PRODUCTS . ' p');
    $str_pos_desc = strpos($sql_lower,TABLE_CATEGORIES_DESCRIPTION.' cd');
    $str_pos_where = strpos($sql_lower,'where');
    $fix = 0;
    if(($str_pos_where===false)&&($str_pos_products!==false)) {
    // There is a the table products in "FROM" and there is no "WHERE"
      $fix = 1;
    } elseif(($str_pos_desc!==false)&&($str_pos_desc<$str_pos_where)) {
    // There is a "categories_description cd" before "WHERE"
      $fix = 2;
    } elseif (($str_pos_products!==false)&&($str_pos_products<$str_pos_where)) {
    // There is "products p" before "WHERE"
      $fix = 1;
    } elseif($sql == "select categories_id from " . TABLE_CATEGORIES . " where categories_status=1 limit 1") {
    // Special Query for the file includes/modules/sideboxes/categories.php
        $sql = "select c.categories_id from ".TABLE_CATEGORIES." c INNER JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd
         ON (c.categories_id = cd.categories_id
         AND cd.language_id=".$_SESSION['languages_id']."
           AND cd.categories_description LIKE '%-".SITE_NAME."-%') where categories_status=1 limit 1";
    } elseif($sql == "select products_id from " . TABLE_FEATURED . " where status= 1 limit 1") {
    // Special Query for the categories sideboxe/Featured Products
      $sql = "SELECT f.products_id FROM " . TABLE_FEATURED . " f
      INNER JOIN ".TABLE_PRODUCTS." p ON (f.products_id=p.products_id)
      INNER JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd
      ON (p.master_categories_id = cd.categories_id
      AND cd.language_id=".$_SESSION['languages_id']."
      AND cd.categories_description LIKE '%-".SITE_NAME."-%')
      WHERE status=1 limit 1";
    } elseif($sql == "select s.products_id from " . TABLE_SPECIALS . " s where s.status= 1 limit 1") {
    // Special Query for the categories sideboxe/Special Products
      $sql = "SELECT s.products_id FROM " . TABLE_SPECIALS . " s
      INNER JOIN ".TABLE_PRODUCTS." p ON (s.products_id=p.products_id)
      INNER JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd
      ON (p.master_categories_id = cd.categories_id
      AND cd.language_id=".$_SESSION['languages_id']."
      AND cd.categories_description LIKE '%-".SITE_NAME."-%')
      WHERE status=1 limit 1";
    } else { // Query with categories but not descriptions
      $str_pos_cats = strpos($sql_lower,TABLE_CATEGORIES . ' c');
      if($str_pos_cats!==false&&$str_pos_cats<$str_pos_where) {
        $add_pos = $str_pos_products + strlen(TABLE_CATEGORIES . ' c');
        $str_pos_coma = strpos($sql_lower,',',$add_pos);
        if($str_pos_coma!==false) {
          $add_pos = $str_pos_coma;
        } 
        if(($str_pos_where!==false)&&(($str_pos_coma===false)||($str_pos_where<$str_pos_coma))) {
          $add_pos = $str_pos_where;
        }
        $sql = substr($sql,0,$add_pos).
          " INNER JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd
            ON (c.categories_id = cd.categories_id
            AND cd.language_id=".$_SESSION['languages_id']."
            AND cd.categories_description LIKE '%-".SITE_NAME."-%') ".
          substr($sql,$add_pos);
      }
    }
    if($fix==1) {
      if(preg_match("/^select p2c.categories_id from ".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c ".
                   "where p.products_id = '[0-9]+' and p.products_status = '1' ".
                   "and p.products_id = p2c.products_id limit 1$/",$sql)) {
        $sql = str_replace(TABLE_PRODUCTS_TO_CATEGORIES." p2c ",TABLE_PRODUCTS_TO_CATEGORIES." p2c
          INNER JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd
          ON (p2c.categories_id=cd.categories_id
          AND cd.language_id=".$_SESSION['languages_id']."
          AND cd.categories_description LIKE '%-".SITE_NAME."-%')
          INNER JOIN ".TABLE_CATEGORIES." as c ON (cd.categories_id=c.categories_id AND c.categories_status=1)",$sql);
            } else {
        $add_pos = $str_pos_products + strlen(TABLE_PRODUCTS . ' p');
        $str_pos_coma = strpos($sql_lower,',',$add_pos);
        if($str_pos_coma!==false) {
          $add_pos = $str_pos_coma;
        } 
        if(($str_pos_where!==false)&&(($str_pos_coma===false)||($str_pos_where<$str_pos_coma))) {
          $add_pos = $str_pos_where;
        }
        $sql = substr($sql,0,$add_pos).
            " INNER JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd
              ON (p.master_categories_id = cd.categories_id
              AND cd.language_id=".$_SESSION['languages_id']."
              AND cd.categories_description LIKE '%-".SITE_NAME."-%') ".
            substr($sql,$add_pos);
             }
    } else if ($fix == 2) {
      $sql = substr($sql,0,$str_pos_where + 6).
        " cd.categories_description LIKE '%-".SITE_NAME."-%'
        AND cd.language_id=".$_SESSION['languages_id']." AND ".
        substr($sql,$str_pos_where + 6);
    }
  }
  if($sql==$sql2) {
    $fh = fopen(DIR_FS_LOGS.'/multisite_logs.txt','a');
    fwrite($fh, "Query not changed:\n".$sql2."\n\n");
    fclose($fh);
  }
  return $sql;
}
?>

I'll fix it up and get in touch with the author.

drgr33n:

Ok progress :D I've tracked it down to the Zen Cart Multi site module
Woot! Glad you were able to narrow it down to a specific plugin.
Again, that's why we ask you to list your plugins when posting about problems ;)

Yup :D That's me 100 miles an hour and overlook things, totally forgot about adding that for future use. Thank you guys for looking over this for me.