Making PHP tests.

[Anon]

I have constructed a PHP test and it consists of twelve questions. The user logs in and then proceeds to the first question where the database automatically captures the datetime, username, id, and password. Once they hit the submit button the database updates if correct or not and then moves to the next question and so forth.

My question is with an UPDATE sql statement a student could return to the test at a later time and change the answers and the database would reflect those changes. Is there a way to tell the database to only update once and then reject all subsequent changes after that?

Sort of like this,

If (submit = "one time")
{
no update again
}

[Anon]

If you structure the database correctly, you can prevent
rows from being updated if they already have a value.

  update table set score_x = 1 where id = '$id' and score_x is null

By "structure correctly" I guess all I mean is that columns to be
so treated must not have a default value specified, and you must
be careful yourself in your app not to assign them a value until
you are actually entering a score.

[Anon]

I'm assuming your storing information like like this:

username,id,answer1,answer2,answer3,etc...

If you had a database just for answers, like this

username,question_number,answer

then make the username,question_number combo unique, you would have your checking you want.

CREATE TABLE answers (
username VARCHAR(20) NOT NULL,
question_num TINYINT NOT NULL,
answer TEXT,
unique (username,question_num)
);

mysql> show columns from answers;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| username | varchar(20) | | PRI | | |
| question_num | tinyint(4) | | PRI | 0 | |
| answer | text | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into answers values ('John',1,'False');
Query OK, 1 row affected (0.08 sec)

mysql> insert into answers values ('John',2,'True');
Query OK, 1 row affected (0.00 sec)

mysql> insert into answers values ('John',1,'True');
ERROR 1062: Duplicate entry 'John-1' for key 1

Hope that helps...or gives you an idea..

---John Holmes...

[Anon]

I meant create a new TABLE for answers...not a whole new database...

[Anon]

Thanks for the helpful advice. I already have the table for each test set up as it own like this and fiqured I would need to use a unique key but I wasn't sure how to do this.

create table test(id INT PRIMARY KEY,username varchar (20) NOT NULL,
testdate DATETIME,
questionone INT NOT NULL,
"""
"""
"""
"""
"""
questionsix INT NOT NULL);

But after you hit the sumbit button on the first question the database is updating with zeros all the way across for all of the questions. This problem will ruin the unique key field solution because I won't be able to update a field that is already filled. Why would the database update with zero's for all the questions when the user has only submitted his/her answer for the first question? Is it poor database design?

[Anon]

Why would the database update with zero's for all the
questions when the user has only submitted his/her
answer for the first question? Is it poor database design?

Definitely, given that one of the requirements is the individual
columns are going to be updated individually. You might get
this to work by removing the NOT NULL attributes, and any
code you have that updates non-answered columns to zero.
But as long as you're going to that trouble, why not fix
the underlying design completely, and come up with a normalized
design with separate tables for the user, test, and questions?

[Anon]

Yes I figured out the NOT NULL design was flawed and that solved the problem. As for my design I have five tests. I have a register table that holds all of the usernames, passwords, and other information about the students. I also have five tests. I have constructed a table for each of the five tests and the username links them all together. Is this not a good normalization scheme?

[Anon]

I'm wonderingif anyone has created a testing process, like a scantron-style method using php?

I've got an online game ( http://promotions.news-press.com/np/primepicks ) that allows a user to pick winners from 20 football games (thus, 20 questions), stores the info in two tables (one for user data, one for game results).

Once all the entries are 'in' (or after the weekend games are played), an administrator goes into another interface, enters the actual winning teams (thus, the answers), and php runs a compare on the correct answers to each entrant.

This causes a heavy load on the db, since I'm doing a compare, and then a replace (update) on each field w/ a 1 or 0 (1 if correct, 0 if blah blah). Then, php returns the score of each player (20 - their total number correct).

an example: played 20 games, got 16 correct. score: 16-4.

We get around 600 entrants per week for this 10 week game, and it's becoming quite a database. I've had to make backup copies contstantly since the grading often damages the tables.

Anyone else create a 'scantron-style' testing/grading procedure?