My PDO Prepared Statement is not working with a fetchALL and Limit

Question

I am trying to get some products from my database with a prepared PDO statements. The formula worked well if I included the variable inside the SQL but of course this is really bad practice.

Working formula:


protected function getSomeProducts($somequantity){
        $sql = "SELECT * FROM products ORDER by ID DESC LIMIT $somequantity";
        
        $stmt = $this->connect()->query($sql);
        $result = $stmt->fetchAll();
        return $result;

My approach to the prepared statement:

protected function getSomeProducts($somequantity){
        $sql = "SELECT * FROM products ORDER by ID DESC LIMIT ?";
        
        $stmt = $this->connect()->prepare($sql);
        $stmt->execute([$somequantity]);
        $result = $stmt->fetchAll();

        return $result;
        
        
    }

This is the error message I get:

Fatal error : Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''6'' at line 1.

Any idea what Im doing wrong could be?

i think it might be because you are binding a parameter to the limit clause. have a look at this question. https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause — RyDog, Feb 04 '21 at 03:59

score 0 · Accepted Answer · edited Feb 04 '21 at 05:25

0

replace the below line

$stmt->execute([$somequantity]);

with

$stmt->bindParam(1, $somequantity, PDO::PARAM_INT);
$stmt->execute();

edited Feb 04 '21 at 05:25

Your Common Sense

156,878
40
214
345

answered Feb 04 '21 at 04:06

John Doe

1,401
1
3
14

Worked like a charm, thank you! – sdgv00 Feb 04 '21 at 04:51
Accept the answer please if it helps you. – John Doe Feb 04 '21 at 04:55

My PDO Prepared Statement is not working with a fetchALL and Limit

1 Answers1