-3

PHP MySQL search word if %100 equals to word I searched "bat" and I saw "batman" in results. I just want to list if title contains "bat", I dont want to list if contains "batman"

I'm tried many way. I can't solve this problem. How can be solve this problem? I can't understand why result like that. This code in the below. Thanks for your advice from now.

$sql = "SELECT * FROM `tablename` WHERE `title` LIKE '%bat%' ORDER BY RAND() LIMIT 30;";
Berk
  • 55
  • 1
  • 9
  • `%` symbol means that any characters can be placed instead of it. Searching for `%bat%` means _find all strings that contain `bat` in them_. – lolbas Mar 13 '18 at 07:50
  • 2
    $sql = "SELECT * FROM `tablename` WHERE `title` = 'bat' ORDER BY RAND() LIMIT 30;"; If you want the exact result, don't use like. – user3551009 Mar 13 '18 at 07:50
  • If you only want title equat to "bat" why you don't do `WHERE title = bat` ? – Mickaël Leger Mar 13 '18 at 07:51
  • You don't need to use like here. you can simple use equal to(title = bat) in your query. – Gajjar Parth Mar 13 '18 at 07:51
  • @MickaelLeger he did not say `equal to "bat"` but contains `bat`. – lolbas Mar 13 '18 at 07:52
  • You need to use a Regular Expression, `'\bcat\b'`, I don't know if MySQL's REGEXP supports that syntax. – dnoeth Mar 13 '18 at 07:55
  • "_I just want to list if title contains "bat", I dont want to list if contains "batman"_" but ... "batman" *does* contain the word "bat"!?! – brombeer Mar 13 '18 at 07:57
  • @Berk : I'm not sure to understand, there is a difference between "contain" and "equal 100%", if you want "contain" why `batman` is not good? You want only `bat` or `**bat**`, `bat***`, `***bat`? – Mickaël Leger Mar 13 '18 at 07:57
  • 2
    The OP appears to want 'Year of the bat' and 'The bat sat on a cat' but not 'Battleships'. That said, I agree that the OP has not been clear in this respect – Strawberry Mar 13 '18 at 07:58
  • @Strawberry ok, that's clear now ! He should edit is question because it's confusing :) – Mickaël Leger Mar 13 '18 at 07:59
  • I show example with one word. But I use multiple words if I do "WHERE title = bat night" don't work. If I do this "WHERE `title` LIKE '%bat%night%'" work but in results available "batman" :( – Berk Mar 13 '18 at 08:00
  • Please edit your question. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Mar 13 '18 at 08:46

2 Answers2

1

If you only want title equal to the WORD "bat" :

$sql = "SELECT * FROM `tablename` WHERE `title` = 'bat' ORDER BY RAND() LIMIT 30;";

Now if you want title that contain the word "bat" but not the words that contain it, you can try this :

$sql = "SELECT * FROM `tablename` 
        WHERE `title` LIKE '% bat %' 
        ORDER BY RAND() LIMIT 30;";

or using REGEXP

$sql = "SELECT * FROM `tablename` 
        WHERE `title` RLIKE '[[:<:]]bat[[:>:]]' 
        ORDER BY RAND() LIMIT 30;";

But in this case, it's a duplicate of this question : MySQL - How to search for exact word match using LIKE?

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
1

The easiest way is to use a Regular Expression to match the exact word bat:

where `title` REGEXP '[[:<:]]bat[[:>:]]'

[[:<:]] and [[:>:]] are word boundaries, bla bat, bla passes, batman and abatwill not pass

dnoeth
  • 59,503
  • 4
  • 39
  • 56