MySQL query works in phpMyAdmin and fails in PHP

Question

SELECT SQL_CALC_FOUND_ROWS * 
FROM (
   SELECT * 
   FROM tbl_substances
   LIMIT 0 , 25
) AS s
LEFT JOIN (
   SELECT subid, list1, list2, list3, list4, list5
   FROM tbl_substances_lists
   WHERE orgid =  '1'
) AS x ON s.subst_id = x.subid
LEFT JOIN (
   SELECT subid, info
   FROM tbl_substances_info
   WHERE orgid =  '1'
) AS y ON s.subst_id = y.subid

The idea is that you have a master list of substances (tbl_substances) then if you have entered any information about them in tbl_substances_lists or tbl_substances_info then that can be displayed too (as long as you are logged in with the right organisation ID)

It's important to show all the substances even if they have no custom information which is why I'm using a LEFT JOIN.

This query works perfectly in phpMyAdmin but when I use it in my database script I get:

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 ') AS s LEFT JOIN (SELECT subid, list1, list2, list3, list4, list5 FROM tbl_substances_' at line 2

I'm not sure whether the problem is something obvious I'm missing or is it something to do with the fact that this bit of code uses mysql_query which I know is deprecated and old fashioned etc. etc.

I'm not a database expert so if this query looks very ugly to you then I apologise in advance!

EDIT 2

Here's the code for building this query (it gets built dynamically depending on what you're searching for but this is the basic form)

    /*
     * Length
     */

    if ( isset( $_POST['iDisplayStart'] ) && $_POST['iDisplayLength'] != '-1' )
    {
        $sLimit = "LIMIT ".mysql_real_escape_string( $_POST['iDisplayStart'] ).", ".
            mysql_real_escape_string( $_POST['iDisplayLength'] );
    }


    /*
     * Ordering
     */

    $sOrder = "";
    if ( isset( $_POST['iSortCol_0'] ) )
    {
        $sOrder = "ORDER BY  ";
        for ( $i=0 ; $i<intval( $_POST['iSortingCols'] ) ; $i++ )
        {
            if ( $_POST[ 'bSortable_'.intval($_POST['iSortCol_'.$i]) ] == "true" )
            {
                $iColumnIndex = array_search( $_POST['mDataProp_'.$_POST['iSortCol_'.$i]], $aColumns );
                $sOrder .= $aColumns[ $iColumnIndex ]."
                    ".mysql_real_escape_string( $_POST['sSortDir_'.$i] ) .", ";
            }
        }

        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
        {
            $sOrder = "";
        }
    }

        /*
         * Table info
         */
    $sTable = "tbl_substances ".$sLimit.") AS s 
LEFT JOIN (
  SELECT subid, list1, list2, list3, list4, list5 
  FROM tbl_substances_lists 
  WHERE orgid = '".$orgid."'
) AS x 
ON s.subst_id = x.subid 
LEFT JOIN (
  SELECT subid, info 
  FROM tbl_substances_info WHERE orgid = '".$orgid."'
) AS y 
ON s.subst_id = y.subid";

        $sWhere = "";


    /*
     * SQL queries
     * Get data to display
     */

    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT * FROM $sTable
                $sWhere
        $sOrder
        $sLimit
    ";

        $rResult = mysql_query( $sQuery ) or die(mysql_error());

Imagine there is nothing in $sWhere and $sOrder at the moment - $sLimit is chosen by the user but in this case it will be LIMIT 0, 25 to get the first 25 records.

This all combines in this case to make the result of echoing out $sQuery:

SELECT SQL_CALC_FOUND_ROWS * 
FROM (
  SELECT * 
  FROM tbl_substances LIMIT 0, 25
) AS s 
LEFT JOIN (
  SELECT subid, list1, list2, list3, list4, list5 
  FROM tbl_substances_lists 
  WHERE orgid = '1'
) AS x 
ON s.subst_id = x.subid 
LEFT JOIN (
  SELECT subid, info 
  FROM tbl_substances_info 
  WHERE orgid = '1'
) AS y 
ON s.subst_id = y.subid

Answer 1

Haven't analyzed the code, but querywise I see no syntax error. But I'd advise, that you write the query like this:

SELECT SQL_CALC_FOUND_ROWS * 
FROM tbl_substances s
LEFT JOIN tbl_substances_lists x ON s.subst_id = x.subid AND x.orgid = '1'
LEFT JOIN tbl_substances_info y ON s.subst_id = y.subid AND y.orgid = '1'
LIMIT 0, 25

Should give the same result.