I have a table
CREATE TABLE response (
rID int NOT NULL AUTO_INCREMENT,
sID int NOT NULL ,
qID int NOT NULL ,
response int NOT NULL ,
PRIMARY KEY (rID, sID, qID))

Can i insert into this table in this format...(rID is auto increment)

rID sID qID resonse
_____________________
1 1 1 sdad
1 1 2 fsdfsd
1 1 3 fsdfsd
2 2 1 fdsfsdf
2 2 2 dsfsdfsd
........

If i use rID = NULL while inserting each row it would increment rID in second row i guess and so oon.
How can i insert a row with an increment in rID when SID changes.
Can some one please help me...
Thanks,
Cooldude.

can you insert those rows?

hmmm, did you try it? i did

i'll even share the insert statement with you --

insert into response
(rID, sID, qID, response)
values (1, 1, 1, 'sdad')
,(1, 1, 2, 'fsdfsd')
,(1, 1, 3, 'fsdfsd')
,(2, 2, 1, 'fdsfsdf')
,(2, 2, 2, 'dsfsdfsd')
;

how can you insert a row with an increment in rID when sID changes?

that's a good question, i don't think you can write any sql that determines when sID "changes"

you would have to determine that before you inserted the row

and then what would you do when you wanted to add a second and third row for the same rID?

insert into response
(rID, sID, qID, response)
values
(2, 2, 3, 'dsfsdfsd')
,(null, 3, 1, 'another')
,(??, 3, 2, 'another')
,(??, 3, 3, 'another')
;

the best thing to do here is not to use an auto_number for rID and simply insert the values yourself

It looks like the sid column will always have the same value as the rid.

If so, then what is the point of having two columns with exactly the same value?

Ravi

Hi all,
I would like to give some clarification....
rId is response id which means that each individual user must have a distinct id and sid is a survey id and qid is question id where each survey might have 10 questions or so.

How can i generate rId to be distinct for each user that is submitting the survey.

If we generate rId manually how can i know rId of the previous user and if many users r submitting the survey simultanelosly how would it know what rId value it should take.

Any help would be appreciated.

i'm going to make the assumption that each user's responses to the survey are to be processed as a group

furthermore, i assume you have some sort of application that will screen/filter/edit/process/validate these responses, and this application will know when a user's set of responses has been submitted as a group

if the users do not already have user_ids (for example, in an anonymous survey), then all you have to do is add a new entry to a controlling "userresponse" table, logging the datetime that this group of responses were processed, and obtaining an auto_increment id value for this user

then use this value when entering the responses into the above table

i.e. don't use an auto_increment in the above table