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'"
You can use it like this:
"SELECT * FROM example_table WHERE Name LIKE '%".$search."%'";
You placed a wildcard at the begin of your like string, so you will find any names which end with the search term.
To search for all names which contain the search term add a wildcard at the end of the like string:
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.
It returns nothing because your SQL Query needs to be modified slightly:
Change this
"SELECT * FROM example_table WHERE Name LIKE '%%$search'"
to this:
"SELECT * FROM example_table WHERE Name LIKE '%$search%'"
By adding % on both sides of the searched string, your database will look for any column value that has that word inserted into it.
Let me know if that solved it for you or if there are any more issues!
try this
% wildcard should be like this
$query = "SELECT * FROM example_table WHERE Name LIKE '%$search%'";
A side note, do not aggregate the user input directly to an SQL query. This is totally unsafe practice and leads to SQL injection, in fact all examples shown here are bad practice. Use binding parameters to create your query using mysqli or PDO.
$search = "%". "your_search_term" . "%";
$sth = $dbh->prepare('SELECT *
FROM example_table
WHERE name like :search');
$sth->bindParam(':search', $search, PDO::PARAM_STR, 12);
$sth->execute();
It has been issue with select query.You have to change select query only.
select * from tbl_pets where name like "%'".$data."'%"
and real mysql query
select * from tbl_pets where name like "%red%"
You can take a look here.I have created schema from that you can also check.