MySQL Select data separated strings

Question

I am trying to select a record using the LIKE function. However, it does not show results if there are characters in between. I want it to select data that contains those strings. For example:

 $value = "Mark Anthony";
 $qry ="SELECT * FROM students WHERE name LIKE '%$value%'";

returns these results:

 John Mark Anthony
 Mark Anthony Philipps

but I also want to have results like these

 Mark James Anthony
 Mark Gabriel Anthony Fernandez
 Marko Julian Anthonyo

Any ideas? UPDATE: 'Mark' must be before 'Anthony'

Roopendra · Accepted Answer · 2013-12-12 08:09:54Z

6

I think Full TEXT works

But Full-text searches are supported for MyISAM tables only

SELECT * FROM students 
  WHERE MATCH (name)
   AGAINST ('Mark Anthony' IN BOOLEAN MODE)

UPDATE:- As per question update OP need to search Marko as well, then you can get like this:-

SELECT * FROM students 
WHERE
(
    name LIKE '%Mark%'
    OR name LIKE '%Anthony%'
)

edited Dec 12, 2013 at 8:09

answered Dec 12, 2013 at 7:43

Roopendra

7,75716 gold badges72 silver badges95 bronze badges

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

4 Comments

Elmer Over a year ago

It's okay, I am using PHPmyadmin anyway. Will try this out.

Roopendra Over a year ago

Yes try it, I think in your case FULL TEXT is best

Elmer Over a year ago

Updated my question. Will results like these appear? Marko Julian Anthonyo

Elmer Over a year ago

Hi thanks for the answer :) Although I have already chosen an answer. Will upvote instead. Thanks!

Jakob · Accepted Answer · 2013-12-12 07:44:06Z

2

You could split the value string into two parts. Like so:

WHERE name LIKE '%Mark%' AND name LIKE '%Anthony%'

answered Dec 12, 2013 at 7:44

Jakob

7384 silver badges17 bronze badges

3 Comments

Elmer Over a year ago

Hi there, I haven't updated the question but Mark has to be before Anthony. With your suggestion I think it will include results with Anthony before Mark. :)

Jakob Over a year ago

Yeah thats a possibility with my query. '%Mark%Anthony%' should do the trick tho.. @Elmer

Elmer Over a year ago

Updated my question. Will results like these appear? Marko Julian Anthonyo

niyou · Accepted Answer · 2013-12-12 08:11:15Z

1

last try:

$value = "Mark Anthony";
$value = str_replace(" ","%",$value);
$qry ="SELECT * FROM students WHERE name LIKE '%$value%'";

not tested!

edited Dec 12, 2013 at 8:11

answered Dec 12, 2013 at 7:48

niyou

8791 gold badge11 silver badges23 bronze badges

4 Comments

Elmer Over a year ago

Updated my question. Do you think it will also include results like this? Marko Julian Anthonyo?

Elmer Over a year ago

Just tested it. No results were found. xD

Elmer Over a year ago

Just a blank page. Not even errors. But your new answer works great! Why didn't I think of str_replace xD Thanks man!

niyou Over a year ago

nice to hear. as you see my first answer was a bit overloaded too ;-)

Devart · Accepted Answer · 2013-12-12 08:19:41Z

1

Try this solution -

SELECT * FROM table WHERE column REGEXP '(Mark).+(Anthony)';

edited Dec 12, 2013 at 8:19

answered Dec 12, 2013 at 7:57

Devart

120k23 gold badges172 silver badges189 bronze badges

2 Comments

Elmer Over a year ago

Haven't tried this yet but was wondering if there was a solution that wouldn't be fixed to Mark Anthony only :) Like if I just want to look for 'Mark'.

Devart Over a year ago

Why haven't you tried this? Regex function sometimes can help.

Kuzgun · Accepted Answer · 2013-12-12 07:42:30Z

0

The proplem is, you say "bring all results which contains Mark Anthony" so it does. You should set it like

$value = "Mark%Anthony";

answered Dec 12, 2013 at 7:42

Kuzgun

4,7575 gold badges36 silver badges50 bronze badges

Collectives™ on Stack Overflow

MySQL Select data separated strings

5 Answers 5

4 Comments

3 Comments

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

3 Comments

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related