Sql

[miztas]

I have a database with columns name, A1, A2, A3....E7

Each column represents a number which is a score from a quiz. I want to write to each column after the user has taken each quiz. ie. for first quiz:

$query = "insert into questions (Name,A1) values
('$name','$val')";

//$val is the score from that quiz.

I want to repeat this but how do I do this without creating duplicate entries, ie I want to be writing to the same name but different column every time using the same php script?

Thanks

[mrhappiness]

it would be wise to rethink your database concept

one table for users with primary key user_id
one for questions with primary key question_id
one for answers to one specific question (with one column for the right one) with primary key answer_id and question_id as foreign key of course

and last but not least one table containing user_id and answer_id

[miztas]

The thing is I need to just post a total number for that quiz in each column, there are no wrong or right answers as it is a personality quiz, the higher score denotes a certain type or personality, each quiz is to do with a trait.

[mrhappiness]

ok, so you have several quizes with severals questions with several answers meaning more or less points for this question?

table users: same as before
table quizes: quiz_id | quiz_name
table questions: question_id | quiz_id | question_name
table answers: answer_id | question_id | answer_text | answer_points
table answered: user_id | answer_id

does that help or di i get you wrong?

[pipe_girl]

Query the database first to see if the user has ever taken a test:

$result = mysql_query("SELECT * FROM questions WHERE Name='$name'");
$num = mysql_numrows($result);


//If person does not exist in database, create new entry:
if (!$num){
mysql_query("INSERT INTO questions (Name,A1) VALUES ('$name','$val')");
}

//If person DOES exist in database, update row:
else {
mysql_query("UPDATE questions SET A2='$val' WHERE Name='$name'");
}

You would probably have to have a variable set, perhaps a hidden variable, that determines which column is to be updated in the database (based on which test they took).

This will work, but think about what mrhappiness has said about table design.

Sound ok?

Jamie