I have these mysql tables. I need to display on single form fields to I can add data to multiple tables.
To add data to both request_type and requests_request_type_services table.
How to display a multiselect field on the single form thus If I chose “Ticketing” to be displayed “Buses” and/or “Airplanes” one or more options. Those options to be reflected/saved into requests_request_type_services
-- Table: request_type
CREATE TABLE request_type (
id_request_type INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `request_type` (`id_request_type`, `name`) VALUES
(3, 'Parcels'),
(5, 'Car Rentals'),
(1, 'Ticketing'),
(4, 'Platform Transport'),
(2, 'Tourism');
-- Table: request_type_services
CREATE TABLE `request_type_services` (
`id_request_type_services` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `request_type_services` (`name`) VALUES
('Buses'),
('Airplanes'),
('Accommodation'),
('Transfer'),
('Vacations'),
('Others');
-- Table: request_type_services_mapping (Mapping Table)
CREATE TABLE request_type_services_mapping (
id_request_type INT NOT NULL,
id_request_type_services INT NOT NULL,
PRIMARY KEY (id_request_type, id_request_type_services),
FOREIGN KEY (id_request_type) REFERENCES request_type(id_request_type) ON DELETE CASCADE,
FOREIGN KEY (id_request_type_services) REFERENCES request_type_services(id_request_type_services) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO request_type_services_mapping (id_request_type, id_request_type_services) VALUES
((SELECT id_request_type FROM request_type WHERE name='Ticketing'), (SELECT id_request_type_services FROM request_type_services WHERE name='Buses')),
((SELECT id_request_type FROM request_type WHERE name='Ticketing'), (SELECT id_request_type_services FROM request_type_services WHERE name='Airplanes')),
((SELECT id_request_type FROM request_type WHERE name='Tourism'), (SELECT id_request_type_services FROM request_type_services WHERE name='Accommodation')),
((SELECT id_request_type FROM request_type WHERE name='Tourism'), (SELECT id_request_type_services FROM request_type_services WHERE name='Vacations'));
-- Table: requests_request_type_services (Many-to-Many)
CREATE TABLE requests_request_type_services (
id_request INT NOT NULL,
id_request_type_services INT NOT NULL,
PRIMARY KEY (id_request, id_request_type_services),
FOREIGN KEY (id_request) REFERENCES requests(id_request) ON DELETE CASCADE,
FOREIGN KEY (id_request_type_services) REFERENCES request_type_services(id_request_type_services) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;