search for multiple keywords with php and mysql (where X like)

Question

I have a code that dynamically search for data in the database using ajax but I can search for only 1 keyword in a time. I would like to modify it so I can search for multiple keywords. Now, if I type 2 keywords separated by a space and in the database, the data is not separated by a space, there will be no result. If in the database the data is:

'playstation3' or 'play cool station3'

and I search for:

play station

there would be no results. I would like to know if it possible to modify my code so I can search 2 or more keywords or words separated by a space or another word or a DOT or an underscore or a (-) or a (+) or a (%) or (anything else lol).

I know that I should use pdo or mysqli but i'm using this for testing only!

$queried = $_POST['query'];



$search = mysql_query("SELECT * FROM links WHERE name LIKE '%$queried%'");
while($searche = mysql_fetch_array($search)){
    echo "".$searche['link']."</br>".$searche['name']."</br>".$searche['size']."</br>".$searche['category']."<hr></br></br>";

    }

Ibu · Accepted Answer · 2018-05-01T01:00:51.407

23

To dynamically search all keywords, you can use the explode function to seperate all keywords;

$queried = mysql_real_escape_string($_POST['query']); // always escape

$keys = explode(" ",$queried);

$sql = "SELECT * FROM links WHERE name LIKE '%$queried%' ";

foreach($keys as $k){
    $sql .= " OR name LIKE '%$k%' ";
}

$result = mysql_query($sql);

Note 1: Always escape user input before using it in your query.

Note 2: mysql_* functions are deprecated, use Mysqli or PDO as an alternative

Update 2018 - Note 3: Don't forget to check the length of the $queried variable and set a limit. Otherwise the user can input a vary large string and crash your database.

edited May 01 '18 at 01:00

answered Mar 21 '13 at 02:55

Ibu

42,752
13
76
103

How can this be converted to a prepared statement? Count the number of placeholders and add them dynamically-however that works? – ArabianMaiden Mar 16 '19 at 21:47
@BillWhickomb you can use `count($keys)` to know how many parameters there are. – Ibu Mar 17 '19 at 00:59
Do not forget to secure your query – amir22 Apr 30 '19 at 06:53
1

@AmirhosseinTarmast that's what the very first line does. See a more [robust version here](https://idiallo.com/blog/php-mysql-search-algorithm) – Ibu Apr 30 '19 at 17:07

score 2 · Answer 2 · edited Apr 21 '19 at 09:54

2

Don't use mysql_* functions even for testing anymore. They are no easier than mysqli, just in case you think easy to test here then move.

However, you could split your input on a , and try this

<?php

$queried="abc,test, testing";
$values=explode(',',$queried);


$sql="SELECT * FROM links WHERE";
$i=0;

foreach($values as $v)
{
    $v=trim($v);
    if($i==0)
    {
        $sql.=" name LIKE '%$v%'";
    }
    else
    {
        $sql.=" OR name LIKE '%$v%'";
    }

    $i++;
}

$search = mysql_query($sql);
while($searche = mysql_fetch_array($search))
{
    echo "".$searche['link']."</br>".$searche['name']."</br>".$searche['size']."</br>".$searche['category']."<hr></br></br>";

}    
?>

edited Apr 21 '19 at 09:54

Martin James

902
1
9
25

answered Mar 21 '13 at 02:53

Hanky Panky

46,730
8
72
95

Thank you very much! It works. I think that I can automatically replace spaces by %20 and then automatically get the different words. Thank you! it really helpful – user1932820 Mar 21 '13 at 03:06
Glad it helps. The `trim` function in here will automatically remove extra spaces from either the start or end of your keywords so you dont have to manually replace spaces – Hanky Panky Mar 21 '13 at 03:08

Alex Maina · Answer 3 · 2018-10-31T10:22:40.887

This is a MySQL PDO version of @Hanky Panky's answer

 $queried="abc,test, testing";
 $values=explode(',',$queried);
 $sql="SELECT * FROM citations_new WHERE";
 $i=0;
        foreach($values as $v){
            $v=trim($v);
            if($i==0) {
                $sql.=" name LIKE '%$v%'";
            }
           else {
               $sql.=" OR name LIKE '%$v%'";
           }
           $i++;
         }

$sth = $this->db->prepare($sql);
$sth->execute();
$rows = $sth->setFetchMode(PDO::FETCH_ASSOC);

while($row = $sth->fetch()){
    //enter code here   
}

score 0 · Answer 4 · answered Mar 21 '13 at 02:49

0

Just add an OR clause.

E.g.

SELECT * FROM links WHERE name LIKE '%$keyword1%' OR name LIKE '%$keyword2%'

But I strongly recommend you using a parametrized query or other safe library to avoid Sql Injection Attacks

answered Mar 21 '13 at 02:49

Nathan

10,593
10
63
87

You are right, Thank you ! But I don't have 2 forms, I have 1 form and when I search, it not accurate. Here is an example of what i'm looking for ---> http://mega-search.me/ When you type, you can put more than one keywords separated by a space and the query will find a result. – user1932820 Mar 21 '13 at 02:57

search for multiple keywords with php and mysql (where X like)

4 Answers4

Linked