why this sql not working?

Question

I have a query

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 the limit as below, then it doesnt 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 will be appreciated

Answer 1

Put a space in front of LIMIT:

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

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

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

Answer 2

Try changing

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

To

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

It looks like your SQL is getting squished together and should be getting a bad syntax error, and you had the wrong (seemingly) variable names in there.

Answer 3

Your variables are called $limit and $start:

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

Answer 4

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