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

[Guest]

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.

 

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]

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!