omiting non-like results

[wikit]

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.

[rproffitt]

http://www.w3schools.com/sql/sql_like.asp writes:

Tip: The "%" sign is used to define wildcards (missing letters) both before and after the pattern. You will learn more about wildcards in the next chapter.

So maybe you don't need the % signs.

[AndrisP]

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[[:>:]]';

[diafol]

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);
$s->execute(["[[:<:]]$searchWord[[:>:]]"]);

[drjohn]

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