Why is this sql not working?

Question

I have a request

public static function TestQuery(

 $start=0,
 $limit=0){


 $sql = "
SELECT     count(*) AS total
FROM    db.table1
JOIN    db.table2
 ON     table1.fieldID = {$fieldID}

AND    table2.assigned = 'N'";



  $qry = new SQLQuery;
  $qry->query($sql);
  if($row = $qry->fetchRow()){
   $total = intval($row->total);
  }

 return $total;

} 

which works fine, but when I add a limit as shown below, it doesn't work and gives me errors

public static function TestQuery(

 $start=0,
 $limit=0){


 $sql = "
SELECT     count(*) AS total
FROM    db.table1
JOIN    db.table2
 ON     table1.fieldID = {$fieldID}

AND    table2.assigned = 'N'";

//this fails   
if($recordlimit > 0) $sql .= "LIMIT {$startRecord}, {$recordLimit} ";  
//  
  $qry = new SQLQuery;
  $qry->query($sql);
  if($row = $qry->fetchRow()){
   $total = intval($row->total);
  }

 return $total;

} 

Any help would be appreciated

Answer 1

Put a space before the LIMIT:

" LIMIT {$startRecord}, {$recordLimit} "

without the space you sql will result in a syntax error.

Edit: This answer is incorrect! MySQL will not be an error without a space before LIMIT (however, earlier versions of phpmyadmin will incorrectly parse such sql).

Answer 2

Your variables are called $ limit and $ start:

if($limit > 0) $sql .= " LIMIT {$start}, {$limit} "; 

Answer 3

if($recordlimit > 0) $sql .= "LIMIT {$startRecord}, {$recordLimit} ";

if($recordlimit > 0) $sql .= " LIMIT {$start}, {$limit} ";

It looks like your SQL is being compressed together and should receive a bad syntax error, and you had the wrong names (apparently).

Answer 4

Invalid Variables

if($recordlimit > 0) $sql .= "LIMIT {$startRecord}, {$recordLimit} ";

solvable thanks