Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]

[Epicballzy]

Hello, I've been working on rather simple comment system and when I try and grab all the comments that are "connected" to a post, I receive the Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000] error.

 

The 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 '-5, 5' at line 1' in C:\xampp\htdocs\post\index.php:90 Stack trace: #0 C:\xampp\htdocs\post\index.php(90): PDO->query('SELECT * FROM c...') #1 {main} thrown inC:\xampp\htdocs\post\index.php on line 90

 

My code:

$start = 0;
$limit = 10;

if(isset($_GET['p'])) {
    $id = $_GET['p'];
} else {
    redirect("/");
}

$start = ($id - 1) * $limit;
$query = $database->query("SELECT * FROM comments WHERE post_id = '$id' ORDER BY date DESC LIMIT $start, $limit");

while($row = $query->fetch()) {
$author = $row['author'];
echo $author;
}

I honestly don't understand why this isn't working. I've used the exact same syntax for my post section on my website.

 

If anything else is needed, just let me know.

Thanks for the help!

[Psycho]

Based upon the error message, specifically this

 

 

. . . the right syntax to use near '-5, 5'

 

Your variables for the $start variable is being calculated as a negative five (i.e. -5). But, something doesn't make sense, because $limit is apparently 5, but you have it hard-coded in the snippet above at 10. So, I'm guessing that is not the entire code or you copy/pasted some 'example' code, but not the actual code.

 

You should create your queries as string variables so you can echo them to the page to validate what they contain when you have variable values.

$sql = "SELECT * FROM comments WHERE post_id = '$id' ORDER BY date DESC LIMIT $start, $limit";
echo $sql; //Debug line
$query = $database->query($sql);

But, why are you using the numerical value of the id passed on the query string to determine the start index? That is not a proper way to do that. Yes, the indexes may seem to start at 1 and increment up at 1, but you can never guarantee that in your database.

Edited February 16, 2015 by Psycho

[Epicballzy]

I got it fixed..

 

$sql = "SELECT * FROM comments WHERE post_id = '$id' ORDER BY date DESC LIMIT $start, $limit";
echo $sql; //Debug line
$query = $database->query($sql);

^ Helped me out, so thanks for that. I'll keep that in mind for next time.

 

My code actually had some variables mixed up. I'm using pagination, so I actually need 2 variables in the header. 1 for the page id (pagination) and one for the actual post and such. I was using the page id to get the comments instead of the post id.

 

But anyways, thanks a lot for the helpful tip!