Mysql Question posted in
The official MySQL documentation can be found here.

MySQL how to have X amount of data in one record

Lets say i have products table
It have regular things but it also have image or collection field
Good if product have only one image or collection
But product could have multiple images like img1 img2 img3 etc
How can i keep it simple to have one filed for multiple images
Like img filed and to have img1 img2 img3?

I heard there is a way to have 2 tables one for main table and 2nd for things like this but i dont know how to set it up

Tags:

2

Answers


  1. 0 
    products table
id | ...
 1 | ...
 2 | ...

    product_images table
id | product_id | ...
 1 |     1      | 'url1_1' ...
 2 |     1      | 'url1_2' ...
 3 |     2      | 'url2_1' ...
 4 |     2      | 'url2_2' ...

    You can use two queries:

    SELECT * FROM products

    Then:

    SELECT * FROM product_images WHERE product_id IN (1, 2)

    Where the 1, 2 are the ids from the first query. You would match up the results of this query to the results of the first query matching on product_id.

    There’s also a way of querying both tables at once with a LEFT JOIN. Many database access libraries have support for using one or two queries (or even choosing which way you want to do it).

    Login or Signup to reply.
  2. 0

    You need a join table to associate a product with its images. MySQL is a "relational database" and these are the relationships.

    Let’s say we have products and images.

    create table products (
  id integer primary key,
  name varchar(255) not null
);

create table images (
  id integer primary key,
  -- avoid storing images in the database, unless they're very small
  -- instead, store a path to fetch the image
  path varchar(255) not null
);

insert into products(id, name) values
  (1, 'The Stuff'), (2, 'Nick Nacks'), (3, 'Chancy Feast');

insert into images(id, path) values
  (1, '/path/to/img.png'), (2, '/path/to/other.png'), (3, '/path/to/this.png');

    You need a join table to relate the product with its images (and vice versa).

    create table product_images (
  product_id integer not null,
  foreign key(product_id)
    references products(id)
    on delete cascade,

  image_id integer not null,
  foreign key(image_id)
    references images(id)
    on delete cascade,

  -- duplicates not allowed
  primary key(product_id, image_id)
);

    This is called a many-to-many relationship. A product can have many images, and the same image can be used by many products (or anything else). The IDs are declared as foreign keys so the database can confirm the referenced products and images exist, this is "referential integrity". on delete cascade says that when the referenced product or image is deleted, also delete its rows in product_images.

    insert into product_images(product_id, image_id) values
  (1, 1), (1, 3),
  (3, 2), (3, 3);

    Product 1 uses images 1 and 3. Product 3 uses images 2 and 3. Product 2 has no images.

    Then we can match a product with its images using join.

    select p.*, i.path
from products p
join product_images pi on pi.product_id = p.id
join images i on pi.image_id = i.id

    If we want to see all products, even those without an image, use left join.

    select p.*, i.path
from products p
left join product_images pi on pi.product_id = p.id
left join images i on pi.image_id = i.id

    The "left" saying to return all rows from the "left" table. The "left" table being the one in the from line: products. See Visual Representation Of SQL Joins for a great explanation of various types of joins.

    Demonstration.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search