CODEWITHSUNDEEP
phpmysqlsql
Niranjan Kumar Chowdam
Niranjan Kumar Chowdam

Reputation: 405

SELECT QUERY LIKE containing only specific words in a string

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

Upvotes: 3

Views: 314

Answers (5)

Ponni
Ponni

Reputation: 433

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;

Upvotes: 0

Kaddath
Kaddath

Reputation: 6151

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

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 2

Subhankar Mitra
Subhankar Mitra

Reputation: 175

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');

Upvotes: 1

Graham
Graham

Reputation: 1960

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.

Upvotes: 0

Related Questions