HOW TO: List Options
9 posts by 3 authors in: Forums > CMS Builder
Last Post: Yesterday at 9:17pm (RSS)
By richb - Sunday at 5:45pm
I have a table cmsb_member with num (record number), lastname and firstname. I have cmsb_tournament with member_id, member (as a list with advance SQL selecting cmsb_member lastname + firstname). How can I update cmsb->tournament->member_id with cmsb_member->num once the list option is chosen?
Ex. cmsb_member 10 (num), Smith (lastname), Greg (firstname)
cmsb_tournament create
cmsb_tournament member (list shows "Smith, Greg") to choose from. Once saved, I need cmsb_tournament->member_id = cmsb_member->num (10).
Hope this makes sense.
Richb.
By Tim - Monday at 5:33pm
Hi Rich,
Typically, you wouldn't store member_id as a separate field in the cmsb_tournament table. Instead, you should set up a foreign key (or related record) that links directly to the num field in the cmsb_member table. This approach avoids the issue of having to update two separate fields simultaneously (the member field and the member_id field in cmsb_tournament), which could easily lead to inconsistencies.
By establishing this foreign key relationship, any query on cmsb_tournament can use the link to cmsb_member to retrieve the member's details, such as first and last name, based on the ID stored in cmsb_tournament.member. This ensures that all member-related information is maintained in a single location (the cmsb_member table) and remains consistent.
Have you considered using a foreign key or related record field to reference the member in cmsb_member? :)
Senior Web Programmer
Interactivetools.com
By Dave - Yesterday at 9:25am
Hi Rich,
Some more thoughts on this.
Do you have two fields in cmsb_tournament? And if so, can you just use one `member_id` with the num as the value and the name as the label?
What's the end goal? What are you trying to accomplish?
interactivetools.com
By richb - Yesterday at 3:45pm
Hi Dave. The Foreign key work perfectly. While I'm using the ID as the key, I can use SQL to create the text field to display (ie. 'firstame,lastname').
thank you
Richb.
By richb - Yesterday at 5:12pm
Hi Dave. I've been adding foreign key to my table, where I need about 150 keys. While adding I received the following error.
"Error adding foreign key constraints"
"Last SQL query (visible to admin only)
Alter table 'cmsb_paring' ADD FOREIGN KEY
('group19_player4) REFERENCES 'cmsb_member' ('num') ON
DELETE RESTRICT ON UPDATE RESTRICT'
"
I've been using all the defaults and this occured on the 64th record.
By richb - Yesterday at 6:26pm
To give you more context. I have 144 golfer members. I'm creating golf parings of 4 in each group. I'm using a Foreign key to reference the golf member vs duplicating their information. The Foreign key works great, but I received an error when trying to add the 64th member. I'm thinking there is a DB limit, however, I'm reading there is 253 limit on MSQL tables. Not sure what.
By Dave - Yesterday at 7:08pm
Hi Rich,
Can you send in a 2nd level support request? We can take a look for you.
https://interactivetools.com/support/request/
Thanks!
interactivetools.com
By Dave - Yesterday at 8:58pm
Hi RichB,
I added some debug code to your install (and the next CMSB version) to show the error, and it is as you guessed:
MySQL Error(1069): Too many keys specified; max 64 keys allowed
As an alternative, I set up paring.group16_player4 as follows:
- Field Type: list
- Display As: pulldown
- List Options: Get options from MySQL query (advanced)
- Query: (your MySQL query)
This should work the same way, but it doesn't strictly enforce database integrity rules like foreign keys. But it is a common way that people set things up.
Another option would be to table for paring_groups with one group per record and then display it with the "Related Records" field type. However, that version will require an extra click each time you want to add a record and a bit more MySQL on the front end, as you'd need to query multiple tables. CMSB is quite flexible, so there are often multiple correct ways to do things. Starting with the simplest, easiest approach is often a good strategy.
Hope that helps! Let me know any questions.
interactivetools.com