Search tags in mysql table with PHP

I have a table with some submissions, this table has a tags field, and I need to search in it.

The data is saved in JSON format in the table, like this: ["basic","example","html","chart"]

I’m trying to find a way to search all rows in the tags fields, but not sure how it can be done the best way when it is in this format.

The user submits an tag to search, like: html, then I need to search all rows for that tag, without to much overhead.

I know most people use to say: what have you tried yourself? – well, nothing. As I have no clue how to do this, I know how to search in sql and all that. but never tried it in this logic.

Answer

There is no “best way” to search in this format. There is no way at all.

No wonder you have no clue how to do that. I’ll tell you more – no one knows it either. Tags should never be stored in json format. It is like as if you built a car, placing wheels on the roof. And then come asking, how to drive it.

You have to learn database basics first. And then create your tables proper way. making a separate table for tags. Storing each on a separate row. After that you will be able to search a tag usual way, using JOIN query to attach the corresponding records to the result.

$sql  = "SELECT a.* FROM articles a, tags t WHERE aid=a.id AND tag=?";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($tag));
$data = $stmt->fetchAll();