1054 Unknown column error after site went live

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/showpost.php?p=742447&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..

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

DrByte:

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..:clap:

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.

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

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.

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!!!:clap:

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.

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.. :frusty:)

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
?>

:frusty::frusty::frusty:

DivaVocals:

$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);

DrByte:

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
<?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 ]
:frusty::frusty:

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

DrByte:

Were you testing it on the products_all page?
Did your changes upload properly?Yep.. here's the page I edited:
includes/templates/custom_template/templates/tpl_products_all_default.php

I tested and confirmed that my changes uploaded properly.. What could I be doing wrong??:frusty:

Well, the query in the error message doesn't match the changes made in the file. I only gave you the changes based on assuming the line of code you provided was the one that was being called.

Maybe you should contact the author of the addon so they can chime in and post the complete solution?

DrByte:

Well, the query in the error message doesn't match the changes made in the file. I only gave you the changes based on assuming the line of code you provided was the one that was being called.

Maybe you should contact the author of the addon so they can chime in and post the complete solution?
sigh

Yep.. looks like that's what I'm gonna have to do.. I was HOPING this was something simple.. Alas it's not turning out this way.. :frusty:

DrByte thanks for your help.. Really.. I do appreciate it.. If and when I get a solution I will update this and the hideCategories thread.. Wanna share the love..:smile:

Well in the name of just not being able to let this go, and based on things DrByte and a programmer friend of mine said, I decided to look at ALL the files in the hideCategories add-on. I used the developers tools and decided to seach for that darn second join.. CLEARLY it wasn't in the product listing pages.. So I figured it had to be in another file that was being called by the "Products All" and Products New" pages somehow.. (Though I wasn't entirely sure about that..:laugh::laugh:)

Anyway I went hunting and found this code:

 
// Begin hideCategories code
$listing_sql = str_replace('WHERE', 'LEFT JOIN ' . TABLE_HIDE_CATEGORIES . ' h ON (p.master_categories_id = h.categories_id) WHERE (h.visibility_status < 2 OR h.visibility_status IS NULL) AND', $listing_sql);
// End hideCategories code

in this file: includes/modules/custom_template/product_listing.php.

So I commented this out in it's entirety, and can you guess what happened????

Yep.. The darn thing worked!!:clap:

Now I know that I'll have to do some more testing to make sure I didn't break anything by doing this.. but so far this appears to be the ticket.. Don't want say I'm feeling smart:smartalec: yet until I do some more testing..

Now I'll be honest I'm not entirely sure WHY this worked, because that would require me to have a better grasp on how this was all constructed in the first place..:laugh::laugh: I mean I do get that this was the likely source for the second join in the SQL statement.. Just don't really know why it was done this way in the first place.. Oh well.. More testing to do before I breathe completely easier.. So far it looks like the mystery is solved.. One I am 100% confident that this is indeed the solution, I'll cross refeence this post in the hideCategories support thread.. LIke I said.. wanna share the love..

Thanks to you DrByte for your help.. Truly it is appreciated..