Page 1 of 1

General Question - Naming

Posted: Thu Jun 10, 2010 12:57 pm
by icesolid
Just looking for some opinions.

I have a form with about 200 questions and I will be storing answers in a MySQL database table. Each one of the questions are about 10-15 word questions. How should I go about naming the columns for the questions in my database table?

Also, in the future I may need to shimmy a question in or out here and there, so naming the columns field1, field2, field3 etc. may pose a problem.

Re: General Question - Naming

Posted: Thu Jun 10, 2010 2:30 pm
by John Cartwright
You definitely don't want to use a fixed column structure, as you noticed, it is not a flexible structure. Instead, you want to use a relational structure. Something along the lines of

Code: Select all

CREATE TABLE IF NOT EXISTS `answers` (
  `answer_id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(10) NOT NULL,
  `answer` tinytext NOT NULL,
  `is_correct` tinyint(1) NOT NULL,
  PRIMARY KEY (`answer_id`),
  KEY `is_correct` (`is_correct`),
  KEY `question_id` (`question_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `answers` (`answer_id`, `question_id`, `answer`, `is_correct`) VALUES
(1, 1, 'red', 0),
(2, 1, 'green', 0),
(3, 1, 'blue', 1);

CREATE TABLE IF NOT EXISTS `questions` (
  `question_id` int(10) NOT NULL AUTO_INCREMENT,
  `question` tinytext NOT NULL,
  PRIMARY KEY (`question_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `questions` (`question_id`, `question`) VALUES
(1, 'What color is the sky');
This will allow you to have multiple answer values, including those which are correct and incorrect.

Code: Select all

SELECT * 
FROM questions
LEFT JOIN answers ON questions.question_id = answers.question_id
[text]
question_id question answer_id question_id answer is_correct
1 What color is the sky 1 1 red 0
1 What color is the sky 2 1 green 0
1 What color is the sky 3 1 blue 1[/text]

Re: General Question - Naming

Posted: Thu Jun 10, 2010 2:51 pm
by icesolid
That looks like a really great idea I want to use that, however, the format of this form is pretty complex. How would I output those questions into the format I need?

Also, this form uses almost every type of form element.

Re: General Question - Naming

Posted: Thu Jun 10, 2010 2:52 pm
by John Cartwright
icesolid wrote:That looks like a really great idea I want to use that, however, the format of this form is pretty complex. How would I output those questions into the format I need?

Also, this form uses almost every type of form element.
You'll have to be more specific.

Re: General Question - Naming

Posted: Thu Jun 10, 2010 2:56 pm
by icesolid
Well I have the form in a pretty complex table layout with many different colspans, rowspans and what have you.

I am just imagining that the method you listed above would be to run through the questions table as a loop and...like....row by row spit out a question and answer?

Re: General Question - Naming

Posted: Thu Jun 10, 2010 3:01 pm
by John Cartwright
icesolid wrote:Well I have the form in a pretty complex table layout with many different colspans, rowspans and what have you.

I am just imagining that the method you listed above would be to run through the questions table as a loop and...like....row by row spit out a question and answer?
I'm not going to write it all for you ;)

Post what you have tried and exactly what you are having difficulty with. However, it is probably easier if you store the question / answer pairs as an array first.

Code: Select all

$sql  = "
   SELECT *
   FROM questions
   LEFT JOIN answers ON questions.question_id = answers.question_id
";
$result = mysql_query($result) or die(mysql_error());

$questions = array();
while ($row = mysql_fetch_assoc($result)) {
   $questions[$row['question']] = $row;
}
Now you have all the answers grouped by their respective question.

Re: General Question - Naming

Posted: Thu Jun 10, 2010 3:14 pm
by icesolid
That answered my question. Thanks for the help!!