CODEWITHSUNDEEP
phpmysqlsearchsql-like
user3476448
user3476448

Reputation: 11

PHP PDO MySQL query LIKE -> multiple keywords

I have a users table in MySQL and would like a search by name. Right now I have the following code:

<?php
$search = @$_GET['q'];
$search = strtoupper($search);
$search = strip_tags($search);
$search = trim($search);

$query = $con->prepare('SELECT * FROM `users` WHERE name LIKE ?');
$query->execute(array('%'.$search.'%'));

$result = $query->rowCount();
echo $result;
?>

The problem is that I want to have multiple keywords. Let's say someone types "Here should be a nice name of a person" then it would search for "here", "should", "be" etc. and display results for every row where there words are in 'name' column. I searched on the web and read that it is possible to do "OR name LIKE ?" as many times as the keywords, but I could not really get it working and I'm not sure if it is optimized enough with ~20 words (in case they search with that many words). If it should be used, can you help me change my code so it would search for every word independently?

Thank you!

EDIT:

I was able to fix this issue by one guy who posted in this thread. The following solution works for me:

<?php
$search = isset($_POST['q']) ? $_POST['q'] : '';
$search = strtoupper($search);
$search = strip_tags($search);
$search = trim($search);

$split_words = explode(" ", $search);

if(count($split_words) > 0) {

    $sql = "SELECT * FROM `users` WHERE ";

    for($i=0 ; $i < count($split_words); $i++){
        $sql .= " name LIKE ? OR";
    }

    $sql = substr($sql , 0, -3); //Remove last 3 characters OR with space
    array_walk($split_words, "addPercentage");


    $query = $con->prepare($sql);
    $query->execute($split_words);
}

function addPercentage(&$value, $key) {
    $value = '%'.$value.'%';
}
?>

Upvotes: 0

Views: 2361

Answers (3)

nebulousGirl
nebulousGirl

Reputation: 1364

You shouldn't use @ to silence errors it is a bad practice, check if the value is set. The example below should work, but the results might not be all that relevant.

$search = isset($_GET['q']) ? $_GET['q'] : ''; 
$search = strtoupper($search);
$search = strip_tags($search); 
$search = trim($search);
$words = explode(' ', $search);
$words_condition = array();
$arguments = array();
foreach ($words as $word) {
    $words_condition[] = 'name LIKE ?';
    $arguments[] = '%'.$word.'%';
}

$query = $con->prepare('SELECT * FROM `users` WHERE '.implode(' OR ', $words_condition));
$query->execute($arguments);

$result = $query->rowCount();
echo $result;

Upvotes: 3

Asik
Asik

Reputation: 7977

Please check the below code.

<?php
$search = isset($_GET['q']) ? $_GET['q'] : '';
$search = strtoupper($search);
$search = strip_tags($search);
$search = trim($search);

$split_words = explode(" ", $search);

$query = "SELECT * FROM `users`";

if(count($split_words) > 0){
 $query .= " WHERE "
 for($i=0 ; $i < $split_words; $i++){
   $query .= " name LIKE ? OR ";
 }
 $query = substr($query , 0, -3); //Remove last 3 characters OR with space


 array_walk($split_words,"addPercentage");

 $query->execute($split_words);
}else{
 $query->execute();
}

$result = $query->rowCount();
echo $result;

function addPercentage(&$value,$key)
{
  $value = "%".$value."%" ;
}
?>

Upvotes: 0

Eric Anderson
Eric Anderson

Reputation: 366

$words = explode(" ", $search);

$i = 0;
while($words[i] != null)
{
    //Query where name LIKE words[i]
}

Upvotes: 0

Related Questions