Jump to content

Archived

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

bruyndoncx

1054 - Unknown column 'xxx' in 'on clause'

Recommended Posts

Background:

with MySQL 5.0 the processing of sql join queries was changed to follow the standards more strictly. As a result, queries that ran fine before MySQL 5.0.12 now give this 1054 - Unknown column 'xxx' in 'on clause'.

 

http://dev.mysql.com/doc/refman/5.0/en/join.html

Read the section which begins, "Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence"

 

Solution:

Standard oscommerce files affected have been updated in the nov 2005 update package, if you haven't installed this yet, you should do that first.

 

But, contributions that you have installed might be suffering from this new strictness as well.

Whenever you get this error, you need to dive into the code and make these changes.

 

The incompatibility with mysql 5.0.x is observed when a generated SQL has joins with both JOIN syntax and comma seperated table names in FROM clause.

 

i.e:

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

This is no more a valid sql for mysql 5.0.x according to the mysql 5.0 reference of join syntax: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

The explanation in mysql reference is as follows:

 

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

So then why is this an issue in YOUR all manufacturers contribution? Perhaps you should take your own advise? Maybe post revised code?

 

Cheers!

Share this post


Link to post
Share on other sites

I don't have an 'all manufacturers' contribution, which contribution are YOU exactly referring too ?

I'm not aware of such an issue, but please let me know where I'm at fault and I will happily upload the revised code, all under the same code warranty ! :P


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites
I don't have an 'all manufacturers' contribution, which contribution are YOU exactly referring too ?

I'm not aware of such an issue, but please let me know where I'm at fault and I will happily upload the revised code, all under the same code warranty ! :P

 

********************************

* All Manufacturers Categories *

********************************

********* Jan 2007 *********

 

**************************************************************************

v1.0 Released under the GNU General Public License

by Carine Bruyndoncx (info@calimeross.com)

 

And if your so cavalier about releasing "code", why even bother in the first place? I could see if it was a simple propblem of not knowing anything about code, but you seem to prefess some knowledge on the subject so forgive me for poiinting out your glaring mistake in the very post in which you profess to have a solution...

 

Thanx for your help and a wonderful contribution :)

Share this post


Link to post
Share on other sites
********************************

* All Manufacturers Categories *

********************************

********* Jan 2007 *********

The contribution (mistakenly) assumed that you have enable/disable categories contribution also installed.

Thus, the error you got looked similar on the surface to the one mentioned here, but is actually a totally different as it is just an oversight from me when I released the code.

 

And if your so cavalier about releasing "code", why even bother in the first place? I could see if it was a simple propblem of not knowing anything about code, but you seem to prefess some knowledge on the subject so forgive me for poiinting out your glaring mistake in the very post in which you profess to have a solution...
Would you like to take back your words ?
Thanx for your help and a wonderful contribution :)

You're welcome.

 

New version is being uploaded, and support thread created. Seems one is needed afterall.


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Martha Dinkwaller - very rude tone. eeeek.

 

Thanks bruyndoncx for your post regarding this. I have been migrating an installation from a 4.0.x mysql to 5.x, and this thread saved me some valuable time.

Share this post


Link to post
Share on other sites

Thanks Scott,

 

It is nice to know that some of my posting have saved others some time !

It's even more flattering to see that this post has made you go through the troubles of creating an account just to have your say in this thread :D

I guess you can't do right for all people all the time :)

 

Cheers !


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

I'm having this same problem, except I can't even find the query in question. The error is this:

 

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

 

select count(p.products_id) as total from products_description pd, products p left join manufacturers2 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 = '736'

 

You can see it in action here: http://www.groceriestoyourdoor.com.au/stor...x.php?cPath=736

 

I've been searching index.php and numerous others, but can't find any query that looks remotely like that one. Can anyone point me in the right direction? Thanks,

 

David

Share this post


Link to post
Share on other sites
yes it is in the index.php there is different notation that's the reason you won't see it if you search for the error string. There are many queries that fail with mysql5x and the old osc. So get the latest archive and implement the changes documented in it.

http://www.oscommerce.com/solutions/downloads

 

I made all the changes but I still get the 1054 - Unknown column 'p.products_id' in 'on clause' error when I click on one of my sub catagories. Can something else be causing this error?

 

Deskdirect ;)

Share this post


Link to post
Share on other sites
yes it is in the index.php there is different notation that's the reason you won't see it if you search for the error string. There are many queries that fail with mysql5x and the old osc. So get the latest archive and implement the changes documented in it.

http://www.oscommerce.com/solutions/downloads

 

Enigma,

 

In the osCommerce 2.2 Milestone 2 Update 060817 update, there's a file called "update-20060817.txt" which appears to be a Problem/Solution type document to fix these problems. But when I search for "1054" or for "Unknown column" there's nothing. Is this particular problem documented in this .txt file, or somewhere else?

Share this post


Link to post
Share on other sites
Enigma,

 

In the osCommerce 2.2 Milestone 2 Update 060817 update, there's a file called "update-20060817.txt" which appears to be a Problem/Solution type document to fix these problems. But when I search for "1054" or for "Unknown column" there's nothing. Is this particular problem documented in this .txt file, or somewhere else?

the documentation includes a section with a mysql5.x header as far I recall. Now the changes in that section target the mysql issues. If you have contributions which also have similar problems obviosuly these mods wont address them. And in these cases you will have to check the contribution support thread (or there is an update for each specific module).

Share this post


Link to post
Share on other sites

OK thanks... I think I'll have to go through error by error using the guide at the top of this thread, and fix them one by one. I'll keep a list as I do it and then post it later when I'm done.

Share this post


Link to post
Share on other sites

Hi,

 

I have 1054 problem :( I fixed the problem once before and now its come back to haunt me! I've tried to follow the updates etc but still struggling....here's the error and my index.php code:-

 

ERROR:

 

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 = '28' and p.products_status = '1' order by p.products_date_added desc limit 9

 

[TEP STOP]

 

INDEX CODE:

 

<?php

 

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 HTML 4.01 Transitional//EN">

<html <?php echo HTML_PARAMS; ?>>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">

<title><?php echo TITLE; ?></title>

<base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>">

<link rel="stylesheet" type="text/css" href="stylesheet.css">

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0">

<!-- header //-->

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<!-- header_eof //-->

 

<!-- body //-->

<table border="0" width="100%" cellspacing="3" cellpadding="3">

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">

<!-- left_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

<!-- left_navigation_eof //-->

</table></td>

<!-- body_text //-->

<?php

if ($category_depth == 'nested') {

$category_query = tep_db_query("select cd.categories_name, c.categories_image from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . (int)$current_category_id . "' and cd.categories_id = '" . (int)$current_category_id . "' and cd.language_id = '" . (int)$languages_id . "'");

$category = tep_db_fetch_array($category_query);

?>

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . $category['categories_image'], $category['categories_name'], HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="2">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="2">

<tr>

<?php

if (isset($cPath) && strpos('_', $cPath)) {

// check to see if there are deeper categories within the current category

$category_links = array_reverse($cPath_array);

for($i=0, $n=sizeof($category_links); $i<$n; $i++) {

$categories_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");

$categories = tep_db_fetch_array($categories_query);

if ($categories['total'] < 1) {

// do nothing, go through the loop

} else {

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");

break; // we've found the deepest category the customer is in

}

}

} else {

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");

}

 

$number_of_categories = tep_db_num_rows($categories_query);

 

$rows = 0;

while ($categories = tep_db_fetch_array($categories_query)) {

$rows++;

$cPath_new = tep_get_path($categories['categories_id']);

$width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%';

echo ' <td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . '<br>' . $categories['categories_name'] . '</a></td>' . "\n";

if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) {

echo ' </tr>' . "\n";

echo ' <tr>' . "\n";

}

}

 

// needed for the new products module shown below

$new_products_category_id = $current_category_id;

?>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td><?php include(DIR_WS_MODULES . FILENAME_NEW_PRODUCTS); ?></td>

</tr>

</table></td>

</tr>

</table></td>

<?php

} elseif ($category_depth == 'products' || isset($HTTP_GET_VARS['manufacturers_id'])) {

// create column list

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,

'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,

'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,

'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,

'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,

'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,

'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,

'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);

 

asort($define_list);

 

$column_list = array();

reset($define_list);

while (list($key, $value) = each($define_list)) {

if ($value > 0) $column_list[] = $key;

}

 

$select_column_list = '';

 

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {

switch ($column_list[$i]) {

case 'PRODUCT_LIST_MODEL':

$select_column_list .= 'p.products_model, ';

break;

case 'PRODUCT_LIST_NAME':

$select_column_list .= 'pd.products_name, ';

break;

case 'PRODUCT_LIST_MANUFACTURER':

$select_column_list .= 'm.manufacturers_name, ';

break;

case 'PRODUCT_LIST_QUANTITY':

$select_column_list .= 'p.products_quantity, ';

break;

case 'PRODUCT_LIST_IMAGE':

$select_column_list .= 'p.products_image, ';

break;

case 'PRODUCT_LIST_WEIGHT':

$select_column_list .= 'p.products_weight, ';

break;

}

}

 

// show the products of a specified manufacturer

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {

// We are asked to show only a specific category

$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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

} else {

// 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 = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

}

} else {

// show the products in a given categorie

if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {

// We are asked to show only specific catgeory

$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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

} else {

// 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_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_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 = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

}

}

 

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('^[1-8][ad]$', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {

if ($column_list[$i] == 'PRODUCT_LIST_NAME') {

$HTTP_GET_VARS['sort'] = $i+1 . 'a';

$listing_sql .= " order by pd.products_name";

break;

}

}

} else {

$sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);

$sort_order = substr($HTTP_GET_VARS['sort'], 1);

 

switch ($column_list[$sort_col-1]) {

case 'PRODUCT_LIST_MODEL':

$listing_sql .= " order by p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

case 'PRODUCT_LIST_NAME':

$listing_sql .= " order by pd.products_name " . ($sort_order == 'd' ? 'desc' : '');

break;

case 'PRODUCT_LIST_MANUFACTURER':

$listing_sql .= " order by m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

case 'PRODUCT_LIST_QUANTITY':

$listing_sql .= " order by p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

case 'PRODUCT_LIST_IMAGE':

$listing_sql .= " order by pd.products_name";

break;

case 'PRODUCT_LIST_WEIGHT':

$listing_sql .= " order by p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

case 'PRODUCT_LIST_PRICE':

$listing_sql .= " order by final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

}

}

?>

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

<?php

// optional Product List Filter

if (PRODUCT_LIST_FILTER > 0) {

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

$filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_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 = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name";

} else {

$filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by m.manufacturers_name";

}

$filterlist_query = tep_db_query($filterlist_sql);

if (tep_db_num_rows($filterlist_query) > 1) {

echo ' <td align="center" class="main">' . tep_draw_form('filter', FILENAME_DEFAULT, 'get') . TEXT_SHOW . ' ';

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

echo tep_draw_hidden_field('manufacturers_id', $HTTP_GET_VARS['manufacturers_id']);

$options = array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES));

} else {

echo tep_draw_hidden_field('cPath', $cPath);

$options = array(array('id' => '', 'text' => TEXT_ALL_MANUFACTURERS));

}

echo tep_draw_hidden_field('sort', $HTTP_GET_VARS['sort']);

while ($filterlist = tep_db_fetch_array($filterlist_query)) {

$options[] = array('id' => $filterlist['id'], 'text' => $filterlist['name']);

}

echo tep_draw_pull_down_menu('filter_id', $options, (isset($HTTP_GET_VARS['filter_id']) ? $HTTP_GET_VARS['filter_id'] : ''), 'onchange="this.form.submit()"');

echo tep_hide_session_id() . '</form></td>' . "\n";

}

}

 

// Get the right image for the top-right

$image = DIR_WS_IMAGES . 'table_background_list.gif';

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

$image = tep_db_query("select manufacturers_image from " . TABLE_MANUFACTURERS . " where manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'");

$image = tep_db_fetch_array($image);

$image = $image['manufacturers_image'];

} elseif ($current_category_id) {

$image = tep_db_query("select categories_image from " . TABLE_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");

$image = tep_db_fetch_array($image);

$image = $image['categories_image'];

}

?>

 

 

Any help would be highly appreciated!

Share this post


Link to post
Share on other sites

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

 

select p.products_id, pd.products_name, pd.products_description, p.products_image, p.products_model, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where 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 p2c.categories_id='80' order by pd.products_name, p.products_date_added DESC

 

[TEP STOP]

 

 

Anyone could give ne a hand check how come im got this error during when im generate the pdf catalog at admin side,,, what should i do???in order to solve

Share this post


Link to post
Share on other sites
Background:

with MySQL 5.0 the processing of sql join queries was changed to follow the standards more strictly. As a result, queries that ran fine before MySQL 5.0.12 now give this 1054 - Unknown column 'xxx' in 'on clause'.

 

http://dev.mysql.com/doc/refman/5.0/en/join.html

Read the section which begins, "Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence"

 

Solution:

Standard oscommerce files affected have been updated in the nov 2005 update package, if you haven't installed this yet, you should do that first.

 

But, contributions that you have installed might be suffering from this new strictness as well.

Whenever you get this error, you need to dive into the code and make these changes.

 

The incompatibility with mysql 5.0.x is observed when a generated SQL has joins with both JOIN syntax and comma seperated table names in FROM clause.

 

i.e:

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

This is no more a valid sql for mysql 5.0.x according to the mysql 5.0 reference of join syntax: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

The explanation in mysql reference is as follows:

 

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

 

I've read the above post a LOT of times but I still don't get it? Any chance of a COMPLETE IDIOTS guide?

 

Many thanks

 

a complete idiot! :-"


I didn't say it was your fault........ I said I was going to blame you!!

Share this post


Link to post
Share on other sites

If you are using MySQL 5 you will need to change line 609 in pdf_catalogue.php from:

 

$requete_prod="select p.products_id, pd.products_name, pd.products_description, p.products_image, p.products_model, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.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 where products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id='".$current_category_id."' order by pd.products_name, p.products_date_added DESC";

 

to:

 

$requete_prod="select p.products_id, pd.products_name, pd.products_description, p.products_image, p.products_model, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from (" . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.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 where products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id='".$current_category_id."' order by pd.products_name, p.products_date_added DESC";

 

The only change is the addition of brackets after the FROM clause:

 

(" . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd)

 

I hope that helps. :thumbsup:


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

if you cant find the file then you dont have the problem, if you dont have the problem then why worry?

pdf_catalogue is an add-on and is NOT part of osCommerce.

Ken


commercial support - unProtected channel, not to be confused with the forum with same name - open to everyone who need some professional help: either PM/email me, or go to my website (URL can be found in my profile).

over 20 years of computer programming experience.

Share this post


Link to post
Share on other sites

But I have got a problem?

 

Site here http://www.rcplans4u.co.uk/shop/index.php?cPath=1_17

 

PROBLEM

 

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 = '17'

 

 

And the site is running with mysql 4

 

So whats the problem now

Share this post


Link to post
Share on other sites

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

 

If you get this message it is probably due to your server upgrading to php5 or MySQL5. You get the message when you try a search on your site. The new OSC from around Sept-Oct 2006 or so, doesn't have this problem.

 

You just need to change one section in index.php and one in advance search_result.php.

 

Very simple Good Luck!

 

Ozstar

 

 

I've gone throught this and still no change and still now working.

 

I have tried this and seems to have done the trick but may not work for others

 

First open you index.php in a html editor

 

Search and replace the following code.

 

p.products_id = s.products_id

 

REPLACE WITH

 

p2c.products_id = s.products_id

 

If anyone use the above and it works for them could you post a message here

 

Many thanks to all the people that have helped in this matter

Share this post


Link to post
Share on other sites

Hi guys,

 

I am running int oa similar problem here. It is after I had a mod made for one of my sites than I decided to clone it onto another site of mine.

 

I get this error when going to Featured in the admin:

 

1054 - Unknown column 's.cat_id' in 'on clause'

select count(*) as total from products p, featured s, products_description pd left join fcat f on f.id = s.cat_id where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id

[TEP STOP]

 

This is the code in that section:

 

$featured_query_raw = "select p.products_id, pd.products_name, s.featured_id, s.featured_date_added, s.featured_last_modified, s.expires_date, s.date_status_change, s.status, f.title as cat from " . TABLE_PRODUCTS . " p, " . TABLE_FEATURED . " s, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_FCAT . " f on f.id = s.cat_id where p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = s.products_id order by pd.products_name";
$featured_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $featured_query_raw, $featured_query_numrows);
$featured_query = tep_db_query($featured_query_raw);
while ($featured = tep_db_fetch_array($featured_query)) {
  if ( ((!$HTTP_GET_VARS['sID']) || ($HTTP_GET_VARS['sID'] == $featured['featured_id'])) && (!$sInfo) ) {

 

Thanks greatly for the assistance.


Sincerely

Mike

Share this post


Link to post
Share on other sites

Thanks for the help guys :) For those of you who have not fixed this yet then please do a bit of research like I have just done. Its one of those problems where one fix doesn't fix everything and therefore you cant expect someone just to paste up some code. I spent all in all 20 minutes of reading/understanding/changing code and now have fixed my problem and know how to fix it again if it appears anywhere else in my store.

Share this post


Link to post
Share on other sites

i have same error in my account.php file :

1054 - Unknown column 's.public_flag' in 'where clause'

 

select count(*) as total from orders o, orders_status s where o.customers_id = '9' and o.orders_status = s.orders_status_id and s.language_id = '4' and s.public_flag = '1

please help me how can i fix it :blink: :blink:

Share this post


Link to post
Share on other sites

×