CODEWITHSUNDEEP
phppdosql-like
Jordy Wittouck
Jordy Wittouck

Reputation: 21

multiple LIKE does not work in PDO (PHP)

Hey I'm having this in my PDO function where I want to check 2 columns of my database with the same input ($string). This comes from my HTML search box. I want the results in one array. However with this code it only checks the genre column and not the name column. But it should check on both columns. Any help? Thanks.

public function search($string){
    $question = '%' . $string . '%';
    $sql = "SELECT * FROM `products` WHERE `name` OR `genre` LIKE :q";
    $stmt = $this->pdo->prepare($sql);
    $stmt->bindValue(':q', $question);
    $stmt->execute();
    $data = '%$data%';
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

I get this error when I use this query:

$sql = "SELECT * FROM `products` WHERE `name` like :q OR `genre` LIKE :q";

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number' in /Applications/MAMP/htdocs/bad/bad/dao/ProductsDAO.php:32 Stack trace: #0 /Applications/MAMP/htdocs/bad/bad/dao/ProductsDAO.php(32): PDOStatement->execute() #1 /Applications/MAMP/htdocs/bad/bad/controller/ProductsController.php(24): ProductsDAO->search('pokemon') #2 [internal function]: ProductsController->index() #3 /Applications/MAMP/htdocs/bad/bad/controller/Controller.php(9): call_user_func(Array) #4 /Applications/MAMP/htdocs/bad/bad/index.php(50): Controller->filter() #5 {main} thrown in /Applications/MAMP/htdocs/bad/bad/dao/ProductsDAO.php on line 32

Upvotes: 0

Views: 884

Answers (2)

Ari Djemana
Ari Djemana

Reputation: 1249

Try this:

public function search($string){
    $question = "%".$string."%";
    $sql = "SELECT * FROM `products` WHERE `name` LIKE :n OR `genre` LIKE :q";
    $stmt = $this->pdo->prepare($sql);
    $stmt->bindValue(':q', $question);
    $stmt->bindValue(':n', $question);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Upvotes: 1

ecarrizo
ecarrizo

Reputation: 2809

Your query isn't passing values to the Where clause, only the column name. Also i edited the way that you are passing the parameters to the execution.

The error that you was experiencing is due to (extract of docs):

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

so I've edited the answer.

Try this:

Modify this lines:

$sql = "SELECT * FROM `products` WHERE `name` OR `genre` LIKE :q";
$stmt->bindValue(':q', $question);
$stmt->execute();

for this ones:

$sql = "SELECT * FROM `products` WHERE `name` LIKE :name OR `genre` LIKE :genre";
$params = array('name' => $question, 'genre' => $question); //or :name and :genre
$stmt->execute($params);

if you still getting errors, please take a look into the documentation it has a few examples in there. PHP PDOStatement::execute Documentation

Upvotes: 2

Related Questions