CODEWITHSUNDEEP
phpsqlhtmlphpmyadmin
Optiq
Optiq

Reputation: 3182

How can I work around my column names needing to be longer than what's allowed in SQL?

I've encountered a bit of a situation with creating my sql database. I've been working on a Questionnaire that has tons of questions. I'm using a PHP script that automatically creates the table when a user submits the questionnaire and it uses the input names to name the columns of the table.

I figured a good way to keep everything easy for me to follow later on when I review submissions is to use the questions as the name attribute for the HTML inputs so that when I query it everything is right there and I don't have to wonder which answer goes to which question or go back and forth looking at something else.

The problem I ran into is there's a limit to how long the names of SQL columns can be. I'm not sure of exactly how long that is but I ran into an error until I shortened the input names.

So what I need to accomplish is using shorter names for the inputs so they're not too long for the sql columns, but somehow link it to another table that consists of the actual names so they can be queried later on when I need to go over a submission. Is there a workaround for this?

Upvotes: 1

Views: 365

Answers (2)

Matt Gibson
Matt Gibson

Reputation: 38238

Don't do that. The way to approach this problem is to store data as data, inside the actual columns, rather than dynamically generate tables and column names.

For example, for a user-generated questionnaire, you might have this layout:

Users table:

id name
 1 john
 2 alice
 3 fred

Questionnaire Table:

id created_by_user_id questionnaire_name
 1 1                  Animal Questionnaire
 2 3                  Tree Questionnaire
 ...

Questions Table:

id questionnaire_id question
 1 1                How many pets do you own?
 2 1                Have you ever owned a goldfish?
 3 1                Do you like cows?
 4 2                How many trees are in your back yard?
 5 2                When did you last see a silver birch?
 ...

Answers table

 id questionnaire_id question_id user_answered_id answer_text
  1                1           1                2 Four
  2                1           2                2 No
  3                1           3                2 I love cows!
  4                1           3                3 I hate cows.

This way you only need one table per type of fact, you don't have to generate tables or columns dynamically, and you can store as much text as you like for the data.

Upvotes: 2

Kyle Willoughby
Kyle Willoughby

Reputation: 21

Typically, the maximum length is 128 characters, but there are exceptions depending on what version of SQL you are using.

Upvotes: 0

Related Questions