Make MySQL full text search to use AND

Question

I've tried for ages to make this work the way I want, but I keep failing.

I have a table with 4 columns. The second one ("itemname") is the one that should be searched. The values contain special characters like "/" and "(".

The working part: Special characters are being ignored, the right entry gets matched.

The non-working part: It's between tons of other matches, that I dont need.

My query:

$query = $pdo->prepare("SELECT itemname, itemprice, itemupdate, itemstat
         FROM pricedata 
         WHERE MATCH itemname AGAINST ('%" . $search . "%' IN BOOLEAN MODE)
         ORDER BY itemprice DESC LIMIT 15");

Example: I need to find an item with the value "M4A1-S / Hyper Beast (Factory New)".

---

Search for: "M4A1-S Hyper"

---

1. "M4A1-S / Hyper Beast (Factory New)"
   • (RIGHT / both words match)
2. "M4A1-S / Golden Coil (Factory New)"
   • (WRONG / only one word matches)
3. "AWP / Hyper Beast (Factory New)"
   • (WRONG / only one word matches)

---

Basically: Every word in the search bar should be treated with an "AND" and not an "OR".

I tried to find the solution myself, but I just can't make it work.

Thanks a lot in advance! :)

EDIT: New suggested query gives no results:

$query = $pdo->prepare("SELECT itemname, itemprice, itemupdate, itemstat  
FROM pricedata  
WHERE itemname REGEXP '%" . $search . "%'  
ORDER BY itemprice DESC LIMIT 15");

Answer 1

I think you can use Regular expression

try this site to understand regex

'M4A1-S.+Hyper' - matches two words 'M4A1-S' being first word 'Hyper' being the second

$query = $pdo->prepare("SELECT itemname, itemprice, itemupdate, itemstat
         FROM pricedata 
         WHERE itemname REGEXP 'M4A1-S.+Hyper'
         ORDER BY itemprice DESC LIMIT 15");

source: check this