websta Posted March 29, 2008 Share Posted March 29, 2008 tthorpe, you are my hero. Thank you! Link to comment Share on other sites More sharing options...
insaini Posted March 29, 2008 Share Posted March 29, 2008 Hello I know this topic is kinda old.. anyway I'm still hoping for someone to help me here on this... Im having the same issue as xchido, I've posted in new Topics but no one is replying :( anyway if anyone can really help me send me a PM or anything...I'll really appreciate :'( Thanks Darp Im not sure if all you are having this same problem.. but it seems as though its all centered on the 'on clause' apparently :P .. well can you guys try this.. For Example select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '61' order by p.products_date_added DESC limit 2 Try using parenthesis select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from ( products_description pd, products p ) left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '61' order by p.products_date_added DESC limit 2 See the two parenthesis (products_description pd, products p ) after the FROM clause and before the JOIN clauses .. try that.. insaini Link to comment Share on other sites More sharing options...
Stewart Gilray Posted April 10, 2008 Share Posted April 10, 2008 Hey everyone I'm having a similar problem, but ONLY if you go to the "My Account" (/account.php) page... here's the exact error ... 1054 - Unknown column 's.public_flag' in 'where clause' select count(*) as total from orders o, orders_status s where o.customers_id = '3' and o.orders_status = s.orders_status_id and s.language_id = '1' and s.public_flag = '1' [TEP STOP] Any ideas? we're meant to go live tonight with our update so timing just sucks! Link to comment Share on other sites More sharing options...
Guest Posted April 10, 2008 Share Posted April 10, 2008 If you're using or upgraded to the rc version did you do this step? Database changesAdd indexes to increase performance Add public and download status flag fields to the orders_status table Increase payment_method field size on the orders table Increase the last_page_url field size on the whos_online table [code]alter table banners add index idx_banners_group (banners_group); alter table banners_history add index idx_banners_history_banners_id (banners_id); alter table currencies add index idx_currencies_code (code); alter table customers add index idx_customers_email_address (customers_email_address); alter table customers_basket add index idx_customers_basket_customers_id (customers_id); alter table customers_basket_attributes add index idx_customers_basket_att_customers_id (customers_id); alter table orders add index idx_orders_customers_id (customers_id); alter table orders_products add index idx_orders_products_orders_id (orders_id); alter table orders_products add index idx_orders_products_products_id (products_id); alter table orders_status_history add index idx_orders_status_history_orders_id (orders_id); alter table orders_products_attributes add index idx_orders_products_att_orders_id (orders_id); alter table orders_products_download add index idx_orders_products_download_orders_id (orders_id); alter table products add index idx_products_model (products_model); alter table products_attributes add index idx_products_attributes_products_id (products_id); alter table reviews add index idx_reviews_products_id (products_id); alter table reviews add index idx_reviews_customers_id (customers_id); alter table specials add index idx_specials_products_id (products_id); alter table zones add index idx_zones_to_geo_zones_country_id (zone_country_id); alter table orders_status add public_flag int DEFAULT '1'; alter table orders_status add downloads_flag int DEFAULT '0'; alter table orders modify payment_method varchar(255) NOT NULL; alter table whos_online modify last_page_url text NOT NULL;[/code] Link to comment Share on other sites More sharing options...
Stewart Gilray Posted April 10, 2008 Share Posted April 10, 2008 Ah I didn't... I now have, and guess what ;-) 100% thanks Link to comment Share on other sites More sharing options...
ComtriS Posted April 27, 2008 Share Posted April 27, 2008 Tried everything from the above. This is what I'm getting on Search... 1054 - Unknown column 'p.products_id' in 'on clause' select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%search%' or p.products_model like '%search%' or m.manufacturers_name like '%search%') ) Everything else on the site is working. Only started noticing this after my host switched to a new platform (presumably using the newest versions of PHP/MySql). Any help appreciated. Link to comment Share on other sites More sharing options...
Guest Posted May 4, 2008 Share Posted May 4, 2008 After applying Monika's fix to my site I'm experiencing a wierd glitch... check it out... http://www.outdoorexperience.us/store/index.php?cPath=26 the n's under the Catagories heading. can anyone give me an idea of what I need to do to get rid of them. thank you. Where did you find the patch? I have been searching for over an hour now, and no luck. The search function on this board is not very good Link to comment Share on other sites More sharing options...
Digerdoo Posted May 6, 2008 Share Posted May 6, 2008 Where did you find the patch? I have been searching for over an hour now, and no luck. The search function on this board is not very good I use contribution called star products and this error comes how i can fix it ? 1054 - Unknown column 'p.products_id' in 'on clause' select substring(pd.products_description, 1, 598) as products_description, p.products_id, p.products_image, p.manufacturers_id, p.products_price, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price, p.products_tax_class_id, sp.product_id from products p, products_description pd, ACER01 sp left join specials s on p.products_id = s.products_id where p.products_id = pd.products_id and p.products_status = '1' and pd.products_description != '' and p.products_id=sp.product_id and pd.language_id = '4' [TEP STOP] :blink: And code here: <?php function tep_ACER01_with_attributes($products_id) { $attributes_query = tep_db_query("select count(*) as count from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id = '" . (int)$products_id . "'"); $attributes = tep_db_fetch_array($attributes_query); if ($attributes['count'] > 0) { return true; } else { return false; } } $star_products_query = tep_db_query("select substring(pd.products_description, 1, 240) as products_description, p.products_id, p.products_image, p.manufacturers_id, p.products_price, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price, p.products_tax_class_id, sp.product_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_ACER01 . " sp left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_id = pd.products_id and p.products_status = '1' and pd.products_description != '' and p.products_id=sp.product_id and pd.language_id = '" . $languages_id . "'"); $star_products = tep_db_fetch_array($star_products_query); $star_products['products_name'] = tep_get_products_name($star_products['products_id']); $evita_cortar_palabras = explode( ' ', $star_products["products_description"] ); array_pop( $evita_cortar_palabras ); $star_products["products_description"] = implode( ' ', $evita_cortar_palabras ); if (!tep_ACER01_with_attributes($star_products['products_id'])) { $star_products["0"] = array('align' => 'center', 'params' => 'width="100%" height="100%" valign="top"', 'text' => ' <table border=0 WIDTH="100%"> <tr> <td align="center" valign="top"> <a href="' . tep_href_link(FILENAME_POPUP_IMAGE, 'pID=' . $star_products['products_id']) . '"' . ' target="popup"' . ' onClick="window.open(this.href, this.target, \'toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=yes, copyhistory=no, width=' . SMALL_IMAGE_WIDTH . ', height=' . SMALL_IMAGE_HEIGHT . '\').focus(); return false;">' . tep_image(DIR_WS_IMAGES . $star_products['products_image'], $star_products['products_name'], 0,200) . '</a> </td> </tr> <tr> <td width="100%" nowrap class="star-product-title01">' . $star_products['products_name'] . '<br></td> </tr> <td width="350" height="100" ALIGN=left VALIGN=TOP nowrap class="star-product01">'. $star_products["products_description"] . '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, "products_id=" . $star_products["products_id"]). '">' . STAR_READ_MORE . '</a> </td> </td> <tr> <td ALIGN=center VALIGN=TOP class="star-product-price01"> '.$currencies->display_price($star_products['final_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . ' <br> </td> </tr> <tr> <td align=center valign=top> <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, "products_id=" . $star_products["products_id"]). '">' . tep_image(DIR_WS_LANGUAGES . $language . '/images/buttons/more_info.gif',IMAGE_BUTTON_MOREINFO) . '</a>' . tep_draw_separator('pixel_trans.gif', '10', '10') . '<a href="' . tep_href_link(FILENAME_KIRJAUDU, tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $star_products['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_IN_CART) . '</a> </td> </tr> </table> <br> '); new contentBox($star_products); } ?> Link to comment Share on other sites More sharing options...
dallascaley Posted May 30, 2008 Share Posted May 30, 2008 Thats seems simple to follow, however as using a template and getting these errors, I have found that I dont have the code that they are stating to replace in the index.php Any ideas? i just spent the last week digging through my code to figure this out. i had the same problem as you and couldn't find the proper code to swap out. i did finally find it though in this file: templates/content/advanced_search_result.tpl.php it should start around line 80. hope this helps, it worked for me. Link to comment Share on other sites More sharing options...
broomfish Posted July 18, 2008 Share Posted July 18, 2008 I had this problem that affected my advanced search results and manufacturers sections I downloaded the latest version of oscommerce and uploaded the advanced_search_results.php which fixed the problem. I didnt initially try doing the same with the index.php file as i have the SPPC contribution installed which has some code right in the middle of the manufacturers section. After trying, and failing, to fix it i decided to upload the index.php file to see what would happen and it not only fixed the problem, but the SPPC continues to work with an unmodified index.php file? perhaps the SPPC mod is more complicated than it needs to be? or perhaps i am just lucky? either way it works. If you are going to try this then dont forget to backup your existing files first!! Link to comment Share on other sites More sharing options...
wenonae Posted September 2, 2008 Share Posted September 2, 2008 I had this problem that affected my advanced search results and manufacturers sections I downloaded the latest version of oscommerce and uploaded the advanced_search_results.php which fixed the problem. I didnt initially try doing the same with the index.php file as i have the SPPC contribution installed which has some code right in the middle of the manufacturers section. After trying, and failing, to fix it i decided to upload the index.php file to see what would happen and it not only fixed the problem, but the SPPC continues to work with an unmodified index.php file? perhaps the SPPC mod is more complicated than it needs to be? or perhaps i am just lucky? either way it works. If you are going to try this then dont forget to backup your existing files first!! Thanks, this idea wound up working like a gem for me. ;) Link to comment Share on other sites More sharing options...
christodoulos Posted September 18, 2008 Share Posted September 18, 2008 That's a known problem for those using mysql version 5. Are you sure of the version? Maybe your host updated since last you checked. In any event, take a look at this thread. It might help. Jack hi, i'm new here and found this to be very help forum, thankyou. this fix worked half way for me, (fixed the subcategory error message) and yes i'm still search and try but i am still stuck on this. when i search for product by manufacturer this error happens.......... 1054 - Unknown column 'p2c.manufacturers_id' in 'where clause' select distinct c.categories_id as id, cd.categories_name as name from products p, products_to_categories p2c, categories c, categories_description cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '1' and p2c.manufacturers_id = '4' order by cd.categories_name [TEP STOP] thanks for any info. Link to comment Share on other sites More sharing options...
Guest Posted September 20, 2008 Share Posted September 20, 2008 We've had the same problem with an OsCommerce but we managed to remedy it by replacing the old code with new one in the index.php & advanced_search_resilt.php files. But we immediatley encoutered a new problem, namely, the CSS is not working. The product pics can be seen, but the template, the design is not working. The fonts, the header the menu's are all fubar. Any ideas on how to fix this problem? Link to comment Share on other sites More sharing options...
♥geoffreywalton Posted September 20, 2008 Share Posted September 20, 2008 Any ideas on how to fix this problem? Restore your original files and apply the mysql compatibility patches that you can find by following the link in my signature block. Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile Virus Threat Scanner My Contributions Basic install answers. Click here for Contributions / Add Ons. UK your site. Site Move. Basic design info. For links mentioned in old answers that are no longer here follow this link Useful Threads. If this post was useful, click the Like This button over there ======>>>>>. Link to comment Share on other sites More sharing options...
FixItPete Posted September 25, 2008 Share Posted September 25, 2008 its a php5 error Try this change.... p.products_id to p2c.products_id and see if that works regards Mark Can somebody tell me specifically where this change should be made? I find the fun in everything. Link to comment Share on other sites More sharing options...
rickard_l Posted October 19, 2008 Share Posted October 19, 2008 This can be resolved by editing default.php go to line 174-175 and replace: // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . $languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'"; with the following: // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'"; worked for me... Hi guys/girls/ This worked for me (the above) ... sort of... Ok the 1054 error message disapered WHOHOO.... HOWEVER!, Now it can't show the products. It's visable in the product category that there are products BUT when i press a category it shows this message... There are no products to list in this category. any suggestions? thx Link to comment Share on other sites More sharing options...
rickard_l Posted October 19, 2008 Share Posted October 19, 2008 Thats seems simple to follow, however as using a template and getting these errors, I have found that I dont have the code that they are stating to replace in the index.php Any ideas? I'we found the code in the products.php however when I changed the code it fixed the 1054 error mes. but now it dont show any products. It just says There are not products to list or something BUT i see in the categories that there infact is products. it just can show them... weird hue? :S Link to comment Share on other sites More sharing options...
rickard_l Posted October 19, 2008 Share Posted October 19, 2008 Hi guys/girls/ This worked for me (the above) ... sort of... Ok the 1054 error message disapered WHOHOO.... HOWEVER!, Now it can't show the products. It's visable in the product category that there are products BUT when i press a category it shows this message... There are no products to list in this category. any suggestions? thx Noo :S It has happened to three different installations of osc all have got different templates, and when I fix them with the fix above that I quoted earlier it's getting... "There are no products to list in this category" - seriously this is bad :blink: PLEASE! help me, what shoudl I do? _________________ THIS IS WHAT HAPPENED ______________________ - The categories display that there are products underneeth but when pressing a category you get the message. "There are no..." - It's possible to wiev the new products = the product must be working somehow... so might it be between the the categories and fetching the information from the products list? :huh: I'we fixed the 1054 error message before and then it gets the message "There are no..." Anyone :'( Link to comment Share on other sites More sharing options...
Mantistech Posted November 24, 2008 Share Posted November 24, 2008 Hi, I'm having a problem and have please can anyone help me, I have been searching for ages to fix this but without any success. Below is my error, please can someone tell me what it means. I only get this when I click a category that has sub categories. My error is below: 1054 - Unknown column 'p.manufacturers_id' in 'on clause' select distinct p.products_id, p.products_image, p.products_model, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, mnf.manufacturers_name, rvw.reviews_rating from (products p) left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join manufacturers mnf on p.manufacturers_id = mnf.manufacturers_id left join reviews rvw on p.products_id = rvw.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '42' and p.products_status = '1' order by p.products_date_added desc limit 2 [TEP STOP] I hope someone can help me! Link to comment Share on other sites More sharing options...
Hotclutch Posted November 24, 2008 Share Posted November 24, 2008 Hi, I'm having a problem and have please can anyone help me, I have been searching for ages to fix this but without any success. Below is my error, please can someone tell me what it means. I only get this when I click a category that has sub categories. My error is below: 1054 - Unknown column 'p.manufacturers_id' in 'on clause' select distinct p.products_id, p.products_image, p.products_model, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, mnf.manufacturers_name, rvw.reviews_rating from (products p) left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join manufacturers mnf on p.manufacturers_id = mnf.manufacturers_id left join reviews rvw on p.products_id = rvw.products_id where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '42' and p.products_status = '1' order by p.products_date_added desc limit 2 [TEP STOP] I hope someone can help me! See this THREAD for example Link to comment Share on other sites More sharing options...
pwerch Posted November 26, 2008 Share Posted November 26, 2008 I have a problem now that I fixed the 1054 too. When there are subcatagories to list, I get n n n where the names of the catagories used to be. I am happy as can be my catagories are back, but can't locate the particular code to stop the n's! Anyone know? Link to comment Share on other sites More sharing options...
Proprietor Posted December 10, 2008 Share Posted December 10, 2008 Hi Guys, I had this problem too and it was driving me crazy. The people at the host were no help either even though the problem was happening when they installed it. I tried some of the other suggestions in this post and they the didn't work or even caused other problems. The pc to p2c fix didn't work because I already had a version that included the p2c change. Finally I found Ozstar's method, which is referenced in some other post on this forum. Here's the link to the fix: http://www.oscommerce.com/community/contributions,4654 It's amazing! By replacing two blocks of code just as he says in his contribution it fixed the problem entirely. Thanks Ozstar! Link to comment Share on other sites More sharing options...
ema Posted December 16, 2008 Share Posted December 16, 2008 Hello, I have a problem, i fix the error 1054 but now when i click to open the produt in the categorie it apear all produtcs it the same descripton, the images are diferent the the descripton it´s the same. A tried to fix this, i tried to search but not anwers. Please help me ! Best regards, Ema Tank you... Link to comment Share on other sites More sharing options...
vjp Posted March 2, 2009 Share Posted March 2, 2009 thanks for that info. Link to comment Share on other sites More sharing options...
vjp Posted March 2, 2009 Share Posted March 2, 2009 ...Not being stellar at programming, this was a hard fix for me. I finally found the bug fix (in laymen's terms) :D http://www.oscommerce.com/community/bugs,3171 ... Hi tthorpe, thanks for that info. It fixed the problem. Btw anyone had the error 1054 - Unknown column 'p.products_id' in 'on clause' ... error occuring from index.php in the first of the 2 lines that starts with the comment:"// We show them all" , even though the field "products_id" exists in the table "products" ??. Just changing the order of the table references fixed it for me, I have no idea why it is so though. I had to change this... $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'"; to this... $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'"; Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.