MySQL - 名称包含特定单词的产品名称[复制]

Question

This question already has an answer here:

• MySQL Like multiple values 8 answers

I have a MySQL table products(id, name, sku) and I have an array of strings: $words = ['1' => 'Carlsberg', '2' => 'Premium', '3' => '250ml'];

How to get from database names of products where product name includes one, two or all words of $words array?

I need some help with writing a sql query to get these products from the database

</div>

Answer 1

You can use 'LIKE' or 'REGEXP'. Regexp example shown below:

$words = ['1' => 'Carlsberg', '2' => 'Premium', '3' => '250ml'];
$search = implode("|", $words);
$query = "SELECT id,name,sku FROM products WHERE name REGEXP %{$words}%";

.

mysql> select * from products;
+------+------------------------+-------------+
| id   | name                   | sku         |
+------+------------------------+-------------+
|    1 | Carlsberg              | 1094802341  |
|    2 | Premium Beer 999 300ml | 1093i103    |
|    3 | Heineken 250ml         | 10901948901 |
|    4 | Test Product           | 14141414    |
+------+------------------------+-------------+
4 rows in set (0.00 sec)

mysql> select * from products WHERE name REGEXP 'Carlsberg|Premium|250ml';   

+------+------------------------+-------------+
| id   | name                   | sku         |
+------+------------------------+-------------+
|    1 | Carlsberg              | 1094802341  |
|    2 | Premium Beer 999 300ml | 1093i103    |
|    3 | Heineken 250ml         | 10901948901 |
+------+------------------------+-------------+
3 rows in set (0.00 sec)**

Please make sure you use PDO and you sanitize the user inputs first.

Reference:

MySQL Like multiple values

https://www.w3schools.com/sql/sql_like.asp