[SOLVED] 1064 error

[BYGino]

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.

 

[roopurt18]

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.

[BYGino]

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?

[roopurt18]

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.

[BYGino]

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.

[BYGino]

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

 

[roopurt18]

Well that's about all I can suggest.  Maybe fenway or barand will come by and sort you out.

[BYGino]

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.

[fenway]

Just wrap any tables "around" comma with parens.

[BYGino]

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.

[BYGino]

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

 

 

[roopurt18]

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.

[BYGino]

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.

[fenway]

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.

[BYGino]

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.

 

 

 

[roopurt18]

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!

[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!

[fenway]

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

[fenway]

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.