1

I have a pool of 100 users who have answered a question. Only about 10% of those users will choose the right answer.

I want to create a new table, with a row for every one of the 90% who answered wrong, with their username and the wrong answer they provided.

What is the best way to do that?

Should I first create an array of the usernames who entered the wrong answer, then, do a foreach loop, with an INSERT MySQL for each iteration?

Or is there a MySQL way to get the approach "apply to all who have NOT answer right"?

Thanks a lot. Regards

Improve this question
3
  • Do you get all the answers at the same time? So, in one request? Commented Apr 30, 2012 at 22:04
  • 1
    Without knowing more I would say you could use prepared statements. Inside the foreach you would then execute the statement. Alternatively, INSERT supports multiple rows in a single SQL query. Commented Apr 30, 2012 at 22:05
  • Hi, yes, I only apply this command when all the users have answered, so it's a one-time command. Commented Apr 30, 2012 at 22:06

4 Answers 4

Reset to default
3

The fastest and easiest way to do a lot of single inserts is:

INSERT DELAYED
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

And here's the reference: dev.mysql.com/doc/refman/5.5/en/insert-delayed.html
2

Assuming you have a table that stores all users and answers, like so:

Table : all_answers user_id | answer joe a mary b john c

And the correct answer is a c, you can have a similar table (same schema) called wrong_answers, and populate it like so:

 INSERT INTO wrong_answers SELECT user_id, answer FROM all_answers WHERE answer <> `c`

You could also create a trigger to do this for you, or a view.

If you have other values stored in wrong_answers (such as a primary key), you would want to indicate which values in wrong_answers to populate, like so:

INSERT INTO wrong_answers (user,answer) SELECT user, answer FROM answers WHERE answer <> 'c';
Improve this answer

2 Comments

Hi thanks for your response. What if in the table wrong_answers, the order of the columns is as follows: idprimary,username,answer. Do I have to use NULL somewhere? THanks a lot
If the idprimary is auto incrementing, you don't need to provide a value, but you will need to indicate that you are only populating the second to fields. I'll update in just a moment.
2

Use a multi-insert SQL query, like so:

INSERT INTO table1 (col1, col2) VALUES (1, 2), (6, 9), (7, 8)

This will let you insert however many rows you need to in a single query.

Improve this answer

2 Comments

Hi, thanks for your response. In the VALUES part of your example, I assume the first digit of each pair would be the username and the second digit would be the answer. Is that correct? Thanks
Yes, you are correct! So you would do something like INSERT INTO table1 (username, answer) VALUES ("Nick", "something"), ("Alex", "Something else")
0 
insert into new_table select (username from old_table where got_it_wrong = 1 )
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.