The Mysql query does not find the first word

I'm building a simple search engine in PHP that retrieves animal names from a MySQL database when the user searches it.

Say I have the following table:

ID |  Name
---------------
1  |  Red panda
2  |  Okapi
3  |  Red fox

When the user inputs "panda" it returns the Red Panda row, when they input "red panda" it again returns the Red Panda row.

But when a user inputs "red" it returns nothing.

However searching "Okapi" does work.

For some reason searching the first word in a multiple made of multiple words doesn't return anything.

The query I'm using to find the data is the following:

"SELECT * FROM example_table WHERE Name LIKE '%%$search'"

---

If you need to find anything which contains what you're searching for then you should use the % wildcard both before and after your $search in the query.

Hence you should do:

$query = "SELECT * FROM example_table WHERE Name LIKE %$search%";

The way you're currently using will match only those values which have the $search at the end of it.

For instance:

ID |  Name
---------------
1  |  Red panda
2  |  Okapi
3  |  Red fox
4  |  kaok    // added for examples

• matching %ka:
  won't return anything because kaok and Okapi have something after the ka. With this query you're looking for everything that starts with your expression;
• matching %ka%:
  will return both kaok and Okapi. With this query you're looking for everything that contains your expression;
• matching ka%:
  will return only kaok. With this query you're looking for everything that ends with your expression.

Take a look at the MySQL Dev guide about pattern matching.

Of course, as pointed out by Elzo Valugi, in his answer you need to remember to sanitise your inputs to avoid SQL Injections.