I am receiving a pagination error in my php script. The query works fine when being ran directly inside mysql workbench and comes back with the proper results.
Error Returned: 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 'LIMIT 0, 20' at line 2
$getpositive = "select case_number, c.name as subject, a.name, u.first_name, u.last_name from cases as c join cases_cstm as cc on c.id = cc.id_c
left join accounts as a on a.id = c.account_id left join users as u on u.id = c.assigned_user_id where rating_c ='1';";
$db -> PS_Pagination($getpositive, 20, 5, "");
$db -> setDebug(true);
$rs = $db->paginate();
$positive_rating_rows = mysql_num_rows($rs);
I am then displaying those results in a table:
while($val = mysql_fetch_assoc($rs))
{
?>
</tr>
<tr>
<td width="7%"><?=$val['case_number']?></td>
<td width="40%"><?=$val['subject']?></td>
<td width="40%"><?=$val['name']?></td>
</tr>
Here is my pagination function:
public function PS_Pagination($sql, $rows_per_page = 10, $links_per_page = 5, $append = "") {
//$this->conn = $connection;
$this->sql = $sql;
$this->rows_per_page = (int)$rows_per_page;
if (intval($links_per_page ) > 0) {
$this->links_per_page = (int)$links_per_page;
} else {
$this->links_per_page = 5;
}
$this->append = $append;
$this->php_self = htmlspecialchars($_SERVER['PHP_SELF'] );
if (isset($_GET['page'] )) {
$this->page = intval($_GET['page'] );
}
}
public function paginate() {
//Check for valid mysql connection
if (! $this->IsConnected()) {
$this->SetError("No connection");
return false;
}
//Find total number of rows
$all_rs = @mysql_query($this->sql );
if (! $all_rs) {
if ($this->debug)
echo "SQL query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
return false;
}
$this->total_rows = mysql_num_rows($all_rs );
@mysql_close($all_rs );
//Return FALSE if no rows found
if ($this->total_rows == 0) {
if ($this->debug)
//echo "Query returned zero rows.";
return FALSE;
}
//Max number of pages
$this->max_pages = ceil($this->total_rows / $this->rows_per_page );
if ($this->links_per_page > $this->max_pages) {
$this->links_per_page = $this->max_pages;
}
//Check the page value just in case someone is trying to input an aribitrary value
if ($this->page > $this->max_pages || $this->page <= 0) {
$this->page = 1;
}
//Calculate Offset
$this->offset = $this->rows_per_page * ($this->page - 1);
//Fetch the required result set
//echo $this->sql . " LIMIT {$this->offset}, {$this->rows_per_page}";
$rs = @mysql_query($this->sql . " LIMIT {$this->offset}, {$this->rows_per_page}" );
if (! $rs) {
if ($this->debug)
echo "Pagination query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
return false;
}
return $rs;
}