0

I have the following method in a PHP class:

function getAll($search, $start, $limit) {
    $search = "%$search%";

    $sql = 'SELECT * FROM users WHERE valid = 1 AND user_name LIKE :user_name LIMIT :start, :limit';
    $sth = $this->db->prepare($sql);
    $sth->bindParam(':user_name', $search);
    $sth->bindParam(':start', $start, PDO::PARAM_INT);
    $sth->bindParam(':limit', $limit, PDO::PARAM_INT);
    $sth->execute();
    return $sth->fetchAll(PDO::FETCH_ASSOC);
}

Works as I expect. When I try to use an array to execute() something goes wrong and my $search variable is not prepared properly resulting in a MySQL error:

function getAll($search, $start, $limit) {
    $search = "%$search%";
    $params = [
            ':user_name' => $search,
            ':start' => $start,
            ':limit' => $limit
    ];

    $sql = 'SELECT * FROM users WHERE valid = 1 AND user_name LIKE :user_name LIMIT :start, :limit';
    $sth = $this->db->prepare($sql);
    $sth->execute($params);
    return $sth->fetchAll(PDO::FETCH_ASSOC);
}

What am I doing wrong? What is the difference resulting in an SQL error?

EDIT:

My 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 MySQL server version for the right syntax to use near ''0', '3'' at line 1' in /home/ubuntu/workspace/curs24/users.php on line 69

PDOException: 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 ''0', '3'' at line 1 in /home/ubuntu/workspace/curs24/users.php on line 69

Community
  • 1
  • 1
bsteo
  • 1,738
  • 6
  • 34
  • 60
  • What error are you getting? – Mureinik May 14 '16 at 12:09
  • Syntax error, view edited question. Is something like I would do a query: `SELECT * FROM users WHERE valid = 1 AND user_name LIKE %% LIMIT 0, 3` and not `SELECT * FROM users WHERE valid = 1 AND user_name LIKE '%%' LIMIT 0, 3` – bsteo May 14 '16 at 12:10
  • 2
    It's because passing an array to `execute()` binds all params as strings, which MySQL won't accept. `LIMIT` must be bound as integers, so you have to use `bindParam()/bindValue()`. – Michael Berkowski May 14 '16 at 12:12

0 Answers0