I am creating a pageant scoring system using PHP ang MySQL or PhpMyAdmin in XAMMP. I'm not an expert when it comes to queries. So I really don't know what to do or how to get the ranking of the candidates according to their scores in a category where there are 5 judges and 9 candidates.
Here is my Database... Hope someone can help me get the ranking using queries. Thanks in advance.
CREATE TABLE IF NOT EXISTS score (
candidate_no varchar(5) NOT NULL ,
category_no varchar(5) NOT NULL ,
judge_id varchar(5) NOT NULL ,
score int(3),
PRIMARY KEY (candidate_no,category_no,judge_id),
KEY score_fkey (judge_id),
KEY score_fkey3 (category_no)) ;
INSERT INTO score (candidate_no, category_no, judge_id, score) VALUES
('C1', 'cat1', 'J1', 17),
('C1', 'cat1', 'J2', 15),
('C1', 'cat1', 'J3', 17),
('C1', 'cat1', 'J4', 18),
('C1', 'cat1', 'J5', 19),
('C2', 'cat1', 'J1', 17 ),
('C2', 'cat1', 'J2', 15 ),
('C2', 'cat1', 'J3', 16 ),
('C2', 'cat1', 'J4', 18 ),
('C2', 'cat1', 'J5', 18 ),
('C3', 'cat1', 'J1', 15 ),
('C3', 'cat1', 'J2', 20 ),
('C3', 'cat1', 'J3', 19 ),
('C3', 'cat1', 'J4', 16 ),
('C3', 'cat1', 'J5', 19 ),
('C4', 'cat1', 'J1', 19 ),
('C4', 'cat1', 'J2', 20 ),
('C4', 'cat1', 'J3', 18 ),
('C4', 'cat1', 'J4', 18 ),
('C4', 'cat1', 'J5', 19 ),
('C5', 'cat1', 'J1', 18 ),
('C5', 'cat1', 'J2', 16 ),
('C5', 'cat1', 'J3', 18 ),
('C5', 'cat1', 'J4', 18 ),
('C5', 'cat1', 'J5', 18 ),
('C6', 'cat1', 'J1', 20 ),
('C6', 'cat1', 'J2', 16 ),
('C6', 'cat1', 'J3', 16 ),
('C6', 'cat1', 'J4', 16 ),
('C6', 'cat1', 'J5', 17 ),
('C7', 'cat1', 'J1', 11 ),
('C7', 'cat1', 'J2', 12 ),
('C7', 'cat1', 'J3', 14 ),
('C7', 'cat1', 'J4', 15 ),
('C7', 'cat1', 'J5', 17 ),
('C8', 'cat1', 'J1', 15 ),
('C8', 'cat1', 'J2', 16 ),
('C8', 'cat1', 'J3', 18 ),
('C8', 'cat1', 'J4', 17 ),
('C8', 'cat1', 'J5', 17 ),
('C9', 'cat1', 'J1', 19 ),
('C9', 'cat1', 'J2', 19 ),
('C9', 'cat1', 'J3', 19 ),
('C9', 'cat1', 'J4', 19 ),
('C9', 'cat1', 'J5', 18 );
