How to organize the output from the database of data specified separated by commas?

Question

PHP

Good day.
I add categories to the database by specifying their IDs separated by commas (for example, column category = 1,3,4), there is a separate category table, where these numbers are field IDs. Since the file (categories for the file are made) can consist of several categories (from 1 to infinity), I see no reason to create columns for a separate category. There were no problems with the output on the file page (explode and you're done). But then I ran into this problem. On the page of the category itself, where files are displayed, I cannot organize the search and output of files if the file has several categories.

$cid = $_GET['id']; // допустим, что это ID той категории, что я сейчас просматриваю.
$stmt = $go -> prepare("SELECT `id` FROM `images` WHERE `category` = ? ORDER BY `id` DESC LIMIT ?, ?");
$stmt -> execute([$cid, $nav -> start(), $max]);
$images = $stmt -> fetchAll();

This code works fine if the category is 1, but not if there are more.
How can I make a query so that it outputs a file from a category like [2] if the `category` column = '1,2,3,4,5`?
I was considering the option with% LIKE%, but I decided to ask if there is a more normal way.

Answer 1

I would make a separate table, for example, images_to_category, and it has 2 columns: image_id, category_id, and I would write each image connection with the category separately in a row, i.e. if let's say the image file.jpg has id 3 and is in 3 categories with id 7, 20 and 45 then this table would have entries:
3 | 7
3 | 20
3 | 45
and when selecting by the desired category, I would use JOIN. For example, if I view a category with id 7, then the query should go to the database like this:

SELECT i.id FROM images i
JOIN images_to_category i2c ON i.id=i2c.image_id
WHERE i2c = 7;

And the result would be all the id of the images that are in the category with id 7. Something like this.
And the LIKE option has too many pitfalls, for example: you have a bunch of categories and you opened a category with id 2. The request will be LIKE% 2% and then you will get images from categories with id 20, 12, 42, 102, etc. d.

Answer 2

faced such problem

This is to be expected, because
Are you familiar with the basics of relational databases? Read at least about normalization .

Answer 3

That's why I asked such a question, because I simply don't know how else I can implement it so that the file is in several categories at the same time and is easily searched for in the database.

You have a problem with the DBMS. It does not support list operations: STRING_SPLIT (Transact-SQL) .