Geek asks and answers

Search multiple keywords with php and mysql (where X like)

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

'playstation3' or 'play cool station3'

and I'm looking for:

play station

There were no results. I would like to know if the code can be changed so that I can search for two or more keywords or words separated by a space or another word, or DOT or underscore or (-) or (+) or (%), or (anything another lol).

I know that I should use pdo or mysqli, but I use this only for testing!

$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>";

    }
+8
source share
4 answers

To dynamically search all keywords, you can use the diversity function to separate 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 avoid 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: Remember to check the length of the variable $queriedand set a limit. Otherwise, the user may enter a large string and cause your database to crash.

+23
source

mysql_* . , mysqli, , , , .

, ,

<?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>";

}    
?>
+2

This is the MySQL version for PDO @Hanky ​​Panky.

 $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   
}
+2
source

Just add an OR clause.

eg.

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

But I highly recommend that you use a parameterized query or another secure library to avoid Sql Injection Attacks

0
source

More articles:


All Articles