I have a working search database and it's query code says

$criteria = $_POST['criteria'];
$query = "SELECT * FROM table WHERE field LIKE '%".$criteria."%' Order by Appeared asc";

but I want it to omit results that contain the word but are more then the word
example the criteria might be "sun" but there may be a field that says "sunshine" I want it to only fetch the results that say "sun" and not the ones that also contain that word.

How can I fix this query to work. I tried WHERE field='%".$criteria."%' but it told me 0 results found when I know there are fields with the word "sun" in them.

1 Year
Discussion Span
Last Post by drjohn

You can use RLIKE instead:

SELECT 'sun' RLIKE '^sun[^shine]*$';
SELECT 'sunshine' RLIKE '^sun[^shine]*$';

or use word boundaries:

SELECT 'long text contain sun and other words' RLIKE '[[:<:]]sun[[:>:]]';
SELECT 'long text contain sunshine and other words' RLIKE '[[:<:]]sun[[:>:]]';

Edited by AndrisP

Votes + Comments
Good stuff on RLIKE :)

We have an SQL injection alert. Never place unsanitized user input directly into an SQL statement. I'm assuming you are using mysqli or PDO and not the deprecated and now dead (PHP7) mysql_* functions.

If in doubt see DW Tutorial: Common Issues with MySQL and PHP.

$sql = "SELECT `field` FROM `table` WHERE `somefield` RLIKE ? ORDER BY `Appeared` ASC";
$s = $pdo->prepare($sql);

Edited by diafol


You could try inserting a space before and after the $criteria, so sun would be returned but not sunshine.
WHERE field LIKE ' %".$criteria."% ' Order by Appeared asc"
Basically you are discovering that using like in a search is a bit tricky to get perfect results .
If the above code works, it would still have a problem if sun was the first word or only word, so no spaces before or after.
So overall, using RLIKE is possibly a better choice

some examples here http://www.sqlexamples.info/PHP/mysql_rlike.htm

Edited by drjohn

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.