How do I insert multiple dates in a mysql table?
Sometimes we need insert multiple booking date in the database behalf of the same add_id
I have a form to book ads for multiple dates, which are selected by using a calendar in the form and then posted in JSON format. An example would be:
[20200915, [20200918, 20200925], 20200926, 20200927]
but I am having a problem storing this json string for each ad.
- How would you recommend storing these dates with relation to the ad?
- How to select an ad using one of its dates.
I don’t want to store each date separately in different tables.
The first way to insert multiple dates to a row in MySQL
How would you recommend storing these dates in relation to the ad?
You should store them in a relationship table, which will have the id of the ad and the date of the booking.
ad_bookings
Table Field
ad_id | booking_date
The PRIMARY KEY would be the compound (ad_id, booking_date).
Example data would be:
d_id | booking_date
---------------------------
1 | 2011-09-15
1 | 2011-09-18
1 | 2011-09-19
1 | 2011-09-20
1 | 2011-09-26
2 | 2011-09-27
2 | 2011-09-30
How to select an ad using one of its dates.
SELECT * FROM ads a INNER JOIN ad_bookings ab ON a.ad_id = ab.ad_id AND ab.booking_date = '2011-09-30'
