CODEWITHSUNDEEP
phpmysqlsqlpdomariadb
jack
jack

Reputation: 151

What's wrong within SQL query?

I'm using this query to get range of rows for example 0-5 , But I get an error message.

Here is the code:

if(isset($_GET['page']) && isset($_GET['per'])){        
    $per = $_GET['per'];
    $pageNumber = $_GET['page'];
    $from = ($pageNumber * $per) - $per;
    $results = $pdo->prepare('SELECT * FROM users LIMIT :sfrom , :sto');
    $results->execute(array('sfrom' => $from , 'sto' => $per));
}

I get the following 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 MariaDB server version for the right syntax to use near ''5' , '5''

I think that's because the $from and $per are inserted as strings or something I tried to convert them to int using (int)$from , (int)$per But still having the same error

Upvotes: 2

Views: 83

Answers (2)

hpaknia
hpaknia

Reputation: 3118

Setting $per and $from as numeric strings is totally OK. Make sure you are not passing "'5'" as a value. Casting "'5'" to int results in 0. Try

// remove " and ' then cast to int
$yourNumber = (int) str_replace(['"', "'"], '', $yourInput);

Upvotes: 0

Paolo
Paolo

Reputation: 15847

The values passed must be integers but casting them is not enought.

Instruct PDO the binded parameters' type is integer by using PDOStatement::bindParam

Then call PDOStatement::execute as last step (without parameters).

$results = $pdo->prepare('SELECT * FROM users LIMIT :sfrom , :sto');
$results->bindParam(':sfrom', (int)$from, PDO::PARAM_INT );
$results->bindParam(':sto',   (int)$to,   PDO::PARAM_INT );
$results->execute();

See http://php.net/manual/en/pdostatement.bindparam.php

Upvotes: 2

Related Questions