1054 Unknown column error after site went live

[DivaVocals]

So if you go to these links you get the errors in the queries as shown:
http://www.hairisle.com/products_new

1054 Unknown column 'p.master_categories_id' in 'on clause'
in:
[select count(p.products_id) as total FROM zen_products p LEFT JOIN zen_manufacturers m ON (p.manufacturers_id = m.manufacturers_id), zen_products_description pd LEFT JOIN zen_hide_categories h ON (p.master_categories_id = h.categories_id) WHERE (h.visibility_status < 2 OR h.visibility_status IS NULL) AND p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 ]

http://www.hairisle.com/products_all

1054 Unknown column 'p.master_categories_id' in 'on clause'
in:
[select count(p.products_id) as total FROM zen_products p LEFT JOIN zen_manufacturers m ON (p.manufacturers_id = m.manufacturers_id), zen_products_description pd LEFT JOIN zen_hide_categories h ON (p.master_categories_id = h.categories_id) WHERE (h.visibility_status < 2 OR h.visibility_status IS NULL) AND p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 ]

This did not ocurr on the dev site as you can see:
http://clients.overthehillweb.com/hairisle/products_new
http://clients.overthehillweb.com/hairisle/products_all


I've been doing a LOT of searching of this forum and Googling, and my research indicates that this might be an issue with the on clause of the left join.. This post was very helpful in nundging me inthe right direction. It reads in part:
http://www.zen-cart.com/forum/showpo...47&postcount=6

The real culprit is Mysql data base queries. Once your host upgrade sql database to 5.x, you need to fix your sql queries...

So I took a look at both dev and the live site and yes indeed they are on different versions of mySQL. (thought my server was running 5.x.. it's not... I'll deal with that later this week)

DB Versions:
hairisle dot com - Database: MySQL 5.0.81-community-log
clients dot overthehillweb dot com/hairisle - Database: MySQL 4.1.22-max-log

So I think I have a grasp on WHAT the issue is, and if I can get some assistance to what the corrected syntax for these queries should be, I should be good to go in correcting the queries for these pages..

I am hoping a SQL guru can pop in to help.. YES I read other threads on this error.. Unfortunately the queries are different, and I can't make the leap to figure out how to correct the syntax of this query based on some of the other threads.. Hope someone will take pity on me and help..

[DrByte]

What version of Zen Cart?
What addons are installed?
What customizations have been made to what parts of the code?

[DivaVocals]

What version of Zen Cart?
What addons are installed?
What customizations have been made to what parts of the code?

Just the man I was hoping to hear from..

Version 1.3.8a
Tons of add-ons but the All Products and and New Products pages includes modified code from two add ons (Single Listing Template and Hidden Categories) The query that causing this issue is from the Hidden Categories add-on.

[DrByte]

Okay, so it seems clear that your Hidden Categories addon is NOT compatible with MySQL 5.

[DrByte]

My hunch is that the 2nd LEFT JOIN is pointless, and actually a waste of CPU power, in addition to its incompatibility with MySQL 5.

Reworking the query this way seems to be more efficient:

select count(p.products_id) as total 
  FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), 
       products_description pd, 
       hide_categories h 
 WHERE p.master_categories_id = h.categories_id AND (h.visibility_status < 2 OR h.visibility_status IS NULL) 
   AND p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 ;

Hopefully that gives you a starting point of how to rewrite the query in the addon.

[DivaVocals]

Thanks sooooo very much... When I get home tonight I'm gonna play around with this.. I know how to write/edit simpler SQL, but joins have ALWAYS confused me.. So I REALLY thank you for the help!!!

My hunch is that the 2nd LEFT JOIN is pointless, and actually a waste of CPU power, in addition to its incompatibility with MySQL 5.

Reworking the query this way seems to be more efficient:

select count(p.products_id) as total 
  FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), 
       products_description pd, 
       hide_categories h 
 WHERE p.master_categories_id = h.categories_id AND (h.visibility_status < 2 OR h.visibility_status IS NULL) 
   AND p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 ;

Hopefully that gives you a starting point of how to rewrite the query in the addon.

[DivaVocals]

Okay.. sooooooo... it's not for lack of trying.. But I do NOT see how the Hide Categories query in here synchs up to the actual query being kicked out in the error message (let alone how to apply DrBytes fix.. )

Arrrrrrrrrrrrrrrrrrrrrrggggggggggggghhhhhhhh!!!

DrByte, I PROMISE you I am trying to learn how to fish on my own, but I REALLY am stuck.. **sigh** Hope you will still help..

Here's what I am looking at in my includes/templates/custom_template/templates/tpl_products_all_default.php file..

<?php
//  Begin hideCategories code
 $products_all_query_raw = preg_replace('/, ' . TABLE_PRODUCTS_DESCRIPTION . ' pd(\s*)WHERE/', ' LEFT JOIN ' . TABLE_HIDE_CATEGORIES . ' h ON (p.master_categories_id = h.categories_id), ' . TABLE_PRODUCTS_DESCRIPTION . ' pd WHERE (h.visibility_status < 2 OR h.visibility_status IS NULL) AND', $products_all_query_raw);
 $products_all_split = new splitPageResults($products_all_query_raw, MAX_DISPLAY_PRODUCTS_ALL);
//  End hideCategories code
?>

[DrByte]

 $products_all_query_raw = preg_replace('/, ' . TABLE_PRODUCTS_DESCRIPTION . ' pd(\s*)WHERE/', ' LEFT JOIN ' . TABLE_HIDE_CATEGORIES . ' h ON (p.master_categories_id = h.categories_id), ' . TABLE_PRODUCTS_DESCRIPTION . ' pd WHERE (h.visibility_status < 2 OR h.visibility_status IS NULL) AND', $products_all_query_raw);

Try replacing that line with this one:

  $products_all_query_raw = preg_replace('/, ' . TABLE_PRODUCTS_DESCRIPTION . ' pd(\s*)WHERE/', ', ' . TABLE_HIDE_CATEGORIES . ' h, ' . TABLE_PRODUCTS_DESCRIPTION . ' pd WHERE p.master_categories_id = h.categories_id AND (h.visibility_status < 2 OR h.visibility_status IS NULL) AND', $products_all_query_raw);

[DivaVocals]

Try replacing that line with this one:

  $products_all_query_raw = preg_replace('/, ' . TABLE_PRODUCTS_DESCRIPTION . ' pd(\s*)WHERE/', ', ' . TABLE_HIDE_CATEGORIES . ' h, ' . TABLE_PRODUCTS_DESCRIPTION . ' pd WHERE p.master_categories_id = h.categories_id AND (h.visibility_status < 2 OR h.visibility_status IS NULL) AND', $products_all_query_raw);

Made the change..
Here's what it looks like.. I think I got it right.. Right??

<?php
//  Begin hideCategories code
$products_all_query_raw = preg_replace('/, ' . TABLE_PRODUCTS_DESCRIPTION . ' pd(\s*)WHERE/', ', ' . TABLE_HIDE_CATEGORIES . ' h, ' . TABLE_PRODUCTS_DESCRIPTION . ' pd WHERE p.master_categories_id = h.categories_id AND (h.visibility_status < 2 OR h.visibility_status IS NULL) AND', $products_all_query_raw);
    $products_all_split = new splitPageResults($products_all_query_raw, MAX_DISPLAY_PRODUCTS_ALL);
//  End hideCategories code
?>

I got this:

1054 Unknown column 'p.master_categories_id' in 'on clause'
in:
[select count(p.products_id) as total FROM zen_products p LEFT JOIN zen_manufacturers m ON (p.manufacturers_id = m.manufacturers_id), zen_products_description pd LEFT JOIN zen_hide_categories h ON (p.master_categories_id = h.categories_id) WHERE (h.visibility_status < 2 OR h.visibility_status IS NULL) AND p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 ]

[DrByte]

Were you testing it on the products_all page?
Did your changes upload properly?