Query works in phpMyAdmin not mysql_query (not multi statement)

[wendyp]

I'm having hair-pulling-out trouble with a query:

SELECT mwr.item_id, mwr.category_id
FROM (

SELECT mwi.item_id, mwc.category_id
FROM (
(

SELECT i.item_id
FROM (
(
(
tbl_items i
INNER JOIN tbl_items_categories ic ON i.item_id = ic.item_id
)
INNER JOIN tbl_categories c ON c.category_id = ic.category_id
)
LEFT JOIN tbl_manufacturers m ON i.manufacturer_id = m.manufacturer_id
)
WHERE i.is_showing =1
AND i.is_approved =1
AND (
(
i.hide_out_of_stock =1
AND i.stock_level >0
)
OR i.hide_out_of_stock =0
OR i.hide_out_of_stock IS NULL
)
AND (
i.language_code IS NULL
OR i.language_code = ''
OR i.language_code = 'en'
)
AND (
ic.category_id =15
AND i.sites_all =1
AND i.user_types_all =1
AND (
i.item_name LIKE '%2%'
OR i.item_code LIKE '%2%'
OR i.manufacturer_code LIKE '%2%'
OR i.short_description LIKE '%2%'
OR i.full_description LIKE '%2%'
)
)
ORDER BY i.item_order, i.item_name, i.item_id
)mwi
INNER JOIN tbl_items_categories mwc ON mwi.item_id = mwc.item_id
)
WHERE mwc.category_id <>0
)mwr

This returns the expected data when I run it in phpMyAdmin.

When used in a php script with mysql_query, I get:

MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY i.item_order, i.item_name, i.item_id' at line 1

I've searched and found reasons of reserved words, and multi statements, neither of which apply here.

Why do I get a syntax error in the script, but not in phpMyAdmin?

Any ideas would be appreciated.

Thanks,
/Wendy

 

[r937]

very weird

take out the ORDER BY clause entirely, as it is not at all necessary

you could also take out the LEFT JOIN to the manufacturers table, since you aren't using it at all

and i'm pretty sure you can take out the categories table, too, since you wouldn't have a row in tbl_items_categories with a category_id that does not have a match in the categories table

r937.com | rudy.ca

 

[DonQuichote]

Without seeing your code, my guess is that one of the quotes is escaped incorrectly.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[vacunita]

I'd have to agree with Don. It comes down to something in your PHP code likely a quote.


Also are you using any variables in your query from PHP?


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.