Databases
StackOverflow2013
Postsdbo
POHow can I break the tie from my MySQL query using PHP?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I break the tie from my MySQL query using PHP?
    primarykey
    data
    text
    <p>I am creating a pageant scoring system using PHP ang MySQL or PhpMyAdmin in XAMMP. I have somehow manage to get the rankings of the candidates using MySQL but there are cases to get ties. How can I break the tie from the result of my query using PHP? </p> <p>The ranking of the candidates are according to their scores in a category where there are 5 judges and 9 candidates. So I really don't know what to do or how to solve it in PHP. </p> <p>What if I supposedly wanted C9 to be at the first rank instead of C4?</p> <p>Here is my Database... Hope someone can help me. Thanks in advance.</p> <pre><code>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 ); </code></pre> <p>This is my query:</p> <pre><code>select sum ,candidate_no ,@curRank := @curRank + 1 AS rank from( select sum(score) / 5 sum ,candidate_no from score where candidate_no = 'c1' union select sum(score) / 5 sum ,candidate_no from score where candidate_no = 'c2' union select sum(score) / 5 sum ,candidate_no from score where candidate_no = 'c3' union select sum(score) / 5 sum ,candidate_no from score where candidate_no = 'c4' union select sum(score) / 5 sum ,candidate_no from score where candidate_no = 'c5' union select sum(score) / 5 sum ,candidate_no from score where candidate_no = 'c6' union select sum(score) / 5 sum ,candidate_no from score where candidate_no = 'c9' ) a , (SELECT @curRank := 0) r order by sum desc </code></pre> <p>The Result would be..</p> <pre><code>sum | candidate_no | rank 18.8000 | C4 | 1 18.8000 | C9 | 2 17.8000 | C3 | 3 17.6000 | C5 | 4 17.2000 | C1 | 5 17.0000 | C6 | 6 16.8000 | C2 | 7 16.6000 | C8 | 8 13.8000 | C7 | 9 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USbaikoko09
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COJust to clarify - that's the desired result right? Even though candidates C4 and C9 have the same score, C4 wins the tie?
      singulars
      1. POHow can I break the tie from my MySQL query using PHP?
      1. USStrawberry
    2. COWhy are you using a UNION instead of `GROUP BY candidate_no`?
      singulars
      1. POHow can I break the tie from my MySQL query using PHP?
      1. USBarmar
    3. COMaybe you can add a `preference` column, set it to 1 if you want it to be the first in case of equal scores. Sort the scores on `preference`.
      singulars
      1. POHow can I break the tie from my MySQL query using PHP?
      1. USPieter
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload