To answer the immediate question: yes, the regular expression approach should work for your described scenario.
With that said, I do not recommend going that route, because the general design it's based on is not very good. You will likely run into issues with different queries (like "give me the games matching all of these tags"), and sanitizing a tag list that is kept as a single comma-separated string may cause additional complications.
A better approach would be normalizing the database table and keeping the tag list in your PHP application as an array that you only merge into a string when you actually run a database query.
A normalized table structure would look like this:
Table games:
| id | title |
+----+--------------------+
| 1 | Neverwinter Nights |
| 2 | Genshin Impact |
| 3 | League of Legends |
| 4 | PUBG |
Table tags:
| id | title |
+----+--------------+
| 1 | game |
| 2 | adventure |
| 3 | role-playing |
| 4 | turn-based |
| 5 | moba |
| 6 | esports |
| 7 | pc |
Table mapping:
| gameid | tagid |
+--------+-------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
| 3 | 1 |
| 3 | 5 |
| 3 | 6 |
| 3 | 7 |
| 4 | 6 |
SQL:
CREATE TABLE games (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
title varchar(255) NOT NULL
);
CREATE TABLE tags (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
title varchar(255) NOT NULL
);
CREATE TABLE mapping (
gameid int NOT NULL,
tagid int NOT NULL,
PRIMARY KEY (gameid, tagid)
);
Your base query would then map games and tags like this:
SELECT g.id
, g.title AS 'game'
, t.title AS 'tag'
FROM games g
INNER JOIN mapping m ON g.id = m.gameid
INNER JOIN tags t ON m.tagid = t.id;
which would give a result like this (the tag column is included only for demonstration purposes and will be omitted in the other examples below):
| id | game | tag |
+----+--------------------+--------------+
| 1 | Neverwinter Nights | game |
| 1 | Neverwinter Nights | adventure |
| 1 | Neverwinter Nights | role-playing |
| 2 | Genshin Impact | game |
| 2 | Genshin Impact | turn-based |
| 3 | League of Legends | game |
| 3 | League of Legends | moba |
| 3 | League of Legends | esports |
| 3 | League of Legends | pc |
| 4 | PUBG | esports |
That'll allow you to construct the selection clauses of your query whichever way you need.
For instance, fetching the games that match any of the given tags would be done with this SQL query:
SELECT g.id, g.title AS 'game'
FROM games g
INNER JOIN mapping m ON g.id = m.gameid
INNER JOIN tags t ON m.tagid = t.id
WHERE t.title IN ('esports','auto-battle');
In PHP:
$tags = Array('esports', 'auto-battle');
$query = "SELECT g.id, g.title AS 'game'" .
" FROM games g INNER JOIN mapping m ON g.id = m.gameid INNER JOIN tags t ON m.tagid = t.id" .
" WHERE t.title IN ('" . implode("','", $tags) . "');";
Whereas fetching the games that match all of the given tags would be done with an SQL query like this:
SELECT g.id, g.title AS 'game'
FROM games g
INNER JOIN mapping m ON g.id = m.gameid
INNER JOIN tags t ON m.tagid = t.id
GROUP BY g.title
HAVING COUNT(CASE WHEN t.title IN ('esports','auto-battle') THEN 1 END) = 2;
In PHP:
$tags = Array('esports', 'auto-battle');
$query = "SELECT g.id, g.title AS 'game'" .
" FROM games g INNER JOIN mapping m ON g.id = m.gameid INNER JOIN tags t ON m.tagid = t.id" .
" GROUP BY t.title HAVING COUNT(CASE WHEN t.title IN ('" . implode("','", $tags) . "') THEN 1 END) = " . count($tags) . ";";
Note that all of these examples assume that $tags contains an array of unique, properly sanitized strings. That is something you must ensure first.
Also note that when imploding the array you must do it in a way that adds quotes around the strings (as in my examples). Never use bare strings in SQL queries.
