Need advice on mysql database structure and functionality

[DamienRoche]

I'm at a total loss here.

 

I have created an admin side to a series of questions and answers.

 

The questions are in table questions with a auto_incremented number

 

and the answers in table answers with each answer linked to the question id

 

-------------Now, the admin panel will have:

 

Question 1

answer1

answer2

answer3

etc

 

Question 2

answer1

answer2

answer3

etc

 

Question 3

answer1

answer2

answer3

etc

 

Question numbers are from the auto_incremented id and the answers are linked to that.

 

But how would I go about say deleting question 2 while forcing question 3 to replace it and fetch the correct answers for question 3.

 

Also, how would I add a question - say after question 2 and have it replace question 3 while making question 3 now question 4.

 

Like I say, I'm at a total loss on how to structure this.

 

Any help is greatly appreciated. Thanks.

 

[btherl]

You shouldn't use an auto_increment id for question numbers if you want to rearrange them.  Instead you can add another column for question id (in addition to the auto increment column).

 

The purpose of auto_increment columns are for when you want a unique identifier for each item, and you don't care what that identifier is.

[haku]

Also, rather than using the IDs of the questions, you can pull the questions from the database, ordered by ID, then use an incrementing counter to output the question numbers. In this case the IDs wont matter:

 

$results = mysql_query("SELECT id, question FROM question_table ORDER BY id ASC");
$i = 1;
while($result = mysql_fetch_array($results))
{
   $answers = mysql_query("SELECT answer FROM answer_table WHERE id='" . $result['id'] . "'");
   echo "Question " . $i . ") " . $result['question'] . "<br />";
   $i++;
   while($answer = mysql_fetch_array($answers))
   {
      echo " - " . $answer['answer'] . "<br />";
   }
}

 

note: not tested for typos etc.