CODEWITHSUNDEEP
mysqlsql
Dimitri Shukuroglou
Dimitri Shukuroglou

Reputation: 373

SQL syntax error on nested query

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

Upvotes: 0

Views: 79

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

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.

Upvotes: 1

Related Questions