[SOLVED] same qry, same db

[scottybwoy]

Hi all,

 

I have a duplicate of the same db on my test server as my live server and am running exactly the same query on both but the one on my test server fails but the live one works fine.

 

Here's the qry :

 SELECT p.products_id, p.products_image, p.products_tax_class_id, if( s.status, s.specials_new_products_price, p.products_price ) AS products_price
FROM products p, products_description pd, products_to_categories p2c, categories c
LEFT JOIN specials s ON p.products_id = s.products_id
WHERE products_status = '1'
AND p.products_ordered >0
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 '0'
IN (
c.categories_id, c.parent_id
)
ORDER BY p.products_ordered DESC
LIMIT 10 

 

The error I'm getting on my test server is :

 

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

 

But I know that column is there.  My test server is running MySQL 5.0.20-nt and my Live server is running v.4.1.22-max-log

[scottybwoy]

Could this be down to having the wrong MySQL client compared to the server?  I'm using php4.4.8 but it uses a MySQL directory rather than the usual mysql extension, but stil shows the client as v3.xxxx.

 

Any other ideas?

[fenway]

No, it's because the comma operator and JOIN don't have the same precedence in v5.

 

Moral of the story -- NEVER EVER EVER use the comma operator -- EVER.  It's only 3 extra characters to do it properly.

 

In the meanwhile, wrapping the "comma-separated" list of tables in parens will "solve" your problem -- see below.

 

SELECT p.products_id, p.products_image, p.products_tax_class_id, if( s.status, s.specials_new_products_price, p.products_price ) AS products_price
FROM (products p, products_description pd, products_to_categories p2c, categories c)
LEFT JOIN specials s ON p.products_id = s.products_id
WHERE products_status = '1'
AND p.products_ordered >0
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 '0'
IN (
c.categories_id, c.parent_id
)
ORDER BY p.products_ordered DESC
LIMIT 10