Good table manners.

5 months 2 weeks ago #308522 by RebeccaWool

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.


I'm severely sight impaired (blind) and it slows me down somewhat but, I'll get there at some point, with or without your understanding.

Be careful who you mock, you might be that person some day!
5 months 1 week ago #308540 by krileon
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 - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
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 EST to 4:00 PM EST. 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
Moderators: beatnantkrileon
Time to create page: 0.375 seconds