Jump to content
Sign in to follow this  
helloworld001

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

Recommended Posts

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 by helloworld001

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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);

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.