MySQL PHP loop search query

Question

I'm trying to find matches in MySQL database using PHP loops to build the query:

public function findMatches(array $columns, string|array $lookingFor) {

    is_array($lookingFor) ?: $lookingFor = array($lookingFor);

    $colString = '';
    $whereString = '';
    $toBind = [];

    foreach ($columns as $col) {

        $colString .= $col . ',';

        foreach ($lookingFor as $item) {

            $whereString .= $col . " LIKE '%?%' OR ";
            
            $toBind[] = $item;

        }
    }

    $sql = 'SELECT ' . rtrim($colString, ',') . ' FROM ' . $this->table .
    ' WHERE ' . rtrim($whereString, ' OR ');
    
    $stmt = $this->dbc->prepare($sql);

    $pos = 1;

    foreach ($toBind as $val) {
        
        $stmt->bindValue($pos, $val);
        $pos++;

    }
    
    $stmt->execute();

    return $stmt->fetchAll();

}

The $toBind values count is exactly same as iterations of the bind loop, so I'm not sure what I did wrong. I would appreciate help to solve following error:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

`'%?%'`...the `'` makes it a literal string, so the `?` doesn't count as a placeholder. You need to put the wildcard (%) in as part of the parameter value, not as surrounding text. — ADyson, Aug 07 '22 at 20:12
See here for the correct approach [How do I create a PDO parameterized query with a LIKE statement?](https://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement) — ADyson, Aug 07 '22 at 20:13

MySQL PHP loop search query

0 Answers0