Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Featured Products problem


jlong07

Recommended Posts

I tried to use the featured products contribution and I ran into a couple of problems. The first I was able to find a topic discussing the fix for it, however once I applied the fix I got another error that said something to the effect of invalid table in statement p.product_id in the on clause. I am new to php and so I started poking around on the net for a solution. I found an article on this forum that talked about this problem being related to older versions of oscommerce running on mysql 5. I have the latest release of oscommerce, but my server is using mysql 5. I am thinking that the offending query in the featured.php file is written using the same syntax that causes problems with mysql 5, but being that I am very new to php I can't figure out how to change it. If anyone could help me out here that would be great.

Link to comment
Share on other sites

I tried to use the featured products contribution and I ran into a couple of problems. The first I was able to find a topic discussing the fix for it, however once I applied the fix I got another error that said something to the effect of invalid table in statement p.product_id in the on clause. I am new to php and so I started poking around on the net for a solution. I found an article on this forum that talked about this problem being related to older versions of oscommerce running on mysql 5. I have the latest release of oscommerce, but my server is using mysql 5. I am thinking that the offending query in the featured.php file is written using the same syntax that causes problems with mysql 5, but being that I am very new to php I can't figure out how to change it. If anyone could help me out here that would be great.

 

 

I am having the exact same problem it has to do with mysql 5.0

 

MySQL 5.0 introduces Server SQL modes as part of its SQL 2003 standards support, and uses a more stricter approach to executing SQL queries. This is performed by default with setting STRICT_TRANS_TABLES as a Server SQL mode.

 

Due to this new setting, MySQL fails on certain SQL queries and produces error messages on the screen.

 

The error is in catalog/includes/modules/featured.php somewhere in this string:

 

$featured_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, products_retail_price, 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 in (" . $featured_products_category_id_list . ") and p.products_status = '1' and f.status = '1' order by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS);

 

If anyone can help rewrite this I am sure we would all appreciate it. If you need a guide there are similar strings that had to be rewritten, check here.

 

http://www.oscommerce.com/ext/update-20051...l#_Toc119473689

Link to comment
Share on other sites

I found the answer here:

http://www.oscommerce.com/forums/index.php?showtopic=199498&hl=

 

replace the $featured_products_query in catalog/includes/modules/featured.php to make it work with mysql 5

 

 

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...