You have an error in your SQL syntax - Help with query

Question

I have a programmer that developed a custom plug in for a client's website and after moving the site to client's server, some parts are not working. Looking at the error log, there is an issue with his queries. The coding seems a little poor to me and when I looked at it, I noticed that he hardcoded the table prefix in the queries, so I suspect that the rest of his code is poorly written.

The error message I get is:

WordPress database 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 " at line 1 for query select hh3_property_list_tbl.*,hh3_model_list_tbl.model_color from hh3_property_list_tbl,hh3_model_list_tbl where hh3_model_list_tbl.id=hh3_property_list_tbl.model_id and model_id= made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/harmony/floor_plan.php')

Any idea what could cause the mySQL issue? Thank you!

Answer 1

If that is the error, a couple of things could be going on. The part that uses 'require', 'require_once' and 'include' is not likely correct in the code. Each of these code statements should end with a semi-colon [;] not a comma [,]

hh3_ I expect is the table prefix initially used in your database. Is that still the prefix of all the tables??

Vee

Answer 2

As far as I know you cannot just SELECT from multiple tables by separating them with a comma; you need to use a JOIN statement.

This is your current query, which looks like it's truncated in you error message. If not, you need to fix the final statement "AND model_id=" and either give it something to be equal to or delete it.

SELECT hh3_property_list_tbl.*, hh3_model_list_tbl.model_color
FROM hh3_property_list_tbl, hh3_model_list_tbl
WHERE hh3_model_list_tbl.id=hh3_property_list_tbl.model_id
AND model_id=

Try this instead:

SELECT hh3_property_list_tbl.*, hh3_model_list_tbl.model_color
FROM hh3_property_list_tbl
LEFT JOIN hh3_model_list_tbl
ON hh3_property_list_tbl.model_id = hh3_model_list_tbl.id
WHERE hh3_property_list_tbl.model_id= [needs something here]

You can also nickname the tables with an AS statement. It might be a little bit easier to read, but functionally it's the same.

SELECT properties.*, models.`model_color`
FROM `hh3_property_list_tbl` AS properties
LEFT JOIN `hh3_model_list_tbl` AS models
ON properties.`model_id` = models.`id`
WHERE properties.`model_id` = [needs something here]