session variable and table join

[siobhan]

I have two tables (below): 1) a general test table which lists questions on a test and their answers 2) a user test table which has users and their responses to questions.

I would like each individual user to be able to review the questions on the test (same for all users), their answer to the question (unique to each user) and their score (unique to each user) .

Instead of inserting the question, which already exists in the test table into the user test table, I have just used the testID from tbl_test and then I use the following code to display all the results.

SELECT *
FROM tbl_usertest INNER JOIN tbl_offering ON tbl_usertest.testid=tbl_test.testid
ORDER BY tbl_usertest.testid DESC

The trouble comes when I want to add in the session variable bit. How do I do this? (I have no problem doing the session variable bit without a join.) I know the problem is that the tbl_usertest has a username but the tbl_test doesn't. But I can't add a username into the tbl_test. Should I just make life easier by inputting the question into tbl_usertest? Or are my tables just so mucked up they are beyond help?

Thanks
siobhan

CREATE TABLE tbl_test (
testID int(11) NOT NULL auto_increment,
questionid varchar(5) NOT NULL default '',
question text,
answer text,
evaluation text,
possiblescore varchar(50) default NULL,
PRIMARY KEY (testID),
KEY testID (testID)
) TYPE=MyISAM;

CREATE TABLE tbl_usertest (
usertestID int(11) NOT NULL auto_increment,
testid varchar(5) NOT NULL default '0',
username varchar(50) default NULL,
useranswer text,
userscore varchar(50) default NULL,
PRIMARY KEY (usertestID),
KEY usertestID (usertestID)
) TYPE=MyISAM;

[cvsherri]

well.. i started workin on this one fer ya, however, i can not determine how you could possably match a users reqponse to a question back to the tbl_test question, unless each question have a differant testID. Since that didnt make any sence, i solved the issue by adding the question number to the tbl_usertest and then wrote my query like this...

SELECT tbl_test.answer, tbl_test.evaluation, 
tbl_test.possiblescore, tbl_usertest.username, 
tbl_usertest.useranswer, tbl_usertest.userscore
FROM tbl_usertest INNER JOIN
          tbl_test ON tbl_usertest.testid = tbl_test.testID
   AND tbl_usertest.questionid = tbl_test.questionid

since i am assuming that the session var is for user identification, i assume that you can handle that by simply adding the session var into the Query text

SELECT tbl_test.answer, tbl_test.evaluation, 
tbl_test.possiblescore, tbl_usertest.username, 
tbl_usertest.useranswer, tbl_usertest.userscore
FROM tbl_usertest INNER JOIN
          tbl_test ON tbl_usertest.testid = tbl_test.testID
   AND tbl_usertest.questionid = tbl_test.questionid
WHERE (tbl_usertest.username = '$session_user')

hope this helps