Name mysql table columns using php POST variables

Question

I'm building an web application where the user fills an html form and the information captured is used to create a table with corresponding columns. However, mysql returns an error where the POST variable is used to name the columns. Here is a sample of the code:

$questionnaire = mysql_real_escape_string(htmlentities($_POST['questionnaire']));
$question = mysql_real_escape_string(htmlentities($_POST['question']));
$answer1 = mysql_real_escape_string(htmlentities($_POST['answer1']));
$answer2 = mysql_real_escape_string(htmlentities($_POST['answer2']));

mysql_query = "CREATE TABLE $questionnaire
               (id int(5) NOT NULL AUTO INCREMENT,
                PRIMARY KEY(id),
                question varchar(200),
                '$answer1' varchar(200),
                '$answer2' varchar(200) )";

How do I make the value in $answer1 be used to give a column its name?

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

use BACKTICK not SINGLE QUOTES as column names are identifiers,

mysql_query = "CREATE TABLE $questionnaire
               (id int(5) NOT NULL AUTO INCREMENT,
                PRIMARY KEY(id),
                question varchar(200),
                `$answer1` varchar(200),
                `$answer2` varchar(200) )";

MySQL - when to use single quotes, double quotes, and backticks?

edited May 23 '17 at 12:29

Community

1
1

answered Apr 09 '13 at 16:12

John Woo

258,903
69
498
492

i tried the backticks and it gives me a new error: Incorrect column name '' – user2262552 Apr 09 '13 at 16:28
what are the values you passed on the variables? – John Woo Apr 09 '13 at 16:38
a person's name. its a questionnaire and the answers are used to name the columns – user2262552 Apr 09 '13 at 16:47

score 0 · Answer 2 · answered Apr 09 '13 at 16:13

0

Don't put it in parentheses ('). If you escape column names, you will need to use backticks.

However, you should reconsider your table layout. If you have to generate tables and/or columns on the fly, there is most certainly something wrong with your design.

answered Apr 09 '13 at 16:13

mrks

8,033
1
33
62

the number of columns is fixed but the names vary from table to table. its a questionnaire so i thought of allocating a table to each question and its answers. – user2262552 Apr 09 '13 at 16:30

score 0 · Answer 3 · answered Apr 09 '13 at 16:18

0

change this AUTO INCREMENT to AUTO_INCREMENT

http://www.w3schools.com/sql/sql_autoincrement.asp

answered Apr 09 '13 at 16:18

echo_Me

37,078
5
58
78

Name mysql table columns using php POST variables

3 Answers3