I don’t know how to make this with mysql, I only know how to do basic queries, I want to show a list of result based on matches, Results will be shown based on the same value of the answers…answers may have a value from 1-10
+++++++++++++++TABLE++++++++++++
id | userName | answer1 | answer2 | answer3 | answer4….
10 Jhon 1 1 3 8
11 Anne 1 2 4 8
12 Mike 7 4 5 7
etc…
++++++++++++++++++++++++++++++++++++++++
If I send the values in the query I want that check the answers and show the result sorted my matches, more matches first…no matches last
So if i send the results:
answer1=1 answer2=1 answer3=7 answer4=2...
the result should be(give back the id)
10 11 12
Your table design is not fine, you should separate it into users and questions table.
If you cannot change the table design you can solve you problem using this query:
select
id,
username,
if(answer1 = :an1, 1, 0) + if(answer2 = :an2, 1, 0) + if(answer3 = :an3, 1, 0) + if(answer4 = :an4, 1, 0) as total
from
table
order by total desc
UPDATE: Better design for this problem:
Check the SQLFiddle: http://sqlfiddle.com/#!9/6c145/2 with a live demo.
Create Users Table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
Create Questions Table
CREATE TABLE questions (
id INT PRIMARY KEY,
correct_answer INT NOT NULL
);
Create User Answers Table
CREATE TABLE user_answers (
user_id INT,
question_id INT,
user_answer TINYINT,
PRIMARY KEY (user_id, question_id),
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE NO ACTION ON UPDATE NO ACTION
);
Than to retrieve the data you can use the query:
SELECT
tmp.id,
tmp.username,
sum(tmp.is_correct) as total
FROM (
SELECT
users.id,
users.username,
IF (questions.correct_answer = user_answers.user_answer, 1, 0) as is_correct
FROM
users
INNER JOIN user_answers on users.id = user_answers.user_id
INNER JOIN questions on user_answers.question_id = questions.id
) tmp
GROUP BY tmp.id, tmp.username
ORDER BY total desc;