SQL: Syntax error or access violation

0
votes

Before I start, I looked at many other posts but none have actually helped.

I get an "SQLSTATE[42000]: Syntax error or access violation" error. This error is being cause by this line:

$sql2 = $database->query("SELECT * FROM `users` LIMIT $limit");

$limit is:

$limit = ($page_id-1)*$itemsPerPage.','.$itemsPerPage;

My full error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[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,10' at line 1' in C:\xampp\htdocs\pdo\index.php:76 Stack trace: #0 C:\xampp\htdocs\pdo\index.php(76): PDO->query('SELECT * FROM `...') #1 {main} thrown in C:\xampp\htdocs\pdo\index.php on line 76

I've spent so long trying to work out this problem. I've tried executing the query without $limit. But that does not work.

Heres how I'm doing the for loop:

$limit = ($page_id-1)*$itemsPerPage.','.$itemsPerPage;
$sql2 = $database->query("SELECT * FROM `users` LIMIT $limit");

while($row = $sql2->fetch(PDO::FETCH_OBJ)) {
    //My stuff in here
}

If theres any sort of way someone could help, that'd be great. Thanks!

2
phpmysqlexceptionpdo
Your offset cannot be a negative number. - Jay Blanchard
-10,10 says it all: You cannot set an negative offset on your limit. - Marc B
Table name should not have quotes around it. Try just $sql2 = $database->query("SELECT * FROM users LIMIT $limit"); and see if it likes that better. - kittykittybangbang
I don't understand that though. It worked perfectly when I used mysql_*. - Matt
@kittykittybangbang His syntax is fine. His calculation of the LIMIT offset is wrong. - user1864610

2 Answers

0
votes

Your calculation of the LIMIT parameters is flaky.

In this line: 

$limit = ($page_id-1)*$itemsPerPage.','.$itemsPerPage;

if $page_id is undefined or zero your first term goes negative and results in a limit clause of LIMIT -10,10 which is what causes the syntax error.

Make sure that $page_id has the value you think it has.

0
votes

You need to initialize $page_id with 1, I think the first time it's not defined:

if (!isset($page_id)) $page_id = 1;
$limit = ($page_id-1)*$itemsPerPage.','.$itemsPerPage;
$sql2 = $database->query("SELECT * FROM `users` LIMIT $limit");