[Resolved] Creating form from DB, then returning values to DB

daeyin

Here is my problem. I've given it a great deal of thought and tried to solve the problem by myself, but I've hit a wall.

I am trying to make an online quiz. Ideally, questions will be read from a database and displayed "x" at a time (where x corresponds to the variable $questions_per_page).

I've managed to figure out how to read the questions from the database, actions to take when I've reached the end of a section (move onto the next section), what to do when I've run out of sections (end the quiz).

my 'questions' table has the following columns:
question_id (the auto increment index), quiz_id (which is equal to quizes.quiz_id in the same database), question_title, cat_id ( == categories.cat_id), question order, question_type (more on this later).

my 'answers' table has the following columns:
answer_id (the index) question_id, quiz_id, answer_title, points, is_autofail.

So, the script does its "connect to the database and gather the data from that" goodness and then, based on how many questions there are starts the form... so:

my query to the db looks something like
"SELECT -the stuff I need- FROM -the tables I want to use- WHERE question_id >= $pagemin AND question_id <= $pagemax"

Then I do some reading and printing of the questions:

echo '<form action='.$PHP_SELF.' method=POST>';
echo "\n\n";
for($i=0; $i < $questions_per_page; $i++)
{
$row = mysql_fetch_array($result);
print_questions($row);
echo '<br/>';
echo "\n";
}
echo '</form>';

the print_questions function takes a $row then queries the database for question matching that question_id and does 1 of three things so far:

if the question type is "Text" : prints the question_title and leave a text box

if the question type is for radio buttons
queries the database for all answers matching that question_id will then do a for loop that prints

echo '<input type="radio" name="var'.$row['question_id'].'" value="'.$f_row['answer_id'].'">';
echo $f_row['answer'];

so, I get out put like

SomeQuestion 5
Answer 1
Answer 2
* Answer 3

the html for this looks being formed by:
$question_title
<input type="radio" name="$question_id" value="$answer_id">Answer 1<br>
<input type="radio" name="$question_id" value="$answer_id">Answer 2<br>
<input type="radio" name="$question_id" value="$answer_id">Answer 3<br>

Something similar happens for check boxes.

Reading from the database to create the questions isn't my problem really. Returning that data is.

I have a table called user_respones with the columns of response_id (index) user_id, quiz_id, question_id, answer_id.

The result I want is for the data to look a bit like this (in the same order)

4 | 12 | 1 | 9 | 2

meaning, the fourth response given by user #12 for quiz #1 question #2 was answer #2.

But, I can't for the life of me figure out how to do this.

Here, finally, are my issues

1) currently, the name I give the <input> is $question_id. My idea was that I could then write a sql query along the lines of

INSERT into user_responses user_id, quiz_id, question_id, answer_id '$user_id', '$quiz_id', '$question_id', '$answer_id'

but, after the script processes, I have no clue really about what my variables will be named!

if the template for them is 'var'.$question_id

then I could get a set of variables like these:

$var12 = '$answer_id'
$var8 = 'answer_id'
$var167 = 'answer_id'

and so on

I suppose I could write one HUGE script that has a rule for every variable

if($var1) {add it to the db}
if($var2) {add it to the db}
if($var3) {add it to the db}

and so on. But, this seems horribly inelegant and is completely useless when I need to add a new questions (i'd have to alter the script and add the lines if($var322) {add it to the db}

2) I could just give the <input> a generic name like var1,var2,var3,var4,var5 for example

but I'm not sure how I would auto increment in the processing script... or how I would store both the question_id and answer_id

I guess I could do something like

$var# = 'question_id , answer_id'
then use explode to make two more variables.... but this doesn't seem right to me either.

3) and the biggie. Some answers are free response, so won't have a corresponding pre-generated answer.

My thoughts are I can either have the free response become an answer and give it an answer_id (INSERTing into the db the question id and the text of the answer)

or have some special table to hold these few special cases...

Anyone know of a good resource for getting a better idea of how to read questions from a database then write the answers to the database, because I am completely frustrated.

Pig

My advice would be to alter your scenario a bit.

First, when storing the uses answers, I do not suggest using the format you described (4 | 12 | 1 | 9 | 2). This structure is not normalized, and you will find trying to manipulate it and retrieve meaningful data will be a headache. Not to mention that you would be count productive toward the good design you are after. If you change the question order, or something changes slightly, all of your coding is messed up since it is reliant on the data being in a certain order. That is never a good idea. To normalize your data, you should have a table of the users ids, question id, and the answer id. If you don't track the user's id, you can still ditch the column, or just increment that max user id that is in there.

Anyway, as for displaying the questions. I would track all the answers/questions in a session. Then on every page, I would run a query to get the question ID's from the questions table that are not already in the session. Every time they submit the page, the script would add all the new answers to the session, and then check to see if all the questions have been answered. If they have all been answered, submit them to the database. If they have not all been answered, then run a query to get the next set of 10 questions that have not been answered.

I would store all the answers in an array, and then implode the array for use with 'NOT IN' for the query.

daeyin

I think I'm more confused now...

First, when storing the uses answers, I do not suggest using the format you described (4 | 12 | 1 | 9 | 2).

and

To normalize your data, you should have a table of the users ids, question id, and the answer id

the above table is a table of the user ids, question ids, and answer ids ... I just also store the quiz_id and the (granted... a bit useless) response_id (I just needed an index, I doubt I will ever use the response_id in meaningful way)

even if I tossed out the response id that still leaves me with

user id | quiz id | question id | answer id
12 | 1 | 9 | 2

I need quiz id so that if there are multiples quizes I can track results for only certain quizes.

The end result will be html page with the user's responses organized by question meaning I'd need to limit my query by quiz (so I don't get the user's answers from another quiz if they happen to take it), user (so I get only the results for one user) question and answer (so I can see what I asked him and what his response was).

Or, I could get a list of what all users responses were to one question (over multiple quizes)

Perhaps a better question is: can anyone point me to some existing multi-page quizes like that crazy eharmony.com page (or any of those types of pages that).

I suspect that eharmony.com, since they have a fixed number of questions, uses fairly closed, tight code.

I'd like mine to be more open so that I can add questions (and quizes) as I go.

Thanks for being helpful and patient... tomorrow (wait, it's after 12am - today) is the one month anniversary of me coding in php (or any programming language at all, for that matter)

Pig

Sorry for the confusion. I have seen lots of people try to insert multiple values into a single cell. That table looks fine. You should be able to out any kind of data you need.

Not sure if that answers your question. It looks like you are set to build what you need. Sorry, I don't know of a premade solution to recommend.

If I haven't answered your question, please let me know.

broc7

If I understand the problem correctly (and I'm not sure that I do), I think you actually need another index: quiz_instance. This would be matched up with quiz_id (meaning which static quiz) and user_id (who took this instance of the quiz). Responses would be stored on a different table with quiz_instance and question_id. When the form with the answers is submitted, you only need to send along these 2 extra bits of information. Since quiz_instance (perhaps with a better name) is being sent along, the db will know, from the other table, what the next batch of questions to be presented are.

I think your five-index table is too much disparate info for a single table. With quiz_instance linking two tables, I think you'll find that it'll be much easier working with either of them, and when you need info from both, it's only a join away.

daeyin

I'm not having any trouble chunking the questions out in $questions_per_page chunks. I solved that, (quite cleverly if I might say) by "reading ahead" in the database (as I like to call it).

I have some variables and math to take care of that.

Questions are arranged into categories, then ordered (using the question_order row)

Here we go:

if(!$cat_ID) $cat_ID = 1;
if(!$page_num) $page_num = 1;

this sets the category to a value of one and also sets the page number to a value of one

then

$questions_per_page = 5;

$page_min = $questions_per_page * ($page_num-1) +1;
$page_max = ($questions_per_page * $page_num);
$page_max_queried = $questions_per_page + 1;

$page_min defines the lowest question on that page

$page_max defines the highest numbered question that appears

$page_max_queried is the part of the code that enables "read-ahead"

I also have a few reads to the DB to find out how many categories there are total (saved in the $total_cat variable)

the SQL query goes like this :

$query = 'SELECT questions.quiz_id, question_id, question_title, questions.cat_id, question_order, question_type, categories.cat_id, cat_name, cat_order FROM questions, categories  WHERE categories.cat_id = questions.cat_id AND questions.quiz_id = '.$quiz_ID.' AND questions.cat_id = '.$cat_ID.' AND question_order >= '.$page_min.' AND question_order <= '.$page_max_queried.' ORDER BY question_order ASC';

Allow me to explain:

let's say that we are $page_num = 1 and $cat_ID=1 (we are on the first page of the first category in the quiz)

Assuming $questions_per_page = 5 the SQL query will try to select questions 1- 6
(this seems counterintuitive, since we only want five questions per page, right? - bear with me for a moment)

Then I run the code:

mysql_query($query);		
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

Why try to find 6 rows when all we want is 5 questions to be displayed?

Well, it helps for chunking out the questions properly.

if I ask for 6, and the DB says "yep, I got 6 more questions for ya!"

then I know that there are more questions in this category. I then run a loop:

if($num_results > $questions_per_page AND $cat_ID < $total_cat)

I run whatever code I need, then I add edit my
$page_num and $cat_ID variables and pass them has <input type=hidden>

$page_num = $page_num +1

the user hits submit, and the variables at the top of the script are changed so now we are in the same category, but $page_num =2 (which means we will print questions 6-10)

What else could the SQL query tell us?

It could say say "You wanted me to find you 6 questions buddy, but I'm only seeing 4"

This means we've reached the end of the category (and, if we have run out of categories, we've reached the end of the quiz)

So:

elseif($num_results <= $questions_per_page AND $cat_ID < $total_cat) { Do some stuff;
$page_num = 1;
$cat_ID = $cat_ID + 1;

If there are categories left, we increment the $cat_ID by 1 and make $page_num = 1 again.

If we are out of categories:

elseif($num_results <= $questions_per_page AND $cat_ID >= $total_cat)
		{
			echo 'The Quiz is over!';
		}

The long and short of this post is:

I've figured out how to chunk out the questions the manner I want. What I don't know how to do, I'll address in the next post a little more clearly.

daeyin

Let's say I have a pretty small static form with a few variables.

The script to process it would be fairly simply.

We'd end up with two variables set to some value:

$Name = 'Joe Schmo';
$sex = 'Other';

And manipulating these variables works just like manipulating any other variables in php.

echo $Name;
echo '<br/>';
echo $sex;

The draw back to this is, when we add another question to our form, say:

Favorite Color:
<input type="radio" name="favcolor" value="Red">
<input type="radio" name="favcolor" value="Blue">
<input type="radio" name="favcolor" value="Green">
<input type="radio" name="favcolor" value="Other">

we then have to alter our script and add some function to the new, unexpected variable $favcolor

echo $favcolor;

Now, if we were to devise a system where a user could add questions, and then answers to those questions to be saved in a database to later be read back and automatically put into a form, we could not use the easy name="what my variable will be" format. If we have, over 100 quizes, 1000 questions we can't realistically write a script that accounts for all of them:

if($somevar) 
{
do something
}

if($some_other_var)
{do something else}

...

if($our_100thvar)
{
do something
}

and even if we did want to write this script, maintain it would be hellish. Every time a user added a question we would have to edit the script to make sure there was a rule for it.

So,

we have to find some way of having:

echo $question_name;
echo '<input type=radio name='.$question_variable_name.' value='.$answer_value.'>';
echo '<input type=radio name='.$question_variable_name.' value='.$answer_value.'>';
echo '<input type=radio name='.$question_variable_name.' value='.$answer_value.'>';

Still, if we allow the user to define the
$question_variable_name and $answer_value we'd have to go back and write code for dealing with all these new variables.

My idea was to use question and answer IDs to print these

so we might have a question with data called question_id = 2
question_title = Sex

and some answers

answer_id = 200
question_id = 2
answer_title = Male

answer_id = 201
question_id = 2
answer_title = Female

answer_id = 202
question_id = 2
question_title = Other

Reading from the database we would get output of HTML in the format:

Sex
Male <input type=radio name=2 value=200>
Female <input type=radio name=2 value=201>
Other <input type=radio name=2 value=202>

Then you could write to the database and say

"User # 678 answered Quiz #1 question #2 with answer #202"

two obvious problems:
1) variables can't be named $1, $2, $5, etc
2) You'd have no way of writing a script that can handle new variables unless you had a loop that used some arbitrary high number to do

for ($i = 0; $i < 1000000000; $i++)
{
'query mysql to insert the value of the variables with values into the db
}

That is, the script would go

$i = 0
insert the value of variable $0 into the db
with question_id = 0 and answer_id = the value of $0
$i++

$i = 1
insert the value of variable $1 into the db
with question_id = 1 and answer_id = the value of $1
$i ++

. . .

$i = 780432
insert the value of variable $780432 into the db
with question_id = 780432 and answer_id = the value of $780432

obviously, this is not an option.

to over come the name='.$question_id.' problem is it possible to have

name=var['.$question_id.']

and store the answers in an array?

then at least I could find out how many questions were in a quiz and do a loop for that

so we could end up wth

$var[2] = 202

for the above Sex = Other quesitons

But, and here is my real problem, how do i shuttle around the value of $question_id?

$var['question_id] = answer_id

I want to be able to INSERT

INSERT into quiz (user_id, quiz_id, question_id, answer_id) values ($user_id, quiz_id, question_id, answer_id)

But I am stuck on how to shuttle that question_id around.

my two ideas were:

using var[2] format and trying to somehow rip that "var[" and "]" off of the variable to get the name I need

some_funciton("$var[2]")

would return "2"

2) OR

making the name="" in the input generically named with a loop based on $questions_per_page

(i.e. var1, var2, var3, var4, var5)

then having the values of these variables contain both question_id and answer_id

so $var5 = '2,202'

then using

explode

to separate them into to variables I can manipulate.

Long question short, how can I move about that $question_id variable so that when it comes time to INSERT INTO I have the value?

broc7

Oh, I see what you're talking about now. To rephrase, you need to get a bunch of form-posted name-value pairs where the names are arbitrary, instead of specifically expected by the receiving page.

I have two possible solutions:

When you're building the question page, create an array in hidden form fields whose values are the question_ids. The hidden fields' names would be 1, 2, 3 etc and therefore known. Include an extra hidden field for the total number of question id's to get. Once you access the question id's in this manner, you then use them to get the answers.

The other option is to iterate over all the name-value pairs in the posted form:

<?php 
    reset ($_POST);
    while(list($key, $val) = each ($_POST))
        //save the keys and values
?>

Resetting the post array sets it to the first value. This may or may not be necessary. In the second example, you've got to check that all the form fields are actually questions/answers. You've probably got some other stuff in there.

daeyin

Thanks! this does exactly what I need!

daeyin

I should mention that

<?php_ 
____reset_($_POST); 
____while(list($key,_$val)_=_each_($_POST)) 
________//save the keys and values 
?>

was what helped.

Thanks again