Display in custom field values depending of previous selection

PHPMaker General Discussion (PHPMaker)
1

I have a table named “request”.
I have this field “request_type_id” with values: “Ticketing” and “Tourism”
When user chose for example “Ticketing” from drop-down field I want to be displayed in a custom field named “services”
multiple services but only those that are specific to “Ticketing”. Currently are displayed all values in the form generated by phpmaker.
I have tables bellow.

Table request_types
id name
1 Ticketing
2 Tourism

Table
request_services
id name
1 Coach (bus)
2 Flight
3 Accommodation
4 Airport Transfer
5 Vacation
6 Other

table resuest_type_service_type

request_type_id request_service_id
1 1
1 2
2 2

Could you tell how to achive to display in the add form only values from “request_services” specific to selected value of “request_types”

2

You may use Dynamic Selection List.

3

I didn’t get it work, maybe it needs different approach, as I use a intermediary table “request_type_services_type”

If I run this query it give me what I need:

SELECT s.id_request_type_services, s.name
FROM request_type_services s
JOIN request_type_services_type st ON s.id_request_type_services = st.id_request_type_services
JOIN request_type t ON st.id_request_type = t.id_request_type
WHERE t.name = 'Ticketing';

image
image1440×341 25 KB

See table schema bellow

image
image736×391 16.2 KB

CREATE TABLE `request` (
  `id_request` int(11) NOT NULL,
  `id_request_type` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `request_type`
--

CREATE TABLE `request_type` (
  `id_request_type` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_romanian_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `request_type`
--

INSERT INTO `request_type` (`id_request_type`, `name`) VALUES
(1, 'Ticketing'),
(2, 'Tourism'),
(3, 'Parcels'),
(4, 'Transport'),
(5, 'Car rental'),
(6, 'Other');

-- --------------------------------------------------------

--
-- Table structure for table `request_type_services`
--

CREATE TABLE `request_type_services` (
  `id_request_type_services` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `request_type_services`
--

INSERT INTO `request_type_services` (`id_request_type_services`, `name`) VALUES
(1, 'Coach - bus'),
(2, 'Flight'),
(3, 'Accommodation'),
(4, 'Transfer'),
(5, 'Holidays'),
(6, 'Other');

-- --------------------------------------------------------

--
-- Table structure for table `request_type_services_type`
--

CREATE TABLE `request_type_services_type` (
  `id_request_type` int(11) NOT NULL,
  `id_request_type_services` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `request_type_services_type`
--

INSERT INTO `request_type_services_type` (`id_request_type`, `id_request_type_services`) VALUES
(1, 1),
(1, 2),
(2, 3),
(2, 4),
(2, 5),
(3, 6),
(4, 4),
(5, 6);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `request`
--
ALTER TABLE `request`
  ADD PRIMARY KEY (`id_request`),
  ADD KEY `fk_request_type` (`id_request_type`);

--
-- Indexes for table `request_type`
--
ALTER TABLE `request_type`
  ADD PRIMARY KEY (`id_request_type`);

--
-- Indexes for table `request_type_services`
--
ALTER TABLE `request_type_services`
  ADD PRIMARY KEY (`id_request_type_services`);

--
-- Indexes for table `request_type_services_type`
--
ALTER TABLE `request_type_services_type`
  ADD PRIMARY KEY (`id_request_type`,`id_request_type_services`),
  ADD KEY `fk_request_type_services` (`id_request_type_services`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `request`
--
ALTER TABLE `request`
  ADD CONSTRAINT `fk_request_type` FOREIGN KEY (`id_request_type`) REFERENCES `request_type` (`id_request_type`);

--
-- Constraints for table `request_type_services_type`
--
ALTER TABLE `request_type_services_type`
  ADD CONSTRAINT `fk_request_type_services` FOREIGN KEY (`id_request_type_services`) REFERENCES `request_type_services` (`id_request_type_services`),
  ADD CONSTRAINT `fk_rst_request_type` FOREIGN KEY (`id_request_type`) REFERENCES `request_type` (`id_request_type`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
4

You may create a view to join your request_type_services and request_type_services_type first, then use the view as the lookup table for your second selection list.

5

@arbei You mean custom view or database view?
There are also other options to achieve this maybe using javascript/ajax, internal API or other means?

Thank you!

6

ALWAYS use database view. I believe it is the simplest method for your case. No customizations are required.