Getting MySQL image

I would use two tables. A table containing the product data:

CREATE TABLE produtos(
    id INT PRIMARY KEY AUTO_INCREMENT,
    ... suas colunas aqui.
);

and the other containing the images:

CREATE TABLE imagens(
    id_imagens INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL DEFAULT 'imagem.jpg',
    id_produtos INT NOT NULL,
    ... outras colunas contendo o que quiser aqui,
    CONSTRAINT dados_imagens_fk FOREIGN KEY (id_produtos)
    REFERENCES produtos(id)
);

With this, you can store as many photos as you want for each product, then only need to search for this data in the table of images by the product id. You can use an INNER JOIN if you want (this is what I usually use).

SELECT p.nome AS nome, i.nome AS nome_imagem 
FROM produtos AS p 
INNER JOIN imagens AS i 
ON p.id = i.id_produtos;

Depending on your application, I recommend storing only the name of the image + its extension in the database and create a folder just in the way of it. Oh, it would be nice to also not leave the original image name, usually when I will upload files with PHP, I give a base64_encode () in the file name and ask for the first 32 characters, so in the searches it gets easier and more organized ( in my opinion).

Useful links:

• Joins
• Relationship of tables in MySQL
• Inner Join SQL - W3Schools

Create two tables to facilitate one image and the other product and you can associate the id of the image with the id of the products.

Here, I'll just put an example, you do not need to take it as a base, it's just for you to have a notion.

First create a table to store the images:

create table images (
    image_id        tinyint(3)  not null AUTO_INCREMENT,
    image_type      varchar(25) not null ,
    image           blob        not null,
    image_size      varchar(25) not null,
    image_ctgy      varchar(25) not null,
    image_name      varchar(50) not null
);

After this, create a product table (I'll just put the product id just for example):

create table products (
products_id int not null AUTO_INCREMENT
);

After this you can associate with the products table the image id with the product id:

select image_id from images join products on images.image_id = products.products_id;

  

NOTE: In the inner join you place where to make a condition for these associations.