BYGino Posted October 11, 2007 Share Posted October 11, 2007 Hi all. First time poster here, I've been pulling my hair out with this one for days and I decided I had to give up and ask for help. I have a query that has worked fine for about 3 years until we recently upgraded the server with the latest updates. I can't really see a problem with my Syntax but I'm sure I must be overlooking something stupid. The query pulls together information from multiple tables to generate a printable stock list. Here's the query: $products_query = tep_db_query("SELECT p.products_id, pd.products_name, p.products_price, m.manufacturers_name, c.categories_id, l.location_name, con.condition_name, cd.categories_name, c.parent_id FROM ( " . TABLE_PRODUCTS . " p , " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_LOCATIONS . " l on p.location_id = l.location_id left join " . TABLE_CONDITIONS . " con on p.condition_id = con.condition_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on p2c.categories_id = cd.categories_id where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and products_status = '1' ORDER BY c.parent_id, categories_id, products_name"); And here's the error message: 1064 - 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 'condition con on p.condition_id = con.condition_id left join categories_descript' at line 1 SELECT p.products_id, pd.products_name, p.products_price, m.manufacturers_name, c.categories_id, l.location_name, con.condition_name, cd.categories_name, c.parent_id FROM (( products p , categories c, products_to_categories p2c ) left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id and pd.language_id = '1' left join specials s on p.products_id = s.products_id left join location l on p.location_id = l.location_id left join condition con on p.condition_id = con.condition_id left join categories_description cd on p2c.categories_id = cd.categories_id where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and products_status = '1' ) ORDER BY c.parent_id, categories_id, products_name I really hope someone can point out the obvious to me here, I've search around for ages and can't find an answer. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/ Share on other sites More sharing options...
roopurt18 Posted October 11, 2007 Share Posted October 11, 2007 I had a similar situation not too long ago. Turned out rewriting the query to use INNER JOIN instead of doing the joins in the WHERE clause fixed it. See this thread for reference: http://www.phpfreaks.com/forums/index.php/topic,162075.0.html I can't say that will fix it, but its worth a shot maybe. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-366877 Share on other sites More sharing options...
BYGino Posted October 11, 2007 Author Share Posted October 11, 2007 WOW, thanks for the speedy reply! I've just tried replacing all my LEFT JOINs with INNER JOINSs but unfortunately I still get the same error. Thanks for the info anyway. Anyone else got any ideas? I know it will be something crazy I've missed, does anyone even know where I can get a list of all the changes in MySQL between two revision numbers? Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-366967 Share on other sites More sharing options...
roopurt18 Posted October 11, 2007 Share Posted October 11, 2007 SELECT p.products_id, pd.products_name, p.products_price, m.manufacturers_name, c.categories_id, l.location_name, con.condition_name, cd.categories_name, c.parent_id FROM products p inner join products_to_categories p2c on p.products_id = p2c.products_id inner join categories c on p2c.categories_id = c.categories_id products_to_categories p2c left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id and pd.language_id = '1' left join specials s on p.products_id = s.products_id left join location l on p.location_id = l.location_id left join condition con on p.condition_id = con.condition_id left join categories_description cd on p2c.categories_id = cd.categories_id where c.categories_status=1 and products_status = '1' ORDER BY c.parent_id, categories_id, products_name I didn't mean to change the LEFT JOINs; you'd have to leave them to get the correct data. I meant the parts of your query that join the products, categories, and products_to_categories tables in the WHERE clause. Anyways, try the query above. I removed the joining statements from the WHERE clause and replaced them with inner join above, which is what fixed my problem. Also, you might want to attach the table alias to the field names where they're missing; I didn't add them because I don't know which table they go with, but it appears they belong with the products table. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367131 Share on other sites More sharing options...
BYGino Posted October 11, 2007 Author Share Posted October 11, 2007 Ah... gotcha, sorry! Makes more sense now, was a bit confused why that would work the first time LOL. OK, I'l try your suggestions and let you know how I get on. Thanks again. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367167 Share on other sites More sharing options...
BYGino Posted October 11, 2007 Author Share Posted October 11, 2007 Well, thanks again roopurt for taking your time to try and help a complete dunce, LOL. I studied what you said, and used your code modified to fit. Unfortunately, I'm still getting the same error. My code now reads (I'll make it a bit more legible this time LOL... $products_query = tep_db_query(" SELECT p.products_id, pd.products_name, p.products_price, m.manufacturers_name, c.categories_id, l.location_name, con.condition_name, cd.categories_name, c.parent_id FROM ( " . TABLE_PRODUCTS . " p INNER JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id INNER JOIN " . TABLE_CATEGORIES . " c on p2c.categories_id = c.categories_id " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c LEFT JOIN " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '1' LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id LEFT JOIN " . TABLE_LOCATIONS . " l on p.location_id = l.location_id LEFT JOIN " . TABLE_CONDITIONS . " con on p.condition_id = con.condition_id LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd on p2c.categories_id = cd.categories_id WHERE c.categories_status=1 and products_status = '1' ORDER BY c.parent_id, categories_id, products_name"); Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367236 Share on other sites More sharing options...
roopurt18 Posted October 11, 2007 Share Posted October 11, 2007 Well that's about all I can suggest. Maybe fenway or barand will come by and sort you out. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367238 Share on other sites More sharing options...
BYGino Posted October 11, 2007 Author Share Posted October 11, 2007 Well, thanks for trying anyway. I've just read the thread you linked to and I now understand why you said to try this and why you ditched the comma. I think now I have a slight idea of where the problem lies I'll be able to do some more research into it. Again many thanks. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367241 Share on other sites More sharing options...
fenway Posted October 11, 2007 Share Posted October 11, 2007 Just wrap any tables "around" comma with parens. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367369 Share on other sites More sharing options...
BYGino Posted October 11, 2007 Author Share Posted October 11, 2007 I'm guessing as there's no comma in the modified code you mean with the original code. I did try that but I'll double check again just in case I missed something stupid. Thanks Fenway, I'll let you know if it works. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367425 Share on other sites More sharing options...
BYGino Posted October 11, 2007 Author Share Posted October 11, 2007 OK tried just about every combination of parenthesis I can with the original code and still cannot get it to work. I tried putting parens after the from and before the first left join (grouping the 3 comma'd tables) I tried putting perens around each table individually a a tried a combination of both, nothing seems to work. I think I should go get some sleep now and try again with a fresh head in the morning LOL. In case anyone else can see something I can't here's my original code in a neater format... SELECT p.products_id, pd.products_name, p.products_price, m.manufacturers_name, c.categories_id, l.location_name, con.condition_name, cd.categories_name, c.parent_id FROM (" . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) LEFT JOIN " . TABLE_MANUFACTURERS . "m ON p.manufacturers_id = m.manufacturers_id LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . "pd ON p.products_id = pd.products_id AND pd.language_id = '" . (int)$languages_id . "' LEFT JOIN " . TABLE_SPECIALS . "s ON p.products_id = s.products_id LEFT JOIN " . TABLE_LOCATIONS . "l ON p.location_id = l.location_id LEFT JOIN " . TABLE_CONDITIONS . "con ON p.condition_id = con.condition_id LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . "cd ON p2c.categories_id = cd.categories_id WHERE c.categories_status =1 AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND products_status = '1' ORDER BY c.parent_id, categories_id, products_name LIMIT 0 , 30 And the error... MySQL said: #1064 - 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 '" . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATE' at line 2 Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367454 Share on other sites More sharing options...
roopurt18 Posted October 12, 2007 Share Posted October 12, 2007 Wait a minute. How come the MySQL error looks like it's referring to unparsed PHP code? You have constants in there (TABLE_) that I'm guessing are defined with, well, define(). I think I found the source of your original error. http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html You have a table named condition, which is also a reserved word. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367623 Share on other sites More sharing options...
BYGino Posted October 12, 2007 Author Share Posted October 12, 2007 Yes, there' all defined using DEFINE() in a seperate file. I just noticed that CONDITION is only reserved since 5.0 that could be the problem actually, since work for years on 4.x. I'm not entirely convinced though as I use all the tables elsewhere on the site and this is the only query in hundreds that I'm actually having difficulty with. It's worth a shot though, I really don't want to have to change all my other queries to fit though but if that's the problem, I'll just have to bite the bullet and do it. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367663 Share on other sites More sharing options...
fenway Posted October 12, 2007 Share Posted October 12, 2007 Yes, there' all defined using DEFINE() in a seperate file. I just noticed that CONDITION is only reserved since 5.0 that could be the problem actually, since work for years on 4.x. I'm not entirely convinced though as I use all the tables elsewhere on the site and this is the only query in hundreds that I'm actually having difficulty with. It's worth a shot though, I really don't want to have to change all my other queries to fit though but if that's the problem, I'll just have to bite the bullet and do it. I'll debug an actual SQL query for you... echo the interpolated query string. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-367827 Share on other sites More sharing options...
BYGino Posted October 31, 2007 Author Share Posted October 31, 2007 Sorry guys, I totally forgot to come back and say thanks for all the help with this. The problem was because the word "condition" was reserved in MYSQL 5.x so I have to rename my condition table and update all of my queries. It's really strange the I use "condition" in some queries and it works without a glitch... oh well, would be good to know why but I'm just happy that it's fixed! After fixing that error I ended up with a 1054 because I forgot the paren's again but worked that one out quite quickly. So a big thanks to you both. Damien. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-382365 Share on other sites More sharing options...
roopurt18 Posted October 31, 2007 Share Posted October 31, 2007 You do know that whenever you have a table or column that matches a reserved word you can enclose it in back ticks (`) and continue to use the name? You'd still have to update all your queries, but at least you wouldn't have had to rename the table! Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-382380 Share on other sites More sharing options...
BYGino Posted October 31, 2007 Author Share Posted October 31, 2007 Actually I feel a bit stupid now, I didn't realise you could do that! Thank you so much, you've saved me a lot of hassle actually by telling me that! I think it's time I do some more brushing up on my SQL! Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-382387 Share on other sites More sharing options...
fenway Posted November 1, 2007 Share Posted November 1, 2007 The problem was because the word "condition" was reserved in MYSQL 5.x so I have to rename my condition table and update all of my queries. It's really strange the I use "condition" in some queries and it works without a glitch... oh well, would be good to know why but I'm just happy that it's fixed! Depending on the context, the parser will sometimes automatically interpret words as literals.... Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-382735 Share on other sites More sharing options...
fenway Posted November 1, 2007 Share Posted November 1, 2007 The problem was because the word "condition" was reserved in MYSQL 5.x so I have to rename my condition table and update all of my queries. It's really strange the I use "condition" in some queries and it works without a glitch... oh well, would be good to know why but I'm just happy that it's fixed! Depending on the context, the parser will sometimes automatically interpret words as literals.... but if an expression can be used, it gets stuck. Quote Link to comment https://forums.phpfreaks.com/topic/72745-solved-1064-error/#findComment-382736 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.