Featured Products (1.5.4_1_2) + 1054 - Unknown column 'p.products_id' in 'on clause'

Roger_Sweden · March 4, 2007

Hello, Ive managed to update most of my shop for php5, with the exception for the Featured Products part :(

Where the featured products are supposed to be displayed I get the following error message

1054 - Unknown column 'p.products_id' in 'on clause'

select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join featured f on p.products_id = f.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '' and p.products_status = '1' and f.status = '1' order by rand() DESC limit 9

[TEP STOP]

My includes/modules/featured.php file (which I think is the one needing to be updated) looks like this

<?php
/*
 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License

 Featured Products V1.1
 Displays a list of featured products, selected from admin
 For use as an Infobox instead of the "New Products" Infobox  
*/
?>
<!-- featured_products //-->
<?php
if(FEATURED_PRODUCTS_DISPLAY == 'true')
{
 $featured_products_category_id = $new_products_category_id;
 $cat_name_query = tep_db_query("select categories_name from categories_description where categories_id = '" . $featured_products_category_id . "' limit 1");
 $cat_name_fetch = tep_db_fetch_array($cat_name_query);
 $cat_name = $cat_name_fetch['categories_name'];
 $info_box_contents = array();

 if ( (!isset($featured_products_category_id)) || ($featured_products_category_id == '0') ) {
//$info_box_contents[] = array('align' => 'left', 'text' => '<a class="headerNavigation" href="' . tep_href_link(FILENAME_FEATURED_PRODUCTS) . '">' . TABLE_HEADING_FEATURED_PRODUCTS . '</a>');
$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_FEATURED_PRODUCTS));
$featured_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c left join " . TABLE_FEATURED . " f on p.products_id = f.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . $featured_products_category_id . "' and p.products_status = '1' and f.status = '1' order by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);

$featured_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_FEATURED . " f on p.products_id = f.products_id where p.products_status = '1' and f.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);
 } else {
$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_FEATURED_PRODUCTS_CATEGORY, $cat_name));
$featured_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_CATEGORIES . " c using(categories_id) left join " . TABLE_FEATURED . " f on p.products_id = f.products_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where c.parent_id = '" . $featured_products_category_id . "' and p.products_status = '1' and f.status = '1' order by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);
}

 $row = 0;
 $col = 0; 
 $num = 0;
 while ($featured_products = tep_db_fetch_array($featured_products_query)) {
$num ++; if ($num == 1) { new featBoxHeading($info_box_contents); }
$featured_products['products_name'] = tep_get_products_name($featured_products['products_id']);
if($featured_products['specstat']) {
  $info_box_contents[$row][$col] = array('align' => 'center',
									   'params' => 'class="smallText2 . infoBox2" width="33%" valign="bottom"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $featured_products['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $featured_products['products_image'], $featured_products['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $featured_products['products_id']) . '">' . $featured_products['products_name'] . '</a><br><s>' . $currencies->display_price($featured_products['<products_price'], tep_get_tax_rate($featured_products['products_tax_class_id'])) . '</s><br><span class="productSpecialPrice">' . 
									   $currencies->display_price($featured_products['specials_new_products_price'], tep_get_tax_rate($featured_products['products_tax_class_id'])) . '</span>');
} else {
  $info_box_contents[$row][$col] = array('align' => 'center',
									   'params' => 'class="smallText2 . infoBox2" width="33%" valign="bottom"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $featured_products['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $featured_products['products_image'], $featured_products['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $featured_products['products_id']) . '">' . $featured_products['products_name'] . '</a><br><font color="#FF0000">' . $currencies->display_price($featured_products['products_price'], tep_get_tax_rate($featured_products['products_tax_class_id'])) . '</font color>');
}	
$col ++;
if ($col > 2) {
  $col = 0;
  $row ++;
}
 }
 if($num) {

  new featBox($info_box_contents);
 }
} else // If it's disabled, then include the original New Products box
{
  include (DIR_WS_MODULES . FILENAME_NEW_PRODUCTS);
}
?>
<!-- featured_products_eof //-->

If anyone could help me fix this I'd be very grateful :)

March 4, 2007

2 options

1.) remove the 'p.products_id' from the featured_products.php page

2.) in phpmyadmin do this:

ALTER TABLE `featured` ADD `products_id` int(11) default '0' not null AFTER `featured_id`;

This will fix your problem. I recommend option 2.

Hello, Ive managed to update most of my shop for php5, with the exception for the Featured Products part :(

Where the featured products are supposed to be displayed I get the following error message

1054 - Unknown column 'p.products_id' in 'on clause'

select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join featured f on p.products_id = f.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '' and p.products_status = '1' and f.status = '1' order by rand() DESC limit 9

[TEP STOP]

My includes/modules/featured.php file (which I think is the one needing to be updated) looks like this

<?php
/*
 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License

 Featured Products V1.1
 Displays a list of featured products, selected from admin
 For use as an Infobox instead of the "New Products" Infobox  
*/
?>
<!-- featured_products //-->
<?php
if(FEATURED_PRODUCTS_DISPLAY == 'true')
{
 $featured_products_category_id = $new_products_category_id;
 $cat_name_query = tep_db_query("select categories_name from categories_description where categories_id = '" . $featured_products_category_id . "' limit 1");
 $cat_name_fetch = tep_db_fetch_array($cat_name_query);
 $cat_name = $cat_name_fetch['categories_name'];
 $info_box_contents = array();

 if ( (!isset($featured_products_category_id)) || ($featured_products_category_id == '0') ) {
//$info_box_contents[] = array('align' => 'left', 'text' => '<a class="headerNavigation" href="' . tep_href_link(FILENAME_FEATURED_PRODUCTS) . '">' . TABLE_HEADING_FEATURED_PRODUCTS . '</a>');
$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_FEATURED_PRODUCTS));
$featured_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c left join " . TABLE_FEATURED . " f on p.products_id = f.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . $featured_products_category_id . "' and p.products_status = '1' and f.status = '1' order by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);

$featured_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_FEATURED . " f on p.products_id = f.products_id where p.products_status = '1' and f.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);
 } else {
$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_FEATURED_PRODUCTS_CATEGORY, $cat_name));
$featured_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_CATEGORIES . " c using(categories_id) left join " . TABLE_FEATURED . " f on p.products_id = f.products_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where c.parent_id = '" . $featured_products_category_id . "' and p.products_status = '1' and f.status = '1' order by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);
}

 $row = 0;
 $col = 0; 
 $num = 0;
 while ($featured_products = tep_db_fetch_array($featured_products_query)) {
$num ++; if ($num == 1) { new featBoxHeading($info_box_contents); }
$featured_products['products_name'] = tep_get_products_name($featured_products['products_id']);
if($featured_products['specstat']) {
  $info_box_contents[$row][$col] = array('align' => 'center',
									   'params' => 'class="smallText2 . infoBox2" width="33%" valign="bottom"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $featured_products['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $featured_products['products_image'], $featured_products['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $featured_products['products_id']) . '">' . $featured_products['products_name'] . '</a><br><s>' . $currencies->display_price($featured_products['<products_price'], tep_get_tax_rate($featured_products['products_tax_class_id'])) . '</s><br><span class="productSpecialPrice">' . 
									   $currencies->display_price($featured_products['specials_new_products_price'], tep_get_tax_rate($featured_products['products_tax_class_id'])) . '</span>');
} else {
  $info_box_contents[$row][$col] = array('align' => 'center',
									   'params' => 'class="smallText2 . infoBox2" width="33%" valign="bottom"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $featured_products['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $featured_products['products_image'], $featured_products['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $featured_products['products_id']) . '">' . $featured_products['products_name'] . '</a><br><font color="#FF0000">' . $currencies->display_price($featured_products['products_price'], tep_get_tax_rate($featured_products['products_tax_class_id'])) . '</font color>');
}	
$col ++;
if ($col > 2) {
  $col = 0;
  $row ++;
}
 }
 if($num) {

  new featBox($info_box_contents);
 }
} else // If it's disabled, then include the original New Products box
{
  include (DIR_WS_MODULES . FILENAME_NEW_PRODUCTS);
}
?>
<!-- featured_products_eof //-->

If anyone could help me fix this I'd be very grateful :)

March 4, 2007

2 options

1.) remove the 'p.products_id' from the featured_products.php page

2.) in phpmyadmin do this:

ALTER TABLE `featured` ADD `products_id` int(11) default '0' not null AFTER `featured_id`;

This will fix your problem. I recommend option 2.

and why you think the products_id is missing? It's not, and the result will be:

MySQL said:

#1060 - Duplicate column name 'products_id'

And the products_id is required for the module so if you remove it you will break the store.

Check the support thread for the contribution, or download a later version of this module. its mysql5x issue. I believe v1.5.9 should not have the problem

lubo · April 11, 2008

and why you think the products_id is missing? It's not, and the result will be:

MySQL said:

#1060 - Duplicate column name 'products_id'

And the products_id is required for the module so if you remove it you will break the store.

Check the support thread for the contribution, or download a later version of this module. its mysql5x issue. I believe v1.5.9 should not have the problem

yes.. I had experienced this issue too.. the problem is probably in mysql query syntax.. this worked for me:

around line 31 change the $featured_products_query to:

    $featured_products_query = tep_db_query("select distinct p.products_id, p.products_image,
p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price from " . 
TABLE_PRODUCTS . " as p left join " . TABLE_SPECIALS . " as s on (p.products_id = s.products_id) left join " .
TABLE_PRODUCTS_TO_CATEGORIES . " as p2c on (p2c.products_id = p.products_id) left join " . TABLE_CATEGORIES . "
as c on (c.categories_id = p2c.categories_id) left join " . TABLE_FEATURED . " as f on f.products_id = p.products_id 
where c.parent_id = '" . $featured_products_category_id . "' and p.products_status = '1' and f.status = '1' order 
by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);

(this code is multiline so maybe 'Copy & Paste' will not work, it has to be one line of code)

FixItPete · September 25, 2008

Ok... So I'm not the only one... but it looks like the 2 choices given were poo-poo'ed...

FixItPete · September 25, 2008

1.5.9...

I tried to install it over the older version I have. Still same problem.

Has anyone been able to get Featured Products to work with mySQL 5?

If so how? Which version of Featured Products are you running?

Featured Products (1.5.4_1_2) + 1054 - Unknown column 'p.products_id' in 'on clause'

Recommended Posts

Roger_Sweden

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

lubo

Link to comment

Share on other sites

FixItPete

Link to comment

Share on other sites

FixItPete

Link to comment

Share on other sites

Archived

Browse

Activity