SQL syntax error on nested query

Question

I'm maintaining an old website that is throwing some sql errors on a mysql query. The error goes as follows...

 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 'OR syprc_serial IN (SELECT b.syprc_serial FROM syproductcategories.b WHERE b.syp' at line 1

I suspect this is something to do with the syntax of my nested select though I've had no success in fixing this yet! Anyone got some insight

$query = "SELECT syprc_serial, syprd_serial, sypcd_description, sypdd_title, sypdd_shortdescription, sypdd_longdescription, sypdd_primarypackage, sypdd_owner, syprd_image01, syprc_subcategoryof ".
     "FROM syproductcategories, syproductcategoriesdescription, syproducts, syproductsdescription ".
     "WHERE syprc_serial = sypcd_productcategoryserial ".
     "AND syprd_categoryserial = syprc_serial ".
     "AND sypdd_languagecode = '". $_SESSION['language']."' ".
     "AND sypcd_languagecode = '". $_SESSION['language']."' ".
     "AND syprd_serial = sypdd_productserial ".
     "AND syprc_companyserial = '" . $_SESSION['company'] ."' ".
     "AND syprd_companyserial = '" . $_SESSION['company'] ."' ".
     "AND syprd_active = 1 ".
     "AND (syprc_serial = $syprc_serial ".
        "OR syprc_serial IN (SELECT b.syprc_serial FROM syproductcategories b WHERE b.syprc_subcategoryof = $syprc_serial) ".
        "OR syprc_subcategoryof IN (SELECT b.syprc_serial FROM syproductcategories b WHERE b.syprc_subcategoryof = $syprc_serial) ".
    ") ".
    "ORDER BY syprc_serial, sypdd_title, syprd_serial";

if you just print your query after that line, you should see exactly what it looks like and its easier to check for errors. — Olli
– Olli, Commented Sep 19, 2014 at 9:59
@TheChaos Without the real query we can only speculate that $syprc_serial evaluates to an empty string ... — VMai
– VMai, Commented Sep 19, 2014 at 10:05
Where did you defined 'b' ? Error message and query seem different: 'FROM syproductcategories b' without dot... — Grégory Vorbe
– Grégory Vorbe, Commented Sep 19, 2014 at 10:07

Thorsten Kettner · Accepted Answer · 2014-09-19 10:42:16Z

1

The syntax looks fine to me. Whatever is in $_SESSION['language'] and $_SESSION['company'] will be put in a string literal. However $syprc_serial wouldn't. So it is likely that $syprc_serial doesn't contain a number.

This is supposed to happen: WHERE b.syprc_subcategoryof = 123).
And this not: WHERE b.syprc_subcategoryof = ABC).
And this neither: WHERE b.syprc_subcategoryof = ).
Or even this: WHERE b.syprc_subcategoryof = (),-_;).

What column type is syprc_subcategoryof? If it's a string then surround it with quotes like you do for the session variables. Otherwise simply make sure that the variable contains a number.

Things like these are easy to detect: Print the final statement. If you don't see immediately what's wrong, then use it directly with the dbms. Remove and add parts till it works.

edited Sep 19, 2014 at 10:42

answered Sep 19, 2014 at 10:33

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL syntax error on nested query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related