Can someone explain why this query using LIMIT works when the other one doesn't?

Question

I have a query that when I use the bindParam() for start and per_page in PDO does not work. However if I add these in directly as seen in query 2 it works fine. I have 2 questions:

Why does the bindParam() not work in query 1 for :start and :per_page?

These values start and per_page are not inputted by the user. They are taken from elsewhere so do I need to use bindParam()?

public function t_status($friends, $groups, $user_id, $start, $per_page,    $db){

$group_array = implode(',', $groups);
$friend_array = implode(',', $friends);

$stmt = $db->prepare("SELECT * FROM statuses WHERE user_id IN (:friend_array) OR user_id = :auth_id OR group_id IN (:group_array) ORDER BY updated_at LIMIT $start, $per_page");
$stmt->bindParam(':auth_id', $user_id);
$stmt->bindParam(':group_array', $group_array);
$stmt->bindParam(':friend_array', $friend_array);

$stmt->execute();
if ($stmt->rowCount() > 0) {
    while($row = $stmt->fetchAll()) {
        return $row;
    }
}

}

Query 2:

public function t_status($friends, $groups, $user_id, $start, $per_page, $db){

    $group_array = implode(',', $groups);
    $friend_array = implode(',', $friends);

    $stmt = $db->prepare("SELECT * FROM statuses WHERE user_id IN (:friend_array) OR user_id = :auth_id OR group_id IN (:group_array) ORDER BY updated_at LIMIT :start, :per_page");
    $stmt->bindParam(':auth_id', $user_id);
    $stmt->bindParam(':group_array', $group_array);
    $stmt->bindParam(':friend_array', $friend_array);
    $stmt->bindParam(':start', $start);
    $stmt->bindParam(':per_page', $per_page);

    $stmt->execute();
    if ($stmt->rowCount() > 0) {
        while($row = $stmt->fetchAll()) {
            return $row;
        }
    }
}

Apologizes if this is a simple question. I'm learning PDO and I'm trying to get a better understanding of it.

I read again the question and think this isn't duplicated by the page, but this: [Running queries in PDO without Binding](http://stackoverflow.com/a/23281851/5816907). — Chay22, May 26 '16 at 12:41

score 2 · Accepted Answer · answered May 26 '16 at 12:36

2

Change :

$stmt->bindParam(':start', $start);
$stmt->bindParam(':per_page', $per_page);

to

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

answered May 26 '16 at 12:36

chandresh_cool

11,753
3
30
45

This works perfect. I'll accept it in a few minutes when I can. I just read up on PDO::PARAM_INT and some more. Should I always include one of these when I can in my PDO bindParam()? Thank you. – Niall May 26 '16 at 12:43
1

Cool no issues. Ideally you should pass it since the page limit and offsets are ints. – chandresh_cool May 26 '16 at 12:44

Can someone explain why this query using LIMIT works when the other one doesn't?

1 Answers1