jlong07 Posted April 7, 2006 Share Posted April 7, 2006 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. Quote Link to comment Share on other sites More sharing options...
saho Posted April 7, 2006 Share Posted April 7, 2006 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 Quote Link to comment Share on other sites More sharing options...
saho Posted April 7, 2006 Share Posted April 7, 2006 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); Quote Link to comment Share on other sites More sharing options...
jlong07 Posted April 9, 2006 Author Share Posted April 9, 2006 This did not fix my problem. I am still having the same issue. Any other suggestions? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.