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

♥bruyndoncx · May 7, 2006

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);

MarthaD · February 13, 2007

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

Cheers!

♥bruyndoncx · February 13, 2007

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

MarthaD · February 13, 2007

http://www.oscommerce.com/community/contri...l+manufacturers\

http://www.oscommerce.com/community/contri...ns/search,33440

If it's not your then I appologise for postin it in your thread and you should have it pulled - if it is yours, please help by correcting the code within ;)

MarthaD · February 13, 2007

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 :)

♥bruyndoncx · February 13, 2007

********************************
* 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.

scliburn · February 15, 2007

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.

♥bruyndoncx · February 15, 2007

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 !

davsoft · March 13, 2007

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

enigma1 · March 14, 2007

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

Deskdirect · June 27, 2007

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 ;)

ChrisW123 · June 28, 2007

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?

enigma1 · June 28, 2007

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).

ChrisW123 · June 28, 2007

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.

sherwoodauctionsuk · December 18, 2007

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">

<head>

</head>

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

<tr>

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

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

</table></td>

<?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);

?>

<tr>

<tr>

</tr>

</table></td>

</tr>

<tr>

</tr>

<tr>

<tr>

<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>

</tr>

<tr>

</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;

}

?>

<tr>

<tr>

<?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!

tangwaichee · December 22, 2007

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

sutikah1 · January 15, 2008

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! :-"

mr_absinthe · February 11, 2008

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:

Mad4rc · April 12, 2008

But where is the file as quoted above pdf_catalogue?

Maybe I should give up :'(

GemRock · April 12, 2008

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

Mad4rc · April 12, 2008

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

Mad4rc · April 13, 2008

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

navyhost · April 21, 2008

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.

JasonTurbo · June 19, 2009

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.

hush_hush · June 22, 2009

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:

Archived

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

Recommended Posts

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites