This topic is locked
[SOLVED]

 Multiselect Checkboxes for Many-Many Relationship

12/24/2019 12:35:21 AM
PHPRunner General questions
D
druck281 author

Hello all...
I am working on a database to catalog serious injury or fatal vehicle crashes. My goal is to be able to easily find similar crashes to use as a guide for future investigations. For example, if we are investigating a fatal crash involving a motorcycle, I want to search the database for other crashes in which a motorcycle was involved.
I have one table for basic data about the crash



Crash

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

| Crash_ID | Date | Location | Units | Severity |

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

| 1 | 01/01/2019 | 123 Main St | 2 | Fatal |

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

| 2 | 02/02/2019 | 456 Front St | 4 | Serious Injury |

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

| 3 | 03/03/2019 | 789 First St | 1 | Fatal |

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


I have another table containing "flags" or special circumstances



Flags

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

| Flag_ID | FlagName |

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

| 1 | Pedestrian |

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

| 2 | Roll-Over |

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

| 3 | Motorcycle |

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

| 4 | Airborne |

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

| 5 | Bicycle |

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


These two tables have a many-many relationship. I created a third table to store the keys relating to each side of the relationship.



CrashFlags

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

| CrashID | FlagID |

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

| 1 | 1 |

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

| 1 | 3 |

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

| 1 | 5 |

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

| 2 | 4 |

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

| 2 | 5 |

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

| 3 | 1 |

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

| 3 | 2 |

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

| 3 | 3 |

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


On the list page for the Crash table, I want a button on each crash record that will display a pop-up containing check boxes for all of the flags in the Flags table. I can then check off any flags that apply to the selected crash. I don't know how to make PHP runner display all of the flags and not just the ones already associated with the selected crash in the "CrashFlags" table. I feel like I should be basing a lookup on a Union query or something along that line.
Ideas?

Thank you!

HJB 12/24/2019



Hello all...
I am working on a database to catalog serious injury or fatal vehicle crashes. My goal is to be able to easily find similar crashes to use as a guide for future investigations. For example, if we are investigating a fatal crash involving a motorcycle, I want to search the database for other crashes in which a motorcycle was involved.
I have one table for basic data about the crash



Crash

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

| Crash_ID | Date | Location | Units | Severity |

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

| 1 | 01/01/2019 | 123 Main St | 2 | Fatal |

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

| 2 | 02/02/2019 | 456 Front St | 4 | Serious Injury |

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

| 3 | 03/03/2019 | 789 First St | 1 | Fatal |

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


I have another table containing "flags" or special circumstances



Flags

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

| Flag_ID | FlagName |

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

| 1 | Pedestrian |

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

| 2 | Roll-Over |

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

| 3 | Motorcycle |

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

| 4 | Airborne |

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

| 5 | Bicycle |

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


These two tables have a many-many relationship. I created a third table to store the keys relating to each side of the relationship.



CrashFlags

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

| CrashID | FlagID |

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

| 1 | 1 |

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

| 1 | 3 |

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

| 1 | 5 |

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

| 2 | 4 |

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

| 2 | 5 |

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

| 3 | 1 |

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

| 3 | 2 |

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

| 3 | 3 |

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


On the list page for the Crash table, I want a button on each crash record that will display a pop-up containing check boxes for all of the flags in the Flags table. I can then check off any flags that apply to the selected crash. I don't know how to make PHP runner display all of the flags and not just the ones already associated with the selected crash in the "CrashFlags" table. I feel like I should be basing a lookup on a Union query or something along that line.
Ideas?

Thank you!


Think, a MASTER/DETAIL oriented database design is what is fitting the needs. For example, MASTER table to contain the "Flagnames" while

DETAILS table is holding the rest of the data. As PHPRunner provides SEARCH and ADVANCED SEARCH by default, finding of what you need is

not a matter to brain about at all. Once CHECKBOXES are used in a table, you shall see ALL of them, both, the checked and the unchecked

ones anyway.
https://xlinesoft.com/phprunner/docs/master-details.htm

D
druck281 author 12/25/2019



Think, a MASTER/DETAIL oriented database design is what is fitting the needs. For example, MASTER table to contain the "Flagnames" while

DETAILS table is holding the rest of the data. As PHPRunner provides SEARCH and ADVANCED SEARCH by default, finding of what you need is

not a matter to brain about at all. Once CHECKBOXES are used in a table, you shall see ALL of them, both, the checked and the unchecked

ones anyway.
https://xlinesoft.com/phprunner/docs/master-details.htm



What I'm aiming to do is very similar to the standard example of the "Order Details" sample. Each order contains multiple products and each product can belong to multiple orders so they create the table in the middle for "Order Details". The biggest difference is that on the "Orders" page, I want check boxes for each product so I can check off which ones belong to the order. I tried creating a Master/Detail between "Crashes" and "CrashFlags" but what I end up with is one record in CrashFlags that is trying to hold multiple FlagIDs and it errors out saying "FlagID cannot be null".
I hope my explanation makes sense. Just trying to relate it to a more standard example.

Sergey Kornilov admin 12/25/2019

If you are looking to implement master-details relationship check this article first:

https://xlinesoft.com/blog/2011/02/28/how-to-setup-many-to-many-relationships/
Basaically many-to-many relationship is a set of two master-details relationships.

HJB 12/26/2019



If you are looking to implement master-details relationship check this article first:

https://xlinesoft.com/blog/2011/02/28/how-to-setup-many-to-many-relationships/
Basaically many-to-many relationship is a set of two master-details relationships.


https://imgur.com/a/Vf0SYdI - IMGUR uploaded screenshots of 1st draft based ADD and LIST page dummy records
For inspiration purposes. Sure, your wished MANY-TO-MANY web app design would finally work,

while my personal focus is USABILITY and ERGONOMICS in first instance, here on DATA COLLECTION

and CLASSIFICATIONS, especially in times where it more and more happens that SMARTPHONES are used

for same..., so the screen content should be tiny, with built-in links (calendar, add new functionality).
Thanks to BOOTSTRAP technology inside PHPR, optimal display on smartphones/tablets does not need manual coding.
Since lots of Search and Report Features inside PHPR v10.3 are shipped by default, at least me is seeing no

problem to get any client satisfied on fact finding missions or reports results to be seen, either of cumulative

or cross-tab shape.
If MANY-TO-MANY is definitely a MUST have, a MASTER / DETAIL design can be added to the above dummy design anyway.

HJB 12/26/2019



If you are looking to implement master-details relationship check this article first:

https://xlinesoft.com/blog/2011/02/28/how-to-setup-many-to-many-relationships/
Basaically many-to-many relationship is a set of two master-details relationships.


@admin - Many thanks for your input by means of a 2011 created GUIDELINE provision.

Now, since 2020 is knocking at the door, say, 9 years after on "Many-to-Many" issues

seen by today, it truly reflects the "early bird" status the XLINESOFT developer team

owns on things like that. At least me is 100% sure off..., v10.4 is most likely doing

the same, say, years after, users and clients are going to realize, it made true sense.

D
druck281 author 12/31/2019



https://imgur.com/a/Vf0SYdI - IMGUR uploaded screenshots of 1st draft based ADD and LIST page dummy records
For inspiration purposes. Sure, your wished MANY-TO-MANY web app design would finally work,

while my personal focus is USABILITY and ERGONOMICS in first instance, here on DATA COLLECTION

and CLASSIFICATIONS, especially in times where it more and more happens that SMARTPHONES are used

for same..., so the screen content should be tiny, with built-in links (calendar, add new functionality).
Thanks to BOOTSTRAP technology inside PHPR, optimal display on smartphones/tablets does not need manual coding.
Since lots of Search and Report Features inside PHPR v10.3 are shipped by default, at least me is seeing no

problem to get any client satisfied on fact finding missions or reports results to be seen, either of cumulative

or cross-tab shape.
If MANY-TO-MANY is definitely a MUST have, a MASTER / DETAIL design can be added to the above dummy design anyway.


@Walk2fly What you have in the images is perfect. It looks like that is done using the Master-Detail instead of Many-Many. I must be missing something because I still can't get a multi-select checkbox field to work. I'll keep playing around. At least I know it's possible using that method.
Thanks!

HJB 12/31/2019

@druck281 - It's a SINGLE page rather than a Many-to-Many issue while however, if wished, a Many-to-Many ADD-ON via Master/Detail could be attached, if needed. Give me a PM via this forum and I shall send you my PHPR v10.3 project file along with the SQL Dump file to let you import the database structure, all FREE OF CHARGE, for your further studies and development of your project as I'm currently on another 2020 course of projects where design & production of web appliances are no longer mattering at all, say, I shall drastically reduce my forum presence in 2020 anyway as it came to light that web appliance development can be no longer a "bread and butter" business for me at all. HTH and "Happy New Year".
P.S. https://imgur.com/1OZhn0B - 31 KB zipped file, containing EVERYTHING made ready for deployment by E-Mail attachment to you.

HJB 1/1/2020

So far, no PM received yet.
Update 4th Jan 2020, PM received, zipped project file and database script sent by e-mail attachment by today.

D
druck281 author 1/5/2020

@walk2fly...You were right on. After your advice about using the Master-Detail relationship instead of trying to do a Many-many, I had it working on the add page but not on the edit or view pages. I looked at the Project file you sent me and found that I had the settings correct but I was still in 10.2. Once I upgraded to 10.3, it worked perfect.
Thanks again!