Having more than one value inserted into the same column

Question

I have a webapp that I'm building. This webapp will take as input some products (cars, motos, boats, houses, etc...) and each product will have one or more photos associated with it. The id of each of photo is generated by the

uniqid()

function of php.

My problem is:I can't seem to fit more than two
id_photos
into the same column

+-----------+------------------------------------------+------+-----+---------+----------------+

| Field     | Type                                     | Null | Key | Default | Extra          |

+-----------+------------------------------------------+------+-----+---------+----------------+

| carid     | int(11)                                  | NO   | MUL | NULL    | auto_increment |

| brand     | enum('Alfa Romeo','Aston Martin','Audi') | NO   |     | NULL    |                |

| color     | varchar(20)                              | NO   |     | NULL    |                |

| type      | enum('gasoline','diesel','eletric')      | YES  |     | NULL    |                |

| price     | mediumint(8) unsigned                    | YES  |     | NULL    |                |

| mileage   | mediumint(8) unsigned                    | YES  |     | NULL    |                |

| model     | text                                     | YES  |     | NULL    |                |

| year      | year(4)                                  | YES  |     | NULL    |                |

| id_photos | varchar(30)                              | YES  |     | NULL    |                |

+-----------+------------------------------------------+------+-----+---------+----------------+

What I would like to happen is something like this:

INSERT INTO cars(id_photos) values ('id_1st_photo', 'id_2nd_photo')

Ending up having something like this:

|    60 | Audi       | Yellow | diesel | 252352 | 1234112 | R8    | 1990 | id_1st_photo  id_2nd_photo     |

Eventually I would have to grab those photos from the folders they are in which is something like this:

/var/www/website/$login/photos/id_of_photo

with the query

select id_photos from cars where carid=$id

.

You may found some

data types

that is not proprelly good for the data that the server will receive but I'm one week into mysql and I'll worry about

data types

later on.

First of all I don't know if that is possible, if it's not how can I design something to work like that?

I have found this question that is quite the same of mine but I can't seem to implement something like this: add multiple values in one column

Answer 1

You can easily adapt the approach in the linked question and even remove one table needed:

You first table stays almost the same, but has the id_photos column removed:

+-----------+------------------------------------------+------+-----+---------+----------------+
| Field     | Type                                     | Null | Key | Default | Extra          |
+-----------+------------------------------------------+------+-----+---------+----------------+
| carid     | int(11)                                  | NO   | MUL | NULL    | auto_increment |
| brand     | enum('Alfa Romeo','Aston Martin','Audi') | NO   |     | NULL    |                |
| color     | varchar(20)                              | NO   |     | NULL    |                |
| type      | enum('gasoline','diesel','eletric')      | YES  |     | NULL    |                |
| price     | mediumint(8) unsigned                    | YES  |     | NULL    |                |
| mileage   | mediumint(8) unsigned                    | YES  |     | NULL    |                |
| model     | text                                     | YES  |     | NULL    |                |
| year      | year(4)                                  | YES  |     | NULL    |                |
+-----------+------------------------------------------+------+-----+---------+----------------+

Then you'll add a second table to store the links to the photo ids:

+-----------+------------------------------------------+------+-----+---------+----------------+
| Field     | Type                                     | Null | Key | Default | Extra          |
+-----------+------------------------------------------+------+-----+---------+----------------+
| carid     | int(11)                                  | NO   | MUL | NULL    |                |
| id_photos | varchar(30)                              | NO   |     | NULL    |                |
+-----------+------------------------------------------+------+-----+---------+----------------+

Both tables are linked by the field carid (You should even make carid in the second table a foreign key pointing to the one in the first table).

Each id_photos then results in a new row in the second table.

To query the data you probably need a JOIN between both tables and maybe a GROUP BY to reduce the result to one row per carid again, but this depends on the other usecases.

Having more than one value inserted into the same column

Sign up