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()
id_photos
+-----------+------------------------------------------+------+-----+---------+----------------+
| 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 | |
+-----------+------------------------------------------+------+-----+---------+----------------+
INSERT INTO cars(id_photos) values ('id_1st_photo', 'id_2nd_photo')
| 60 | Audi | Yellow | diesel | 252352 | 1234112 | R8 | 1990 | id_1st_photo id_2nd_photo |
/var/www/website/$login/photos/id_of_photo
select id_photos from cars where carid=$id
data types
data types
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.