Jump to content

Archived

This topic is now archived and is closed to further replies.

tthorpe

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

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites

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'

Share this post


Link to post
Share on other sites

×