BYGino

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!

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.

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.

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...

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.

Don't know if it will make much difference but you're first code has a double comma between your user_level and email strings.

Hi, I had the exact same problem a couple of years ago trying to group items and list them sequentially in groups and never did quite get to the bottom of it. In the end I chose to hactk the PHP code using loops to pull the data in the order I wanted. Don't suppose that's the answer you are looking for but it's an idea if you don't have any luck with it. I wouldn't really advise it though, it messy and bloated but it works for the task I needed it for.

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.

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");

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.

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?

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: I really hope someone can point out the obvious to me here, I've search around for ages and can't find an answer.