search for multiple keywords with php and mysql (where X like)
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.
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>";
}
?>
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
}