dqxuiq7772
2017-11-16 10:54
浏览 115
已采纳

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

图片转代码服务由CSDN问答提供 功能建议

我有一个如下表,表名是 js_skills_achievements

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

当admin 搜索关键字是“nersupalli中的java开发人员”

我的查询在下面

  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 
   
 
 

它不适用于我。 我的要求是我需要显示所有java记录。 这里共有2条java记录。 即id1,id2。 但我得到空行。

以下方式只有工作

  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 
   
 
 

我需要基于字符串指定的关键字搜索。 你能帮忙吗? 非常感谢。 谢谢

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • doucheng9304 2017-11-16 11:02
    已采纳

    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');
    
    打赏 评论
  • duanbi7204 2017-11-16 11:02

    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.

    打赏 评论
  • duanjue6584 2017-11-16 11:03

    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

    打赏 评论
  • dragonpeng200811111 2017-11-16 11:22

    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;';
    
    打赏 评论
  • dongxiequ3724 2017-11-16 11:31
    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;
    
    打赏 评论

相关推荐 更多相似问题