27 replies to this topic

[Mad4rc]

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]

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]

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]

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]

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

[KawaiiShop]

Ok - so I am getting a similar issue with the Yahoo Feeder file (yahoo.php) add-on. The error I get is:

: SQL error Unknown column 'products.manufacturers_id' in 'on clause'| sql = SELECT concat( 'http://www.jeanashop.com/kawaiishop/product_info.php?products_id=' ,products.products_id) AS product_url, products_model AS prodModel, products_weight, manufacturers.manufacturers_name AS mfgName, manufacturers.manufacturers_id, products.products_id AS id, products_description.products_name AS name, products_description.products_description AS description, products.products_quantity AS quantity, products.products_status AS prodStatus, FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * 1.0975,2) AS price, CONCAT( 'http://www.jeanashop.com/kawaiishop/images/' ,products.products_image) AS image_url, products_to_categories.categories_id AS prodCatID, categories.parent_id AS catParentID, categories_description.categories_name AS catName FROM categories, categories_description, products, products_description, products_to_categories left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id ) left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) ) WHERE products.products_id=products_description.products_id AND products.products_id=products_to_categories.products_id AND products_to_categories.categories_id=categories.categories_id AND categories.categories_id=categories_description.categories_id ORDER BY products.products_id ASC

From reading this thread, I understand that I need to go thru the .php file and change the "," to "JOIN" instead.....right? But I'm a little hesitant about as to WHERE. I *think* this is the part of teh file that needs to be changed:

$sql = "
SELECT concat( '" . $productURL . "' ,products.products_id) AS product_url,
products_model AS prodModel, products_weight,
manufacturers.manufacturers_name AS mfgName,
manufacturers.manufacturers_id,
products.products_id AS id,
products_description.products_name AS name,
products_description.products_description AS description,
products.products_quantity AS quantity,
products.products_status AS prodStatus,
FORMAT( IFNULL(specials.specials_new_products_price, products.products_price) * " . $taxCalc . ",2) AS price,
CONCAT( '" . $imageURL . "' ,products.products_image) AS image_url,
products_to_categories.categories_id AS prodCatID,
categories.parent_id AS catParentID,
categories_description.categories_name AS catName
FROM categories,
categories_description,
products,
products_description,
products_to_categories

left join manufacturers on ( manufacturers.manufacturers_id = products.manufacturers_id )
left join specials on ( specials.products_id = products.products_id AND ( ( (specials.expires_date > CURRENT_DATE) OR (specials.expires_date = 0) ) AND ( specials.status = 1 ) ) )

WHERE products.products_id=products_description.products_id
AND products.products_id=products_to_categories.products_id
AND products_to_categories.categories_id=categories.categories_id
AND categories.categories_id=categories_description.categories_id
ORDER BY
products.products_id ASC
";

Any help is appreciated.

bruyndoncx, on 07 May 2006, 12:54, said:

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

[bruyndoncx]

a lazy solution might be to add

(

after FROM

and

)

before the JOIN

I think that will get it working but it might be a bit slow.

[onnig]

Mad4rc, on 13 April 2008, 12:50, said:

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

Yes it worked for me thank you!