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'

Answer 1

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

Answer 2

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

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