Arrays question

[dmacman1962]

Hi all,

I have a quiz built, with help from this board and it was suggested that I store my questions in one table (questions table, with a key of question_id), and the quiz name in another table (quiz table, with a quiz_id key) and then a third table, quiz_questions to relate them. (quiz_questions table, with quiz_id and question_id in it).

quiz (table)
quiz_id(autoincrement)---quiz_name
--1----------------------------test
--2----------------------------another name

quiz_questions (table)
quiz_id --- question_id (autoincrement)
--1---------------1
--1---------------2
--2---------------3
--2---------------4

questions (table)
ID (autoincrement) --question_id--question--- correct_answer_id
---1--------------------------1------What? ---------------3
---2--------------------------2-------Huh?----------------1

Now in my admin setup pages I have them enter all the info to build the quiz and I am attempting to insert data from 2 arrays and merge the data for one insert.

A print_r of both the questions array, and the query from the quiz_questions table will have matching keys.

Array (questions from form)
(
[0] => Question #1
[1] => Question #2
)

Array (from quiz_questions table)
(
[0] => 51
[1] => 52
)

I want to insert that into my questions table. But I also need to get the autoincremented question_id from my quiz table and I want to 'pair' that up for this insert. (NOTE I asked them how many questions before I let them fill in the questions, so I can put a record in for each question (in the quiz_questions table).

questions (table)
ID (autoincrement) --question_id--question---------- correct_answer_id
---1--------------------51------- Question #1 -------------------3
---2--------------------52------- Question #2 -------------------1

Does that make sense and is that even the correct way to approach this?

I have been reading up on arrays and joins and I dont know if my logic is even on the correct path?

Thanks in advance,

Don

[pete_bisby]

Mmm, need to get this right in my head ....

.... firstly, I'm going to assume you have a user table, allowing the specified user admin rights to their own quiz:
[SQL]
CREATE TABLE tblUser (
UserId INT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
UserName VARCHAR( 50 ) NOT NULL ,
UNIQUE (UserName)
)
[/SQL[

You then have a table holding main quiz information, and the user having admin rights (you could include the number of questions for each individual quiz):
[SQL]
CREATE TABLE tblQuiz (
QuizId INT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
QuizName VARCHAR( 100 ) NOT NULL ,
UserId INT( 8 ) NOT NULL
)
[/SQL]

The following table will hold the questions for each quiz:
[SQL]
CREATE TABLE tblQuizQuestion (
QuizQuestionId INT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
QuizId INT( 8 ) NOT NULL ,
QuizQuestion VARCHAR( 255 ) NOT NULL ,
QuizResponseId INT( 8 ) NOT NULL
)
[/SQL]

And this table will hold the possible answers (QuizResponseId from tblQuizQuestion will point to one of these answers for each question):
[SQL]
CREATE TABLE tblQuizResponse (
QuizResponseId INT( 8 ) NOT NULL ,
QuizQuestionId INT( 8 ) NOT NULL ,
QuizResponse VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( QuizResponseId )
)
[/SQL]

Now the admin form that will build the questions and responses can be built quite easily, and can be used for both adding and amending question and responses. If you use arrays within the input form, they can be easily referenced within PHP once the form is posted (obviously you'll need to add a bit of formatting and labels to make the form look 'nice' !!).

<form name="frmQuizQuestion" action="..." method="post" target="_self">
<input type="hidden" name="QuizQuestionId" value="" />
<input type="text" name="QuizQuestion" value="..." />
<input type="text" name="QuizResponse[]" value="..." />
<input type="text" name="QuizResponse[]" value="..." />
<input type="text" name="QuizResponse[]" value="..." />
<input type="submit" name="btnSubmit" value="Save Question" />
</form>

The information submitted can be easily looped through:

for ($i=0; $i < count($_POST["QuizResponse"]; $i++) {
  echo $_POST["QuizResponse"][$i];
}

Hopefully things are a little clearer - if not, post back with any questions.