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!!