PHP PDO MySQL query LIKE -> multiple keywords

Question

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.'%';
}
?>

i would say put your keywords in an array, and inside a loop add an OR statement for each keyword. there comes a point where they're not really keywords anymore — Jeff Hawthorne
– Jeff Hawthorne, Commented Oct 30, 2014 at 18:48

nebulousGirl · Accepted Answer · 2014-11-03 14:15:27Z

3

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;

edited Nov 3, 2014 at 14:15

answered Oct 30, 2014 at 19:04

nebulousGirl

1,3922 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3476448 Over a year ago

Hey there! Thanks for your assistance. I am getting the following error: Notice: Array to string conversion. I know what it means but I do not know how to fix this in this code. Can you assist me with that? Thanks!

user3476448 Over a year ago

$query->execute(array($arguments));

nebulousGirl Over a year ago

I don't know if it's too late, but remove the array() around $arguments. I edited my answer.

Eric Anderson · Accepted Answer · 2014-10-30 18:56:27Z

0

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

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

answered Oct 30, 2014 at 18:56

Eric Anderson

3862 gold badges4 silver badges15 bronze badges

1 Comment

i alarmed alien Over a year ago

Please edit your answer to add an explanation of how your code works and how it solves the OP's problem. Many SO posters are newbies and will not understand the code you have posted.

Asik · Accepted Answer · 2014-10-30 19:16:31Z

0

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."%" ;
}
?>

answered Oct 30, 2014 at 19:16

Asik

7,9674 gold badges30 silver badges35 bronze badges

Collectives™ on Stack Overflow

PHP PDO MySQL query LIKE -> multiple keywords

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related