Taking image from Mysql

Question

I’m making a website in PHP and MySQL to store the products and their image. On the products page there are 5 or more models and I need to pick up 5 or more images of each product. I don’t know if I create another table and put the images on it or use the same table. What alternative do you suggest?

Answer 1

Create two tables for easy, one for images and the other for products and you can associate the image id with the product id.

Here I will put just one example,you do not need to take as a basis,it is simply 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 that, 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 Internet you put if you want where to make a condition for these associations.

Answer 2

I would use two tables. A table containing 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 just need to fetch this data in the image table by product id. You can use an INNER JOIN if you like (that’s 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 image name + its extension in the database and creating a folder just for its path. Ah, it would be good also not to leave the original name of the image, usually when I use upload files with PHP, I give a base64_encode() in the name of the file and ask the first 32 characters, so in the searches it is easier and more organized (in my opinion).

Useful links:

• Joins
• Table relationship in Mysql
• Inner Join SQL - W3schools