1109 Unknown table 'p' in field list on Featured and All-prods listings

[creativeone]

I am running version v1.3.8a
Have added the following:
Cross Sell
Zen Lightbox (disabled though)
Reward Points
Fuel Slimbox
Ajax Image Swapper
IH2
Attribute Image

All pages work great except for when you click Featured Products or All Products links on the left navigation. Thats when I get this
1109 Unknown table 'p' in field list
in:
[select count(p.products_id) as total ]

I thought it might have something to do with attribute image addon. When I look in my MySQL database I see a table called zen_products_attributes_images when none of my other tables have zen_ It won't let me edit it to see if that is the problem. Any ideas if this might be the problem and how I can fix it? Thanks

[kuroi]

That the attribute image mod has left you with a table prefixed by zen_ is quite likely a problem, but not, I suspect, the problem causing the SQL error.

The SQL error appears to be because the SQL is prematurely truncated. "p" is a very commonly defined alias for the products table. But that (or indeed any table) is not declared in this SQL. Have you given us the entire error message?

[creativeone]

Yes that is the entire error message. It sits under the Sort drop down Here is the page to see it

http://jetpilotusa.com/index.php?main_page=products_all

[kuroi]

The error message doesn't appear to be coming from core Zen Cart code. I can't find any references to "count(p.products_id)" in that. So I suspect must be coming from a mod.

I don't believe that attribute images is to blame for either of your problems.

The AJAX image swapper is the source of the zen_ prefixed table, though it's difficult to see how with the latest version. Maybe this was an issue in older versions.

The origins of your SQL error are more of a mystery, since of your mods either don't go near the all and featured product listings, or are very well-established and reliable mods.

I recommend that you use the developper toolkit (Admin > Tools). To search for the term "count(p.products_id)" to narrow this down.

[creativeone]

When I used Developer Tool Kit and searched that under Catalog/Admin I see this

Line #140 : $result['product_check'] = $db->Execute( "select p.products_id, p.products_model, pd.products_name, count(p.products_id) as xsells from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_XSELL . " px ".

Line #162 : return $db->Execute( "select p.products_id, p.products_model, pd.products_name, count(p.products_id) as xsells from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_XSELL . " px " .


Could it be the cross sell module causing this issue?

[kuroi]

I think probably not. The cross-sell code that you have identified aliases the count as xsell (i.e. "count(p.products_id) as xsells") rather than as "total" which is what's appearing on your screen. And cross-sell isn't really interested in these listings.

Rather puzzling is that this sort of error would appear where it does at all, suggesting that there is some SQL being executed from within a template.

Did you build the base template yourself or get it from somewhere else? Does the problem still occur if you switch temporarily to classic?

[creativeone]

ahhhh. I think this was a free template that I found on the net and changed it a little. When I switch it to the classic green template, the featured products etc works great. hmmm.

[kuroi]

That narrows it down. The problem is clearly in one of your template files.

Is this one of those templates that replaces every template file or just a few?

Does it have tpl_products_all_default.php and tpl_featured_products_default.php?

[creativeone]

I don't think it replaces all the files but lots of them. It does have both tpl_products_all_default.php and tpl_featured_products_default.php in its template folder.

[kuroi]

Try temporarily changing the extension on tpl_products_all_default.php to .tmp and see what effect that has.

(I don't recommend this as a permanent solution and at some stage it should be renamed back if it's needed or deleted if it's not)