使用LIKE和LIMIT逐个查询字符串单词的结果

Question

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:)

Answer 1

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