CODEWITHSUNDEEP
phpmysql
Anup Singh Rana
Anup Singh Rana

Reputation: 3

how to search multiple words in mysql in php

In my code I am not able to search values when I input queries like "word 1, word2" in the search box. My problem is that it does not display anything.

Here is my code:

<?php
include("config.php");

?>
<html>
<body>
<form method="post" action="">
<input type="text" name="q" /> <input type="submit" value=" Search " />
</form>
<?php 
if($_SERVER["REQUEST_METHOD"] == "POST")
{
$q=explode(',',$_POST['q']);
$q1=$q[0];
$q2=$q[1];
$q3=$q[2];
$q=mysql_escape_string($q);
$q_fix=str_replace(" ","%",$q); // Space replacing with %
$sql=mysql_query("SELECT title FROM articles WHERE title LIKE N'%$q_fix%'");

while($row=mysql_fetch_array($sql))
{
//$title=$row['title'];
//echo '<div>'.$title.'</div>';
echo $row['title'].'<br/>';
}

}


?>
</body>
</html>

Upvotes: 0

Views: 260

Answers (3)

daxeh
daxeh

Reputation: 1085

Since there is only an input named q:

<input type="text" name="q" />

Therefore the following will accessing the $_POST key-value array by then call explode()

<?php 
$delimiter = ',';    

// validations, set value if exists otherwise null for evaluation 
$q = isset($_POST['q']) ? $_POST['q'] : null;

// null or check for empty string for early termination
if(!$q) {
  // TODO: handle error
  die();
}

// not null, assuming it is comma separated, validations done after  
$q = explode($delimiter, $q);

// validations ie. explode() returns FALSE, [] or [ .., .. ] so:
if(!$q or !is_array($q) or count($q) <= 0) {
  // TODO: handle error
  die();
} 

// some examples to prevent sql injection, xss, 
// omitting implementation of sanitize()
// refer to docs for purpose of each for different security preventions
// mysql_escape_string(), html_entities(), stripslashes(), trim();

$sanitised_array = sanitise($q);

// build query string while sanitizing inputs
// *Note: that LIKE is probably best avoided in this case for
// performance reasons given 'n' inputs ( although we know 'n' is small in this context ). 
// references are below, use IN ( slightly better ) instead

$sql_string = "SELECT title FROM articles WHERE title IN ($sanitised_array)";

// execute query
$result = mysql_query($sql_string);
if (!$result) {
  die('ERROR[BAD]: ' . mysql_error());
}

// mysql_fetch_array — Fetch a result row as an associative array,
// a numeric array, or both

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  // validations
  if($row == null or !isset($row['title'])) {
    // TODO: log error  
    continue;
  }

  // *htmlentities()
  echo $row['title'].'<br/>';
}


?>

References:

  1. MySQL IN
  2. Why we do validations and sanitise user inputs
  3. PHP Data Filtering
  4. Database optimisations for LIKE, IN etc in another post
  5. Article for further DB readings
  6. mysql_query() - note the deprecation notice, use mysqli_query() instead

Hope this helps.

Upvotes: 0

Michal Przybylowicz
Michal Przybylowicz

Reputation: 1668

Try something like:

$sql="SELECT title FROM articles WHERE title";
foreach($q as $word) {
    if (!empty($word)) {
        $q_fix = str_replace(" ","%",$word);
        $sql .= " LIKE N'%{$word}%' OR";
    }
}
$sql = rtrim($sql, 'OR ');
print_r($sql);

Upvotes: 0

Marc B
Marc B

Reputation: 360872

With your query construct, not possible. With LIKE and multiple words that can be in any location, you have to split things up:

WHERE title LIKE '%word1%' AND LIKE '%word2%' AND ...

This sort of searching is highly inefficient - the DB cannot use indexes at all. You'd be better off using a fulltext index instead.

With your construct, WHERE title LIKE '%foo bar%', you're requiring that the exact phrase foo bar be somewhere in the field. MySQL won't split that up look for foo and bar separately. So if your field contains foo baz bar, you won't match anything.

Upvotes: 1

Related Questions