could not execute mysql query in right way

Question

I've data in DB as

| id|title        | exp_level  | skill      | company   |
|---|-------------|------------|------------|-----------|
| 1 |Web Developer|  Entry     |Java, PHP   |Virtual Inc|
| 2 |App Developer|Intermediate|Android, iOS|Virtual Inc|
| 3 |App Developer|Intermediate|Ionic, React|Virtual Inc|

result can be seen at SQL Fiddle Here, OK, now requirement is that if user searches for developer then all jobs with title developer should be returned through query, if user searches for Entry then all jobs with level Entry should be returned through query and so on, I have this query

SELECT * FROM AVAIL_JOBS WHERE title LIKE '%App Development, Social Marketing%' OR exp_level LIKE '%Entry%' OR skill LIKE '%Java%' OR company LIKE '%App Inc%'

the above query works well, result can be seen at SQL Fiddle Here, but the following query

SELECT * FROM AVAIL_JOBS WHERE title LIKE '%App Development, DB Admin%' OR exp_level LIKE '%Entry, Advanced%' OR skill LIKE '%Java, Spring, Angular%' OR company LIKE '%Inc, Foo, Bar, Lorem%'

this query returns empty value, no any data, even if there is "App", "Entry", "Java" present in table but those are not found by query result can be seen at SQL Fiddle Here.

How to make this query work with values shown above? Any tick to get those comma seprated string values compared with table data?

The question looks legitimate, why down vote any explanation? Disclaimer I'm not a MySQL ninja — Crazy Coder
– Crazy Coder, Commented Jul 20, 2017 at 18:44

Alexys Leclerc · Accepted Answer · 2017-07-20 18:43:51Z

1

Change WHERE title LIKE '%App Development, DB Admin%' to WHERE title LIKE '%App Development%' OR title LIKE '%DB Admin%'. Putting commas in the LIKE condition does not separate the strings. I hope I got it right !

answered Jul 20, 2017 at 18:43

Alexys Leclerc

214 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

GrumpyCrouton Over a year ago

Alternatively you could do WHERE title IN ("%App Development%", "%DB Admin%") I'm pretty sure

Crazy Coder Over a year ago

That's it the problem I'm facing, I have to get those comma containing values separated and get the response

Alexys Leclerc Over a year ago

You can use $myValue = explode(',',$myValue); to separate them, and be able to loop through them. you can also explode them and use WHERE title REGEXP 'title1|title2|title3'

Crazy Coder Over a year ago

Any example please!!

Alexys Leclerc Over a year ago

Let's say you have Entry, Advanced in $title. I would replace the commas with $title = str_replace(',','|',$title). After, i would remove the whitespace with $title = trim($title, " "). Now my $title is ready to be used with WHERE title REGEXP '$title'

Collectives™ on Stack Overflow

could not execute mysql query in right way

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related