I have an error and need eyes to look at a mysql statement

$query_article = sprintf("SELECT * FROM articles, articles_cat_main 
    WHERE articles.id = %s 
 开发者_如何学运维   AND articles.cat_main = articles_cat_main.id_articles_cat_main", 
    $colname_article);

Im getting : 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 'AND articles.cat_main = articles_cat_main.id_articles_cat_main' at line 1

I can't see anything obvious.. its been a long day.

Thanks

---

after edit

If the variable is a numeric, just concat instead of using sprintf

$query_article = "
SELECT * FROM articles, articles_cat_main 
WHERE articles.id = " . $colname_article . "
AND articles.cat_main = articles_cat_main.id_articles_cat_main";

But in either case, if the variable is empty or does not contain anything, your query becomes

WHERE articles.id = AND articles.cat_main

So it errors out.

original

It is very obvious that the PHP you showed is not what is being run!

It has a

WHERE articles.cat_main ..

The error message has

near 'AND articles.cat_main

Check your code to see what you are really running

---

SELECT * FROM articles, articles_cat_main
WHERE articles.id = %s AND ...

And %s is replaced with the content of a variable called $colname_article. Based on the name of that variable, I don't think it contains an integer, that can be inserted there without quotes.

If it is either empty or contains a string without quotes, it will result in the syntax error you saw.

Please note that this kind of dynamic SQL statements is prune to have SQL injection vulnerabilities.

---

Do you have a field in 'articles_cat_main' called 'id_articles_cat_main'. If not, then you have a some stuff to delete and that should fix it.

---

You need to make it a JOIN to include the second table articles_cat_main. So:

$query_nextprev = "SELECT * FROM articles JOIN articles_cat_main ON articles.cat_main = articles_cat_main.id_articles_cat_main WHERE articles.active = 'y' ORDER BY articles_cat_main.name_cat_main, articles.`order`";

---

Whatever the value in $colname_article is what's causing your issue. Most likely that value is not a number but in the query mysql is expecting it to be a number or a column name. Most likely the value is a string. Or it's empty.

You can quote it like this:

$query_article = sprintf("SELECT * FROM articles, articles_cat_main WHERE articles.id = '%s' AND articles.cat_main = articles_cat_main.id_articles_cat_main", $colname_article);

If you're expecting it to be a string. Or if you want it to be a number you should use %d instead of %s because then if you have something other than a number you'll get a PHP warning (but the query will still fail)

As a bonus the setup as you currently have it has the potential for a SQL injection attack. You may have other code that prevents that but if not you should escape $colname_article with mysql_real_escape_string() or better yet use PDO and placeholders which will do the same substitution as you're doing here with sprintf and handle the escaping.