mysql query LIMIT use attribute :variable

Question

i am getting a Syntax error or access violation, when i use the below format in the query.

$limit = $request->getAttribute('limit');
$sql = "SELECT * FROM users WHERE status = 1 ORDER BY date DESC LIMIT :limit";

 try{
    $db = new db();
    $db = $db->connect();
    $stmt = $db->query($sql);
    $stmt->bindParam(":limit", $limit);

    $users = $stmt->fetchAll(PDO::FETCH_OBJ);

    $db = null;

    if(empty($users)) {
        $response->getBody()->write
        ('
        {
            "error":
            {
                "message":"Invalid Request"
            }
        }');
    } else {
        $response->getBody()->write(json_encode($users));
    }
} catch(PDOException $e) {}

how can i call the attribute for the limit within the query ?

Check this one: https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause — Ofir Baruch, Aug 31 '17 at 12:30
_"Syntax error or access violation"_ - Which is it? You should also look up how to use prepared statements. You're currently not preparing it _and_ you're trying to bind the param _after_ you've made the query to the database. — M. Eriksson, Aug 31 '17 at 12:31

Alive to die - Anant · Accepted Answer · 2017-08-31T13:15:24.513

3

You need do it like below (changes are commented):-

$limit = $request->getAttribute('limit');
$sql = "SELECT * FROM users WHERE status = 1 ORDER BY date DESC LIMIT :limit";

try{
    $db = new db();
    $db = $db->connect();
    $stmt = $db->prepare($sql); //prepare sql first
    $stmt->bindValue(':limit',(int)$limit,PDO::PARAM_INT);//tell value is integer
    $users = $stmt->fetchAll(PDO::FETCH_OBJ);

    if(count($users) ==0) {
        $response->getBody()->write('{"error":{"message":"No More Users Exist"}}');
    } else {
        $response->getBody()->write(json_encode($users));
    }
    $db = null;
} catch (PDOException $e) {
   print "Error!: " . $e->getMessage() . "<br/>";
   die();
}

edited Aug 31 '17 at 13:15

answered Aug 31 '17 at 12:33

Alive to die - Anant

70,531
10
51
98

still no result i get No More Users Exist – mirvatJ Aug 31 '17 at 13:30
1

no you answered my question, and fixed my syntax error that i was getting, this is something form the query since even if i replace `:limit` with `limit = 2` i still get no data, i'l figure it out, thanks for your help buddy – mirvatJ Aug 31 '17 at 13:38

BeetleJuice · Answer 2 · 2017-08-31T12:38:56.777

The syntax error you see occurs because when you bindParam(), PDO by default inserts your parameter as a string, so the DB engine sees something like LIMIT "10".

So you have 2 choices: you could tell PDO to insert the value as an integer (see @Alive to Die answer) or, more simply, you can cast it to an integer and put it right in the query. Since you're casting first, there is no risk of SQL injection:

$limit = (int) $request->getAttribute('limit');
$sql = "SELECT * FROM users WHERE status = 1 ORDER BY date DESC LIMIT $limit";

Azee · Answer 3 · 2017-08-31T12:36:22.750

0

It's better to use prepare and execute statement like :

$limit=10
$req=$db->prepare('SELECT * FROM users WHERE status = ? ORDER BY date DESC LIMIT ?');
$req->execute(array(1, $limit));

I never use bindParam statement.

edited Aug 31 '17 at 12:36

answered Aug 31 '17 at 12:31

Azee

71
12

3

`$db->('SELECT...`? I think you missed the `prepare()`. Also, I don't think `status` is the same as `limit`. The OP have the value of `status` hard coded, so keep it hard coded. – M. Eriksson Aug 31 '17 at 12:34
1

Yes, corrected . – Azee Aug 31 '17 at 12:36

score 0 · Answer 4 · answered Aug 31 '17 at 12:33

0

Symply put your $limit variable in query

$limit = $request->getAttribute('limit');
$sql = "SELECT * FROM users WHERE status = 1 ORDER BY date DESC LIMIT $limit";

answered Aug 31 '17 at 12:33

Sadikhasan

18,365
21
80
122

score 0 · Answer 5 · answered Aug 31 '17 at 12:34

0

In PDO define it as an INT

$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);

answered Aug 31 '17 at 12:34

Carlos Alves Jorge

1,919
1
13
29

it works like that but i keep getting invalid request as if the response $users is empty – mirvatJ Aug 31 '17 at 12:36

mysql query LIMIT use attribute :variable

5 Answers5