PHP Fatal error: 1109: Unknown table 'p' in field list

I constantly get the following error after 24/48 hours on my site which prevents both the site and admin areas from functioning at all. This requires a complete reinstall of ZenCart, and a restore of the database.

PHP Fatal error: 1109:Unknown table 'p' in field list :: select count(p.products_id) as total ==> (as called by) /home/black137/public_html/includes/classes/split_page_results.php on line 78 <== in /home/black137/public_html/includes/classes/db/mysql/query_factory.php on line 155

I have used the Developers toolkit to search for **'select count(p.products_id) as total' **and this is not found, I have also used Dreamweaver to search the entire site and this is not found in any file. I am unsure why this being thrown-up but maybe somebody here has encountered this problem and/or can advise how I overcome this problem.

Bob

Hi All,

I have discovered that this problem is thrown up ONLY when somebody clicks the 'All Products' link on the site!

Any help would be much appreciated.

bobc:

Hi All,

I have discovered that this problem is thrown up ONLY when somebody clicks the 'All Products' link on the site!

Any help would be much appreciated.

and what modules have you installed??? because this is likely some issue cause by a bad module install versus Zen Cart out the box..

Are you able to reproduce this error every time you you click on All Products?

And each time, does it generate the exact same debug log that you posted:

PHP Fatal error:  1109:Unknown table 'p' in field list :: select  count(p.products_id) as total  ==> (as called by)  /home/black137/public_html/includes/classes/split_page_results.php on  line 78 <== in  /home/black137/public_html/includes/classes/db/mysql/query_factory.php  on line 155

In answer to Diva Vocals - The only addon module I have is DHL Express XML.

In Answer to Ajeh - Yes it is the same error each time the 'All Products' is clicked. And each time it is clicked, I have start again with the ZC Install, and re-import the database! For the moment, I have swithched off the 'All products' Link.

Very odd ... I was expecting this error from either the Product Listing or the Advanced Search or the Search ...

If you get bored, with nothing to do, and want to hunt this down more, you could make a Test site with a Test database so that this could be examined further ...

You could also try installing myDEBUG Backtrace (http://www.zen-cart.com/downloads.php?do=file&id=1879), which will further identify the errant caller of the split_page_result.php class function.

bobc:

Hi All,

I have discovered that this problem is thrown up ONLY when somebody clicks the 'All Products' link on the site!

Any help would be much appreciated.

This error points at includes/classes/split_page_results.php
line 74 - 78
$count_query = "select count(" . $count_string . ") as total " . substr($this->countQuery, $pos_from, ($pos_to - $pos_from));
if ($debug) {
echo 'count_query=' . $count_query . '<br /><br />';
}
$count = $db->Execute($count_query);

Clicking that button pulls these files:

includes/templates/YOUR_TEMPLATE/templates/tpl_products_all_default.php
line 63

includes/templates/YOUR_TEMPLATE/templates/tpl_modules_products_all_listing.php
line 22 - 23 if ($products_all_split->number_of_rows > 0) {
$products_all = $db->Execute($products_all_split->sql_query);includes/modules/pages/products_all/header_php.php
line 66
if ( (($how_many > 0 and $show_submit == true and $products_all_split->number_of_rows > 0) and (PRODUCT_ALL_LISTING_MULTIPLE_ADD_TO_CART == 1 or PRODUCT_ALL_LISTING_MULTIPLE_ADD_TO_CART == 3)) ) {

What are the chances your template changed any of these files or what happens when you switch back to the classic template with the products_all turned on?

Additionally, in Admin->Configuration->Maximum Values, what's your setting for Maximum Display of Products All Page ?
And also all the settings in Admin->Configuration->All Listing ?

twitchtoo:

This error points at includes/classes/split_page_results.php
line 74 - 78
$count_query = "select count(" . $count_string . ") as total " . substr($this->countQuery, $pos_from, ($pos_to - $pos_from));
if ($debug) {
echo 'count_query=' . $count_query . '<br /><br />';
}
$count = $db->Execute($count_query);

Clicking that button pulls these files:

includes/templates/YOUR_TEMPLATE/templates/tpl_products_all_default.php
line 63

<div id="allProductsListingBottomLinks" class="navSplitPagesLinks forward"><?php echo TEXT_RESULT_PAGE . ' ' . **$products_all_split**->display_links(MAX_DISPLAY_PAGE_LINKS, zen_get_all_get_params(array('page', 'info', 'x', 'y', 'main_page'))); ?></div>

includes/templates/YOUR_TEMPLATE/templates/tpl_modules_products_all_listing.php
line 22 - 23 if ($products_all_split->number_of_rows > 0) {
$products_all = $db->Execute($products_all_split->sql_query);includes/modules/pages/products_all/header_php.php
line 66
if ( (($how_many > 0 and $show_submit == true and $products_all_split->number_of_rows > 0) and (PRODUCT_ALL_LISTING_MULTIPLE_ADD_TO_CART == 1 or PRODUCT_ALL_LISTING_MULTIPLE_ADD_TO_CART == 3)) ) {

What are the chances your template changed any of these files or what happens when you switch back to the classic template with the products_all turned on?

I have the same problem when “All products or Special products” are clicked
I am using Winchester responsive template, Zen cart v1.5.5d, Ceon Advanced Shipper 5.0.2 and Sitemap XML
Lines 74-78 in includes/classes/split_page_results.php show:

$count_query = "select count(" . $count_string . ") as total " . substr($this->countQuery, $pos_from, ($pos_to - $pos_from));
if ($debug) {
  echo 'count_query=' . $count_query . '<br /><br />';
}
$count = $db->Execute($count_query); 

includes/templates/YOUR_TEMPLATE/templates/tpl_products_all_default.php shows:

<div class="centerColumn" id="allProductsDefault">
<h1 id="allProductsDefaultHeading"><?php echo HEADING_TITLE; ?></h1>
<?php
require($template->get_template_dir('/tpl_modules_listing_display_order.php',DIR_WS_TEMPLATE, $current_page_base,'templates'). '/tpl_modules_listing_display_order.php');
require($template->get_template_dir('tpl_modules_product_listing.php',DIR_WS_TEMPLATE, $current_page_base,'templates'). '/tpl_modules_product_listing.php');
?>
</div>

lines 22-23 in includes/templates/YOUR_TEMPLATE/templates/tpl_modules_products_all_listing.php shows:

if ($products_all_split->number_of_rows > 0) {
  $products_all = $db->Execute($products_all_split->sql_query);

includes/modules/pages/products_all/header_php.php
line 66-74 shows:

if ( (($how_many > 0 and $show_submit == true and $products_all_split->number_of_rows > 0) and (PRODUCT_ALL_LISTING_MULTIPLE_ADD_TO_CART == 1 or  PRODUCT_ALL_LISTING_MULTIPLE_ADD_TO_CART == 3)) ) {
  $show_top_submit_button = true;
} else {
  $show_top_submit_button = false;
}
if ( (($how_many > 0 and $show_submit == true and $products_all_split->number_of_rows > 0) and (PRODUCT_ALL_LISTING_MULTIPLE_ADD_TO_CART >= 2)) ) {
  $show_bottom_submit_button = true;
} else {
  $show_bottom_submit_button = false;
}

If I change to the default template, the products display fine without a myDEBUG error.

its pretty apparent that picaflor-azul in her 10-oct-2016 release of winchester responsive is using v1.5.1 of

includes/classes/split_page_results.php

(its dated at the top... v1.5.1, aug 17, 2012)

you can replace with the ZC version that your are running, and try to re-incorporate her changes. else you can post in the support thread for that template that she (or someone) needs to update the split_page_results class.

good luck.

carlwhat:

its pretty apparent that picaflor-azul in her 10-oct-2016 release of winchester responsive is using v1.5.1 of

includes/classes/split_page_results.php

(its dated at the top... v1.5.1, aug 17, 2012)

you can replace with the ZC version that your are running, and try to re-incorporate her changes. else you can post in the support thread for that template that she (or someone) needs to update the split_page_results class.

good luck.

This is what my includes/classes/split_page_results.php file starts with

<?php
/**
 * split_page_results Class.
 *
 * @package classes
 * @copyright Copyright 2003-2016 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: Author: DrByte  Sat Jan 9 13:13:41 2016 -0500 Modified in v1.5.5 $
 */
if (!defined('IS_ADMIN_FLAG')) {
  die('Illegal Access');
}
/**

interesting....

turn debug on, and then post all of the results. it looks like debug should send them to the screen.

carlwhat:

interesting....

turn debug on, and then post all of the results. it looks like debug should send them to the screen.

#1  trigger_error() called at [/home/user/mywebsite.com/includes/classes/db/mysql/query_factory.php:167]
#2  queryFactory->show_error() called at [/home/user/mywebsite.com/includes/classes/db/mysql/query_factory.php:139]
#3  queryFactory->set_error() called at [/home/user/mywebsite.com/includes/classes/db/mysql/query_factory.php:266]
#4  queryFactory->Execute() called at [/home/user/mywebsite.com/includes/classes/split_page_results.php:78]
#5  splitPageResults->__construct() called at [/home/user/mywebsite.com/includes/modules/winchester_responsive/product_listing.php:35]
#6  include(/home/user/mywebsite.com/includes/modules/winchester_responsive/product_listing.php) called at [/home/user/mywebsite.com/includes/templates/winchester_responsive/templates/tpl_modules_product_listing.php:14]
#7  require(/home/user/mywebsite.com/includes/templates/winchester_responsive/templates/tpl_modules_product_listing.php) called at [/home/user/mywebsite.com/includes/templates/winchester_responsive/templates/tpl_specials_default.php:18]
#8  require(/home/user/mywebsite.com/includes/templates/winchester_responsive/templates/tpl_specials_default.php) called at [/home/user/mywebsite.com/includes/modules/pages/specials/main_template_vars.php:53]
#9  require(/home/user/mywebsite.com/includes/modules/pages/specials/main_template_vars.php) called at [/home/user/mywebsite.com/includes/templates/winchester_responsive/common/tpl_main_page.php:253]
#10 require(/home/user/mywebsite.com/includes/templates/winchester_responsive/common/tpl_main_page.php) called at [/home/user/mywebsite.com/index.php:97]

[17-Jan-2017 15:11:43 America/Los_Angeles] PHP Fatal error:  1109:Unknown table 'p' in field list :: select count(p.products_id) as total  ==> (as called by) /home/user/mywebsite.com/includes/classes/split_page_results.php on line 78 <== in /home/user/mywebsite.com/includes/classes/db/mysql/query_factory.php on line 167

those are debug logs. not what i was asking for. on lines 35-38, we have:

    if ($debug) {
      echo '<br /><br />';
      echo 'original_query=' . $query . '<br /><br />';
      echo 'original_count_query=' . $countQuery . '<br /><br />';
      echo 'sql_query=' . $this->sql_query . '<br /><br />';
      echo 'count_query=' . $this->countQuery . '<br /><br />';
    }

$debug needs to be true so that we can see all of these elements. they will display to the screen....

unfortunately, not quite sure how that gets turned on. you can just add the following line just before it and then remove it after we get the values:

$debug = true;

then please post what you see on the screen.

carlwhat:

those are debug logs. not what i was asking for. on lines 35-38, we have:

if ($debug) {
  echo '<br /><br />';
  echo 'original_query=' . $query . '<br /><br />';
  echo 'original_count_query=' . $countQuery . '<br /><br />';
  echo 'sql_query=' . $this->sql_query . '<br /><br />';
  echo 'count_query=' . $this->countQuery . '<br /><br />';
}

> 
> $debug needs to be true so that we can see all of these elements.  they will display to the screen....
> 
> unfortunately, not quite sure how that gets turned on.  you can just add the following line just before it and then remove it after we get the values:
> 
> $debug = true;
> 
> then please post what you see on the screen.```
original_query=SELECT p.products_id, p.products_image, pd.products_name, p.master_categories_id FROM (products p LEFT JOIN specials s on p.products_id = s.products_id LEFT JOIN products_description pd on p.products_id = pd.products_id ) WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' AND s.status = 1 AND pd.language_id = 1 ORDER BY s.specials_date_added DESC

original_count_query=

sql_query=SELECT p.products_id, p.products_image, pd.products_name, p.master_categories_id FROM (products p LEFT JOIN specials s on p.products_id = s.products_id LEFT JOIN products_description pd on p.products_id = pd.products_id ) WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' AND s.status = 1 AND pd.language_id = 1 ORDER BY s.specials_date_added DESC

count_query=SELECT p.products_id, p.products_image, pd.products_name, p.master_categories_id FROM (products p LEFT JOIN specials s on p.products_id = s.products_id LEFT JOIN products_description pd on p.products_id = pd.products_id ) WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' AND s.status = 1 AND pd.language_id = 1 ORDER BY s.specials_date_added DESC

count_query=select count(*) as total FROM (products p LEFT JOIN specials s on p.products_id = s.products_id LEFT JOIN products_description pd on p.products_id = pd.products_id ) WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' AND s.status = 1 AND pd.language_id = 1 

ok. GREAT stuff....

i'm not sure you are on the page that creates the error. this looks to be the specials page. and the problem is coming from the all-products page.

looking at the "code" from the debug shows the 2 lines for the variable, $count_query. they are the last 2 lines. the first line is echoed on line 40; the second line is echoed on line 76. and from the debug logs, it is line 78 is when we get the error. and line 78 is executing the $count_query.

from the debug logs, we have the following error:

Unknown table 'p' in field list :: select count(p.products_id) as total

and yet from the display of $count_query we have:

select count(*) as total FROM.....

the sql statements are different.... so from my standpoint, we can not be on the page that creates the error.

in addition, if we were to run the complete sql statement contained in the variable $count_query using phpmyadmin or any other sql db interface:

select count(*) as total FROM (products p LEFT JOIN specials s on p.products_id = s.products_id LEFT JOIN products_description pd on p.products_id = pd.products_id ) WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' AND s.status = 1 AND pd.language_id = 1

my test dataset returns completely fine.

lets get the same 4 debug outputs from the page that creates the error.

carlwhat:

ok. GREAT stuff....

i'm not sure you are on the page that creates the error. this looks to be the specials page. and the problem is coming from the all-products page.

looking at the "code" from the debug shows the 2 lines for the variable, $count_query. they are the last 2 lines. the first line is echoed on line 40; the second line is echoed on line 76. and from the debug logs, it is line 78 is when we get the error. and line 78 is executing the $count_query.

from the debug logs, we have the following error:

Unknown table 'p' in field list :: select count(p.products_id) as total

and yet from the display of $count_query we have:

select count(*) as total FROM.....

the sql statements are different.... so from my standpoint, we can not be on the page that creates the error.

in addition, if we were to run the complete sql statement contained in the variable $count_query using phpmyadmin or any other sql db interface:

select count(*) as total FROM (products p LEFT JOIN specials s on p.products_id = s.products_id LEFT JOIN products_description pd on p.products_id = pd.products_id ) WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' AND s.status = 1 AND pd.language_id = 1

my test dataset returns completely fine.

lets get the same 4 debug outputs from the page that creates the error.
This error triggers on the Specials,New Products,All Products and featured Product pages. When I run the statements via phpmyadmin, they all produce results

All products link:

original_query=SELECT p.products_type, p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 order by pd.products_name

original_count_query=

sql_query=SELECT p.products_type, p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 order by pd.products_name

count_query=SELECT p.products_type, p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 order by pd.products_name

count_query=select count(*) as total FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1

New products link:

original_query=SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 order by p.products_date_added DESC, pd.products_name

original_count_query=

sql_query=SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 order by p.products_date_added DESC, pd.products_name

count_query=SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 order by p.products_date_added DESC, pd.products_name

count_query=select count(*) as total FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1

Featured products:

original_query=SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM (products p LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd LEFT JOIN featured f on pd.products_id = f.products_id ) WHERE p.products_status = 1 and p.products_id = f.products_id and f.status = 1 AND p.products_id = pd.products_id and pd.language_id = 1 order by pd.products_name

original_count_query=

sql_query=SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM (products p LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd LEFT JOIN featured f on pd.products_id = f.products_id ) WHERE p.products_status = 1 and p.products_id = f.products_id and f.status = 1 AND p.products_id = pd.products_id and pd.language_id = 1 order by pd.products_name

count_query=SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM (products p LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd LEFT JOIN featured f on pd.products_id = f.products_id ) WHERE p.products_status = 1 and p.products_id = f.products_id and f.status = 1 AND p.products_id = pd.products_id and pd.language_id = 1 order by pd.products_name

count_query=select count(*) as total FROM (products p LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd LEFT JOIN featured f on pd.products_id = f.products_id ) WHERE p.products_status = 1 and p.products_id = f.products_id and f.status = 1 AND p.products_id = pd.products_id and pd.language_id = 1

i think you have a mysql version incompatibility.

see:

https://www.zen-cart.com/content.php?48-what-are-the-server-requirements-to-run-zen-cart

v1.5.4 requires mysql 5.1 - 5.4

if you have mysqlnd 5.0.11-dev, that could possibly explain the error.

talk to your host about upgrading the mysql version.

It appears that at the moment I am stuck using mysqlnd 5.0.11-dev. I go through DreamHost and I believe that I would be required to get a VPS in order to get upgraded to v5.6, I submitted a ticket to find out my options.
I have another website v1.5.4 that uses the same template and is using mysqlnd 5.0.11-dev and it works perfectly fine. I think my only option at this point is to go back to the original version and only upgrade to v1.5.4 instead of v1.5.5.

Thank you so much for your insight and wisdom Carlwhat, I enjoyed the debugging lesson.