csabi_fl Posted October 4, 2006 Share Posted October 4, 2006 Hi.I am building a website and I am stuck .I have a form on one of my pages that has 5 fields(M,Tu,W,Th and F).Each field contains a question .Members have to answer each question in any particular order and one at a time.When they for instance answer question M and click on submit the answer goes into a table in my database.I use Mysql.Now the table has 6 columns :loginid,M,Tu,W,Th and F.What I need is a code that inserts the data into the table one at a time and I want to have only one row,something like this:loginid/answer to M/answer to Tu/answer to W/answer to Th/answer to F,so everything is nice and neat.And one more thing,I want to prevent them from sending in an answer to the same question twice.Please help me out , I have been trying for about a month now with no success.Thank you very much in advance for your time. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 4, 2006 Share Posted October 4, 2006 Well, first, I would discourage storing each question/answer pair as a separate column/value, since this is quite inflexible. However, as far as your UPDATE statement is concerned, you could easily make sure the column was blank to begin with. Alternatively, if you were to normalize the table, you could simply run INSERT IGNOREs with a proper UNIQUE key. Quote Link to comment Share on other sites More sharing options...
csabi_fl Posted October 4, 2006 Author Share Posted October 4, 2006 I'm afraid I don't understand.I am not a pro,could you be a little more specific,PLEASE. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 4, 2006 Share Posted October 4, 2006 Sorry, my bad... instead of having multiple columns for each question, you should simply have loginid/question/answer as your columns. That way, for each "answer", you simply INSERT a new row, with the loginid of the user, the question, and the answer value provided. If you set up the table with a UNIQUE index on (loginid, question), then you won't be able to have multiple answers for a given question for a given user. Hence INSERT IGNORE. Does that make more sense? Quote Link to comment Share on other sites More sharing options...
csabi_fl Posted October 5, 2006 Author Share Posted October 5, 2006 It makes a lot of sense.I think it's an excellent idea.The only thing that I am not familiar with is the INSERT IGNORE command.I tried to look it up in the Mysql documentation but there isn't much there.Could you tell me how to use it in my case? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 5, 2006 Share Posted October 5, 2006 It is in the documentation for INSERT syntax. Basically, if you were to try and insert a record that would result in a duplicate result for a UNIQUE index, you would normally get an error. This can often be useful, if you want to notify the user, for instance (like for logins/e-mails); however, in your case, it sounds like you don't care, so you can simply supress the error, and no new record will be inserted. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.