1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. Good table manners.

Good table manners.

5 years 5 months ago #308522 by RebeccaWool
Good table manners. was created by RebeccaWool
Hello,

Having read a number of topics were it appears that one should use a 'self created' table rather than add hundreds of new columns to the 'com profiler' table, I have created a new table called 'course_dates'.

This contains:

id - primary, self incrementing
user_id - unique, same as 'comprrofiler; table
fullname - concat of first & last name from 'comprofiler'
area_code - from a custom field on the 'comprofiler' table.
then dates columns.....

The problem I have is that the following query throws an error:
INSERT INTO course_dates
SET area_code =
(SELECT t1.cb_area_code
FROM traincomprofiler as t1, traincomprofiler AS t2 
WHERE t1.cb_role = 'services'
AND t1.cb_service_name = t2.cb_service_name
AND t2.user_id = [user_id] ) ;

Error is:

Duplicate entry '0' for key 'user_id'

No matter what I try, I get different errors.
I have tried:
UPDATE instead of INSERT INTO - This results in updating EVERYONES record with the same data.

Can you please help, it would be greatly appreciated.
Also, why is adding lots of new columns to the comprofiler table a bad idea? I do need a separate table for course_dates so that I can run a SELECT * query later.

Many thanks for your patience and help.

Cheers.
Life is a constant learning curve and I've just started out!

Please Log in to join the conversation.

5 years 5 months ago #308540 by krileon
Replied by krileon on topic Good table manners.
You don't need to try to store field values in an external table. Simply create the field within CB > Field Management and it's fine. The _comprofiler database table can hold roughly 1,000 fields depending on database configuration, but realistically you shouldn't need more than 100-200.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
The following user(s) said Thank You: RebeccaWool

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. Good table manners.
Time to create page: 0.198 seconds

Search

Login / Logout

User Menu