SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error

Question

I'm using php and pdo

Here is my code:

try {
    $sql="LOCK TABLE appleid WRITE, appleid AS appleid1 READ;";
    $stmt = $GLOBALS['$connection']->prepare($sql);
    $stmt->execute();
    $sql="SELECT MAX(num) FROM appleid;";//to know how many rows it has
    $stmt = $GLOBALS['$connection']->prepare($sql);
    $stmt->execute();
    $result=$stmt->fetch();
    $table_top=$result[0];

    if (empty($head)) $head = 1;
    $check=$table_top - $head;
    $check++;//number of available rows that are ready to use

    if($check>=$this->apple_id_num)
    {
        $sql = "SELECT id,pass,en_b_y,en_b_m,en_b_d,sqa1,sqa2,sqa3 FROM 
        appleid AS appleid1
        WHERE num>=$head LIMIT $this->apple_id_num ORDER BY `TimeStamp` 
        DESC;";
        $stmt = $GLOBALS['$connection']->prepare($sql);
        $stmt->execute();
        $this->pre_head=$head;      
        $head=1+$head+$this->apple_id_num;
        $sql="UNLOCK TABLES;";
        $this->num_rows = $stmt->rowCount();
        echo $stmt->rowCount();
    }
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}

I get this error :

SELECT id,pass,en_b_y,en_b_m,en_b_d,sqa1,sqa2,sqa3 FROM appleid AS appleid1 WHERE num>=1 LIMIT 1 ORDER BY TimeStamp DESC; 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 'ORDER BY TimeStamp DESC' at line 2

I got confused and I don't know what is wrong with this query and how to fix it.

Answer 1

SELECT id,pass,en_b_y,en_b_m,en_b_d,sqa1,sqa2,sqa3 FROM 
        appleid AS appleid1
        WHERE num>=$head ORDER BY `TimeStamp` 
        DESC LIMIT $this->apple_id_num ; 

put your limit after orderby

Answer 2

Use orderby clause before limit clause.

$sql = "SELECT id,pass,en_b_y,en_b_m,en_b_d,sqa1,sqa2,sqa3 FROM 
appleid AS appleid1
WHERE num>=$head ORDER BY `TimeStamp` DESC LIMIT $this->apple_id_num;";

Answer 3

Change

"SELECT id,pass,en_b_y,en_b_m,en_b_d,sqa1,sqa2,sqa3 FROM 
    appleid AS appleid1
    WHERE num>=$head LIMIT $this->apple_id_num ORDER BY `TimeStamp` 
    DESC;";

to

"SELECT id,pass,en_b_y,en_b_m,en_b_d,sqa1,sqa2,sqa3 FROM 
    appleid AS appleid1
    WHERE num>=$head LIMIT {$this->apple_id_num} ORDER BY `TimeStamp` 
    DESC;";

since the double quotes would not know whether to parse the ->apple_id_num as string or part of a variable.

Answer 4

I have not tested, but timeStamp sound like a reserved word of mysql,

maybe you can try a correct quote of this field, or best pratice change the name of the field.