How to query data from MySQL using regex and prepare PDO statement?-CodePudding

I am using a REGEXP to filter MySQL Query. I do not get an error, but the output is not fine. I would like to understand what I am doing wrong. Here is my code

public function ignoredNames($lim, $str_regex, $not_to_considered){
    $params = array_values($not_to_considered);

    $in = join(',', array_fill(0, count($not_to_considered), '?'));
    
    $sql = "SELECT u_name, u_status 
            FROM users
            WHERE u_name NOT IN ($in)
            AND u_name REGEXP ?
            ORDER BY u_id DESC
            LIMIT ?";

    if($stmt=$this->pdo->prepare($sql)){
        array_push($params, $str_regex);
        array_push($params, $lim);

        $stmt->execute($params);

        echo "<pre>";
        $stmt->debugDumpParams();
        echo "</pre>";

        return $stmt->fetchAll(PDO::FETCH_OBJ);
    }
}

Call the function

$not_to_considered = array ( "name1" => "Paul", "name2" => "Paola", "name3" => "John" )
ignoredNames(5, 'sp', $not_to_considered)

While debuging with

echo "<pre>";
$stmt->debugDumpParams();
echo "</pre>";

I get:

SELECT u_name, u_status 
FROM users
WHERE u_name NOT IN ('Paul','Paola','John')
AND u_name REGEXP 'sp'
ORDER BY u_id DESC
LIMIT '5'

Could someone please tell me what is wrong with my query ?

CodePudding user response：

You can not pass LIMIT in prepared statement. You can use something like:

$sql = "SELECT u_name, u_status 
            FROM users
            WHERE u_name NOT IN ($in)
            AND u_name REGEXP ?
            ORDER BY u_id DESC
            LIMIT " . intval($lim);

if($stmt=$this->pdo->prepare($sql)){

    $stmt->execute([$str_regex]);
    return $stmt->fetchAll(PDO::FETCH_OBJ);
}

CodePudding user response：

I think you are missing the $not_to_considered as one of the $params ? ... the $in has ?,?,?, so you need 5 params in all for those 3, the regex, and the limit:

if($stmt=$this->pdo->prepare($sql)){
    array_push($params, $not_to_considered); // add this line
    array_push($params, $str_regex);
    array_push($params, $lim);