Need help with this error SQLSTATE[42000]: Syntax error or access violation: 1064

[helloworld001]

I have a pdo prepared statement that fetches records from mysql database.  The records show up on the page. However if there are no records on a page, I get this error message.

 

"QLSTATE[42000]: Syntax error or access violation: 1064 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 '-10' at line 4"

 

Here is my statement

$getCategoryId = $_GET['id'];
$limit = 10;
$offset = ($page - 1)  * $limit;

$statement = $db->prepare("SELECT records.*, categories.* FROM records 
LEFT JOIN categories ON records.category_id = categories.category_id
WHERE records.category_id = :category_id 
ORDER BY record_id DESC LIMIT {$limit} OFFSET ".$offset);
$statement->bindParam('category_id', $getCategoryId);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);

If I remove try and catch block, it'll tell me exactly which line is giving the issue.  So from the above code, the error has to do with "$statement->execute();".  This is where the error occurs.

 

As far as I know, the above pdo statement is correct.  Can you tell me if something is wrong with it?

Edited December 16, 2014 by helloworld001

[Ch0cu3r]

Your query is fine. The problem is with the use of $offset which is defined here

$offset = ($page - 1)  * $limit;

Here it takes away one from $page and then multiplies that by $limit (which is 10).

 

Problem is if no page has been requested $page will be 0. This will cause ($page - 1) to return -1 and then that is multiplied by $limit (which is 10). This will result in $offset being set to -10 and that is the cause of the error

 

You only want to calculate the offset if $page is not 0.

$offset = 0;  // default offset value

// if $page greater than zero, calculate the offset
if($page > 0)
    $offset = ($page - 1)  * $limit;

[NotionCommotion]

Try the following.  I am expecting you will see "OFFSET -10".  Is this what you want?

echo("SELECT records.*, categories.* FROM records
LEFT JOIN categories ON records.category_id = categories.category_id
WHERE records.category_id = :category_id
ORDER BY record_id DESC LIMIT {$limit} OFFSET ".$offset);

[helloworld001]

Here is the $page variable.

					$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
						'options' => array(
							'default'   => 1,
							'min_range' => 1,
						),
					)));

@NotionCommontion - yes I do see "OFFSET -10" in error when I try your way.

 

@Ch0cu3r - I will try your method.

[helloworld001]

Alright so I got it working using your method Ch0cu3r.

                                       if($page > 0) {
						$offset = ($page - 1)  * $limit;
					} else {
						$offset = 0;
					}

Thank you!

[NotionCommotion]

Offset needs to be zero or positive.  ChOcu3r response told you why it was negative.