SELECT QUERY LIKE仅包含字符串中的特定单词

问题描述：

I have one table like below ,table name is js_skills_achievements

id   skill1  skill2  skill3  skill4
1     java    php    html    android
2     php     java   jquery  html
3     sql     php    html    jquery

When admin search key is "java developer in nersupalli"

my query is below

SELECT *
FROM `js_skills_achievements`
WHERE `skill1`  LIKE '%java developer in nersupalli%'
   OR `skill2`  LIKE '%java developer in nersupalli%'
   OR `skill3`  LIKE '%java developer in nersupalli%'
   OR `skill4`  LIKE '%java developer in nersupalli%'
ORDER BY id DESC
LIMIT 10

Its not working for me . My Requirement is i need to display all java records . here total 2 records in java . i.e id1,id2. But am getting empty rows.

Below ways only working

SELECT *
FROM `js_skills_achievements`
WHERE `skill1`  LIKE '%java%'
   OR `skill2`  LIKE '%java%'
   OR `skill3`  LIKE '%java%'
   OR `skill4`  LIKE '%java%'
ORDER BY id DESC
LIMIT 10

I need based on string specified keyword search . can you any one please help on this . Am very appreciate. Thanks

我有一个如下表，表名是 js_skills_achievements strong> p>

  id skill1 skill2 skill3 skill4 
1 java php html android 
2 php java jquery html 
3 sql php html jquery 
  code>  pre> 
 
 当admin 搜索关键字是“nersupalli中的java开发人员” p> 
 
 
我的查询在下面 p> 
 
 
  SELECT * 
FROM`js_skills_achievements` 
WHERE'technit1`  LIKE'％java开发人员在nersupalli％'
或``skill2` LIKE'％java开发人员在nersupalli％'
或``skill3` LIKE'％java开发人员在nersupalli％'
或``skill4` LIKE'％java developer 在nersupalli％'
ORDER BY id DESC 
LIMIT 10 
  code>  pre> 
 
 它不适用于我。 我的要求是我需要显示所有java记录。 这里共有2条java记录。 即id1，id2。 但我得到空行。   p> 
 
 
以下方式只有工作 p> 
 
 
  SELECT * 
FROM`js_skills_achievements` 
WHERE'technit1` LIKE'％java％'
 OR  `skill2` LIKE'％java％'
或``skill3` LIKE'％java％'
或``skill4` LIKE'％java％'
ORDER BY id DESC 
LIMIT 10 
  code>  pre  > 
 
 我需要基于字符串指定的关键字搜索。 你能帮忙吗？ 非常感谢。 谢谢 p> 
  div>

答

Add full text in your filed

ALTER TABLE `js_skills_achievements` ADD FULLTEXT(`skill1`);
ALTER TABLE `js_skills_achievements` ADD FULLTEXT(`skill2`);
ALTER TABLE `js_skills_achievements` ADD FULLTEXT(`skill3`);
ALTER TABLE `js_skills_achievements` ADD FULLTEXT(`skill4`);

And then run this query

SELECT * FROM js_skills_achievements
WHERE MATCH(skill1,skill2,skill3,skill4) AGAINST ('java developer in nersupalli');

答

You can explode the search string on a space

$words = explode ( ' ' , $string);

Than search all the words in skill1. All the words in skill2 etc.

答

A bit ugly way to do this is reverse your like clauses and match your string against columns

SELECT 
  * 
FROM
  js_skills_achievements 
WHERE 'java developer in nersupalli' LIKE CONCAT('%',skill1,'%')
  OR 'java developer in nersupalli' LIKE CONCAT('%',skill2,'%')
  OR 'java developer in nersupalli' LIKE CONCAT('%',skill3,'%')
  OR 'java developer in nersupalli' LIKE CONCAT('%',skill4,'%')
  OR skill1  LIKE '%java developer in nersupalli%'
  OR skill2  LIKE '%java developer in nersupalli%'
  OR skill3  LIKE '%java developer in nersupalli%'
  OR skill4  LIKE '%java developer in nersupalli%'
ORDER BY id DESC 
LIMIT 10

Demo

答

Other answers are more technical and probably more optimized, however if you wish to have full control on your query, like changing OR to AND and things like this, you can split your query in words and build it manually:

//your search query
$query = 'java developer in nersupalli';

//in case you want to add more skills later
$skills = ['skill1', 'skill2', 'skill3', 'skill4'];

//split your query in an array of words
$words = explode(' ', $query);

//build the sql
$condOuter = '';
foreach($skills as $skill){
    $condInner = '';
    foreach($words as $word){
        $condInner .= " OR `$skill` LIKE '%$word%'";
    }
    $condInner = substr($condInner, 4);
    $condOuter .= " OR ($condInner)";
}
$condOuter = substr($condOuter, 4);
$sql = 'SELECT * FROM `js_skills_achievements` WHERE ' . $condOuter . ' ORDER BY id DESC LIMIT 10;';

答

SELECT *
FROM `js_skills_achievements`
WHERE '%java developer in nersupalli%' LIKE CONCAT('%',skill1,'%')
   OR '%java developer in nersupalli%' LIKE CONCAT('%',skill2,'%')
   OR '%java developer in nersupalli%' LIKE CONCAT('%',skill3,'%')
   OR '%java developer in nersupalli%' LIKE CONCAT('%',skill4,'%')
ORDER BY id DESC
LIMIT 10;

SELECT QUERY LIKE仅包含字符串中的特定单词

相关推荐