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.
General Question - Naming
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: General Question - Naming
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
This will allow you to have multiple answer values, including those which are correct and incorrect.
[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]
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');Code: Select all
SELECT *
FROM questions
LEFT JOIN answers ON questions.question_id = answers.question_idquestion_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
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.
Also, this form uses almost every type of form element.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: General Question - Naming
You'll have to be more specific.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.
Re: General Question - Naming
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 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?
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: General Question - Naming
I'm not going to write it all for youicesolid 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?
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;
}Re: General Question - Naming
That answered my question. Thanks for the help!!