bhavatmaj Posted April 22, 2009 Share Posted April 22, 2009 thanks bhavatmaj Link to comment Share on other sites More sharing options...
MrPhil Posted April 23, 2009 Share Posted April 23, 2009 The proper fix is to change $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 $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'] . "'"; I don't know if changing the order of tables p, pd, and m is safe, or if that will sometimes produce different results. Anyway, as explained many times before, the problem is that MySQL 5 is incompatible with code written for MySQL 4. Version 5 changed the behavior of JOINs to match the SQL standard (which previous versions of MySQL did not correctly implement). Before, the comma (,) operator and JOIN were the same precedence, so p, pd, m LEFT JOIN behaved as (p, pd, m) LEFT JOIN. Now, the JOIN is of higher precedence, so only "m" participates in the operation, and you get an error that a field in "p" isn't found. You have to explicitly give the parentheses around the list, to create the same behavior as before. Link to comment Share on other sites More sharing options...
kunal247 Posted May 6, 2009 Share Posted May 6, 2009 managed to fix the index.php file but having issues on catalog/advanced_search_result.php Code on my advanced_search_result.php is: $from_str = "from (" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on p.products_id=p2pef.products_id) left join " . TABLE_MANUFACTURERS . " m on m.manufacturers_id=p.manufacturers_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c"; ----Tried adding this fix but still getting errors--------------------------------------------------------------- In catalog/advanced_search_result.php Find this $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c"; ---------------------------------------- and replace with this $from_str = "from ((" . TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c"; ------------------------------------------------------------------------------------------------------------ Any suggestion on this bit? Link to comment Share on other sites More sharing options...
MrPhil Posted May 6, 2009 Share Posted May 6, 2009 Hmm. I don't seem to be allowed to modify my previous post. I wanted to add: Here's my list of changes for MySQL 5: http://www.oscommerce.com/forums/index.php?sho...=335136&hl= They're minor changes, so you may want to go ahead and try them. Link to comment Share on other sites More sharing options...
sakkiotto Posted May 21, 2009 Share Posted May 21, 2009 Hi, I have the same problem only if some customers login. I don't know the difference, but some account work fine and some don't work with error like: 1054 - Unknown column 'p.products_id' in 'on clause' select pd.products_id, pd.products_name, pd.products_description, p.products_image, 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 p, products_description pd left join specials s on p.products_id = s.products_id where pd.products_id = '3468' and p.products_id = pd.products_id and pd.language_id = '4' order by products_name [TEP STOP] I fixed index.php page with () before "left join" but still don't work. Any idea for help me? Thanks Link to comment Share on other sites More sharing options...
MrPhil Posted May 21, 2009 Share Posted May 21, 2009 Fix discussed in the other thread: http://www.oscommerce.com/forums/index.php?sho...=335136&hl= Please don't double post. Link to comment Share on other sites More sharing options...
postonoh Posted June 15, 2009 Share Posted June 15, 2009 this is my index.php file. <?php session_name (); ini_set ('session.use_cookies', 0); /* $Id: index.php,v 1.1 2005/08/11 17:37:59 hpdl Exp $ E-Commerce Solutions Released under the GNU General Public License */ require('includes/application_top.php'); // the following cPath references come from application_top.php $category_depth = 'top'; if (isset($cPath) && tep_not_null($cPath)) { $categories_products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'"); $cateqories_products = tep_db_fetch_array($categories_products_query); if ($cateqories_products['total'] > 0) { $category_depth = 'products'; // display products } else { $category_parent_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$current_category_id . "'"); $category_parent = tep_db_fetch_array($category_parent_query); if ($category_parent['total'] > 0) { $category_depth = 'nested'; // navigate through the categories } else { $category_depth = 'products'; // category has no products, but display the 'no products' message } } } require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_DEFAULT); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title>Taylor Made Scentsations</title> <base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>"> <!--<link rel="stylesheet" type="text/css" href="stylesheet.css">--> <link rel="stylesheet" type="text/css" href="style.css"> <style type="text/css"> .style1 { background-color: #F8F4F0; } .style2 { border-style: solid; border-color: #F8F4F0; } </style> </head> <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" style="background-color: #F8F4F0"> <!-- header //--> <?php require(DIR_WS_INCLUDES . 'header.php'); ?> <!-- header_eof //--> <!-- body //--> <tr> <td width="302" height="439" align="left" valign="top"><img src="images/main.jpg" width="302" height="439" alt=""></td> <td width="410" height="439" bgcolor="#F5F5F5"> <table border="0" width="410" cellpadding="0" cellspacing="0" height="439"> <tr> <td width="409" height="100%"> <table border="0" width="100%" cellpadding="0" cellspacing="0" height="100%"> <tr> <td width="100%" height="196" valign="top" > <table border="0" width="100%" cellpadding="0" cellspacing="0" > <tr> <td width="289" height="196" align="center" valign="top" > <table border="0" width="232" cellpadding="0" cellspacing="0" > <tr> <td height="38" valign="top" style="padding-top:20px;"><img src="images/welcome.jpg" width="232" height="24" alt="" /></td> </tr> <tr> <td height="1" background="images/hpoint.gif"><img src="images/hpoint.gif" width="3" height="1"></td> </tr> <tr> <td width="100%" align="left" style="padding-top:8px; padding-bottom:11px;"> <img src="images/str_head.gif" width="9" height="5" hspace="2" vspace="1"/><span class="crimson"></span> </td> </tr> <tr> <td height="1" background="images/hpoint.gif"><img src="images/hpoint.gif" width="3" height="1"></td> </tr> <tr> <td height="30" style="padding-top:6px; " align="left"><img src="images/point_crimson.gif" width="4" height="4" vspace="1" alt=""/> <a href="" class="more">more</a> <img src="images/str_more.gif" width="6" height="5" alt="" vspace="1"/></td> </tr> </table> </td> <td width="92" height="196" style="padding-right:28px; "><img src="images/welcome_girl.jpg" width="92" height="196" alt="" /></td> </tr> <tr> <td colspan="2" height="1" background="images/hpoint.gif" align="left"><img src="images/hpoint.gif" width="3" height="1" alt=""></td> </tr> <tr> <td colspan="2" bgcolor="#F5F5F5" height="2"><img src="images/spacer.gif" width="1" height="1" alt=""></td> </tr> <tr> <td colspan="2" height="1" background="images/hpoint.gif" align="left"><img src="images/hpoint.gif" width="3" height="1" alt=""></td> </tr> <tr> <td colspan="2"><?php include(DIR_WS_MODULES . 'special_products.php'); ?></td> </tr> </table> </td> </tr> </table> </td> <td width="1" height="100%" background="images/vpoint.gif" valign="top"><img src="images/vpoint.gif" width="1" height="3"></td> </tr> </table> </td> </tr> <tr> <td width="100%" height="2" colspan="2" bgcolor="#FFFFFF"><img src="images/spacer.gif" width="1" height="1" alt=""></td> </tr> <tr> <td width="302" height="168" valign="top"><?php include(DIR_WS_MODULES . 'new_products.php'); ?> </td> <td width="410" height="168" bgcolor="#F5F5F5" valign="middle" align="left"> <table width="100%" height="162" border="0" cellpadding="0" cellspacing="6" bgcolor="#F5F5F5"> <tr> <td rowspan="3" height="61" class="style2" ><img src="images/girl_links.jpg" width="127" height="154" alt="" /></td> <td class="style1"><b>Inim vulputate refoveo</b>, pagus olim, ntosus feugait quidem, decet in. Eros valde vtosus refoveo minim vel te nulla importunus drerit in ille:</td> </tr> <tr> <td height="1" background="images/hpoint.gif" align="left"><img src="images/hpoint.gif" width="3" height="1" alt=""></td> </tr> <tr> <td height="100" class="style2"> <ul style="list-style-image:url(images/sign_link.jpg); margin-left:25px; "> <li><a href="" class="crimson">Lorem ipsum dolor sit amet</a></li> <li><a href="" class="crimson">Consectetuer adipiscing elit. Nunc suscipit.</a></li> <li><a href="" class="crimson">Suspendisse enim arcu, convallis non</a></li> <li><a href="" class="crimson">Cursus sed, dignissim et, est.</a></li> <li><a href="" class="crimson">Aenean semper aliquet libero. </a></li></ul></td> </tr> </table> </td> </tr> <tr> <td width="100%" height="3" colspan="2" bgcolor="#FFFFFF"><img src="images/spacer.gif" width="1" height="1" alt=""></td> </tr> <!-- body_eof //--> <!-- footer //--> <?php require(DIR_WS_INCLUDES . 'footer.php'); ?> <!-- footer_eof //--> </body> </html> <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?> need happy. Link to comment Share on other sites More sharing options...
Jan Zonjee Posted June 15, 2009 Share Posted June 15, 2009 this is my index.php file. <?php need happy. Personally I have absolutely no clue what you need but of course I'm not a native English speaker. I assume this is a template you bought somewhere? And from the looks of it you are using a text editor that added a BOM-marker at the beginning of the file as described here for example. Link to comment Share on other sites More sharing options...
MrPhil Posted June 16, 2009 Share Posted June 16, 2009 I agree with Jan that you need to use a real editor (not Microsoft crap) to remove those three characters () from before <?php. They would cause errors about "not being able to send headers". Notepad++ and ViM come highly recommended, although even Notepad (comes with Windows) will do the job. Never use a word processor such as Word, and try to avoid using HTML page editors such as Dreamweaver (sometimes they get confused by the PHP code). If whoever created the template sent it out with those characters (the Byte Order Mark) in it, I would suggest that you find another template source right now. That guy has his head stuck up his taillight socket! Link to comment Share on other sites More sharing options...
pupusscantik Posted September 21, 2009 Share Posted September 21, 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 the 5th response down was very clear cut, and worked like a charm. Of course, then I had an error come up with the search function, but it was also clearly explained further down on the same page. Here are the fixes for those that might need them in the future: First, open "index.php" in an html editor and do this: Then open "advanced_search_results.php" in an html editor and do this: Problem Solved!!! :thumbsup: thanks a lot! this one works for me. thanks for sharing it :) Link to comment Share on other sites More sharing options...
GTLM Posted September 22, 2009 Share Posted September 22, 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 the 5th response down was very clear cut, and worked like a charm. Of course, then I had an error come up with the search function, but it was also clearly explained further down on the same page. Here are the fixes for those that might need them in the future: First, open "index.php" in an html editor and do this: Then open "advanced_search_results.php" in an html editor and do this: Problem Solved!!! Thank you, Problem Solved!! Link to comment Share on other sites More sharing options...
lei_tx Posted October 17, 2009 Share Posted October 17, 2009 All of a sudden, this error message pops up when customers click on anything under "Categories": 1054 - Unknown column 'p.products_id' in 'on clause' select count(p.products_id) as total 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 = '23' [TEP STOP] I am running on MYSQL version 4.0.27 and OSCommerce 2.2-MS2 I have searched and searched ... all the information I can find is way too technical for me. I am not an experienced HTML editor, nor do I have SQL or database experience. Is there anyone out there that can take me through the fix on this in LAYMEN'S terms? I'm getting so frustrated with all of this, I spent 6 months just personalizing my site (you know, putting my logo on the front page, etc.) and finally got it working like a dream. Now, over a year later, this problem pops up. I can't update MYSQL through my server (at least not that I know of), and I tried installing the newer version of OSCommerce and putting my backed up database in it ... that was a flop! :'( Any help at all would be much appreciated. I've seen the line-by-line fix for MYSQL 5, but not for earlier versions. Thanks a bunch for any help you can give. :huh: See the changes below and you'll fix it: select count(p.products_id) as total 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 p2c.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 = '23' Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.