PDO Cannot link two attributes
I am trying to associate a search term and limit value with a request to do PDO, but I get errors no matter how I do it
public static function searchUsersByName($searchTerm, $results = null) {
//getDBConnection
if($results == null) {
$results = 5;
}
$searchTerm = '%'.$searchTerm.'%';
$query = $database->prepare("SELECT user_id, user_firstname, user_lastname
FROM users_details
WHERE user_firstname LIKE :searchTerm
OR user_lastname LIKE :searchTerm
LIMIT :results");
$query->bindParam(':searchTerm', $searchTerm, PDO::PARAM_STR);
$query->bindParam(':results', $results, PDO::PARAM_INT);
$query->execute();
$search_results = array();
foreach ($query->fetchAll() as $user) {
$search_results[$user->user_id] = new stdClass();
$search_results[$user->user_id]->user_id = $user->user_id;
$search_results[$user->user_id]->user_firstname = $user->user_firstname;
$search_results[$user->user_id]->user_lastname = $user->user_lastname;
}
return $search_results;
}
This is the error I am getting from this:
PDOStatement::execute(): 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 "5"
It works fine if I pull the binding for LIMIT and just hardcode 5
into the SQL query, but I want to be able to change it if possible
$query->execute(array(':searchTerm' => '%'.$searchTerm.'%', ':results' => $results));
I've tried doing this, but of course PDO automatically puts quotes around the values it inserts with this method, and as far as I know, you can't put quotes around PDO::PARAM_INT
when using this method.
What am I doing wrong?
source to share
Could it be that $ results is not an integer? The error appears to be that your PHP code is sending a string to the request, which explains the error.
I guess this is a problem due to the following piece of code
if($results == null) {
$results = 5;
}
How is the result of $ first determined? Via GET / POST? It can then be converted to a string.
I tried my piece of code myself and cast it to int, committing it for me.
$query->bindParam(':results', intval($results), PDO::PARAM_INT);
source to share