After server was moved, inner join is suddenly returning empty results

[Benjip]

I'm stumped as to why this might be happening. A website I had completed for a client over a year ago, was working fine until a couple of days ago when the web host went and moved the site onto a new server without warning any of their customers (myself included). This caused errors with the database connections etc., but that was all easily fixed.

Now something strange is happening though - one of my bigger INNER JOIN queries (which was working fine) is suddenly returning empty results. I had a quick search and found that this was happening for some people on a certain version of MySQL (the same one the new server was running, 5.1.49), so I had the web host update the MySQL but it hasn't changed anything.

Is there something obvious that I'm missing here - perhaps a syntax error or some old method I'm using which is no longer supported in MySQL 5.1+?

$subcat_products_lookup = mysql_query('SELECT DISTINCT pclook.product_id, pclook.category_id, prod.name AS prod_name, prod.id, prod.enabled, prod.sku, prod.brand, prod.range, prod.wels_rating, prod.image_filename_1, range.id AS range_id, range.name AS range_name, brand.id AS brand_id, brand.name AS brand_name FROM product_category_lookup pclook INNER JOIN products prod on pclook.product_id = prod.id INNER JOIN ranges range on prod.range = range.id INNER JOIN brands brand on prod.brand = brand.id WHERE (pclook.category_id = "'.$subcat_id.'" AND prod.enabled = "yes" AND prod.brand != "6") ORDER BY prod.rank asc, prod.name asc');

have you tried running it with a real $subcat_id value in phpmyadmin?

[Benjip]

have you tried running it with a real $subcat_id value in phpmyadmin?

Hi ardav, thanks for the reply!

Am just running it at the moment in phpmyadmin and finding some interesting stuff - I don't think it likes the word 'range' as this must be an actual MySQL word/term?

This is what I'm getting in phpmyadmin:

SELECT DISTINCT pclook.product_id, pclook.category_id, prod.name AS prod_name, prod.id, prod.enabled, prod.sku, prod.brand, prod.range, prod.wels_rating, prod.image_filename_1, range.id AS range_id, range.name AS range_name, brand.id AS brand_id, brand.name AS brand_name
FROM product_category_lookup pclook
INNER JOIN products prod ON pclook.product_id = prod.id
INNER JOIN ranges
RANGE ON prod.range = range.id
INNER JOIN brands brand ON prod.brand = brand.id
WHERE (
pclook.category_id =  "14"
AND prod.enabled =  "yes"
AND prod.brand !=  "6"
)
ORDER BY prod.rank ASC , prod.name ASC 
LIMIT 0 , 30

Notice how 'range' is now 'RANGE ON' ?

So perhaps I should just change the word 'range' to something different?

[Benjip]

Ok, all sorted now - thanks ardav for the phpmyadmin suggestion.

Turns out 'range' must be a new word/operation in more recent versions of MySQL, so I just changed it to 'rangers' instead.