CODEWITHSUNDEEP
phpmysql
Vahid
Vahid

Reputation: 382

mysql query result for a string words one by one with LIKE and LIMIT

as we know it, MySQL can get results by LIKE and LIMIT, but my problem is how we can search DB for some words of a string one by one and show result without duplication?

my means is for example when user make search for "a book for php and mysql" we send query in For loop, word to word to DB by: 

SELECT * FROM table WHERE title LIKE %i% LIMIT 5

but this code just show duplicated result, indeed for each word it shows 5 results then for another word another 5 result and... i need to search db for words one by one, but at last show just 5 match results without duplication! sorry for my poor english:)

Upvotes: 1

Views: 2622

Answers (3)

tosin
tosin

Reputation: 1179

Would you like to achieve this only by MySQL? I show one with PHP:

$result = array();
$excludeIds = array(); //store recored id already got
$words = explode(" ", $_POST['input']);
foreach($words as $word) {

  //if there is no 'id' column, use another one instead
  $condForNoDup = ($excludeIds) ?
    "AND id NOT IN (".implode(',', $excludeIds).")" : "";

  $word = '%'.mysql_real_escape_string($word).'%';
  $sql = "SELECT * FROM table
          WHERE title LIKE '{$word}' {$condForNoDup}
          LIMIT 5";

  //executeQuery() returns array which has 'id' as key
  if($_result = executeQuery($sql)) {
    $excludeIds = array_merge($excludeIds, array_keys($result));
    $result = array_merge($result, $_result);
  }
}
print_r($result);

Awkward...I also want to know smart answer only with MySQL:)

Upvotes: 1

Zuul
Zuul

Reputation: 16269

I've been reading the comments and your question, and I think that you need a bit of PHP to prepare your MySql statement, and a Mysql Query to fetch matching results limited to the first 5 without repeated items?

If so!!

PHP

<?php
  $searchFieldData = $_POST['search'];
  $searchArr = explode(" ", $searchFieldData);

  $sqlWhere = '';
  $count = count($searchArr);
  foreach ($searchArr as $or) {
    $sqlWhere.= " title LIKE '%".$or."%' ";
    if ($count>1) $sqlWhere.= " OR ";
    $count--;
  }

  $query = "SELECT col1, col2, ... FROM table WHERE ".$sqlWhere." LIMIT 5";
?>

The above code would return for the search term: "super hyper query crawler"

SELECT col1, col2, ... FROM table WHERE title LIKE '%super%' OR title LIKE '%hyper%' OR title LIKE '%query%' OR title LIKE '%crawler%' LIMIT 5

NOW FOR THE MYSQL MAGIC

If you don't want any repeated results, just use: GROUP BY 'title'

and your final query would be: 

$query = "SELECT col1, col2, ... FROM table WHERE ".$sqlWhere." GROUP BY 'title' LIMIT 5";

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 839114

I think you want something like this:

SELECT col1, col2, ...
FROM table
WHERE title LIKE '%a%' OR title LIKE '%b%' OR title LIKE '%c%'
LIMIT 5

Notes:

  • Using LIKE '%xxx%' is slow for large data sets. Consider using an indexed full text search instead.
  • Searching for words like 'a' or 'the' will likely give lots of useless matches. Consider using a stop word list or some other approach to improve the relevance of the matches.

Upvotes: 1

Related Questions