CODEWITHSUNDEEP
phpmysqlsqlhtml
MJob
MJob

Reputation: 33

how to sort MySQL data according to values calculated outside the database

i have 2 MySQL tables: polls and candidates.candidates table stores name of candidates and position they vying for i.e candidates(candidate_name,Position) The polls table stores every poll made by user while voting. The polls table structure is polls(candidate_name,Position). Assuming there is only one position of chairman and the candidates are 3 ; x,y and z. when a user votes for x, a row in table polls is filled with value (x,chairman). assuming there are 6 ballots cast and the table will look like this. 

|y | Chairman|
|x | Chairman|
|z | Chairman|
|x | Chairman|
|y | Chairman|
|x | Chairman|

From this table, i want to display every candidate in it and the number of votes gained in a html table. the code to do this is 

$cnameres=mysqli_query($conn,"SELECT candidate_name FROM candidates WHERE Position ='Chairperson'");

            while($cname = mysqli_fetch_array($cnameres))
            {

                $v = $cname['candidate_name'];

                //total votes for each candidate

                $cand_ballot_count = mysqli_fetch_array(mysqli_query($conn, "SELECT COUNT(Position) FROM polls WHERE Position ='Chairperson' and candidate = '$v'"));
                $y = $cand_ballot_count[0] ;        

            //echo candidate and votes

              echo "<tr align='left'>"; 
                echo"<td>" .$v ."</td>";
                echo"<td>". $y. "</td>";

            }

The result will be a html table called result table that looks like this. 

| y | 2 |
| x | 3 |
| z | 1 |

My question; how can i sort this data so that it could echo on the result table while sorted in a descending order like this. 

| x | 3 |
| y | 2 |
| z | 1 |

remember the value to be sorted is not in the database. its just a result of counting rows.

Upvotes: 0

Views: 67

Answers (2)

Samir Selia
Samir Selia

Reputation: 7065

You can JOIN the 2 queries into one like below

SELECT c.candidate_name, count(*) AS votes
FROM candidates c 
JOIN polls p ON c.candidate_name = p.candidate_name
WHERE c.Position = 'Chairperson'
GROUP BY c.candidate_name
ORDER BY votes DESC;

Here is the updated code snipped

$sql = "
SELECT c.candidate_name, count(*) AS votes
FROM candidates c 
JOIN polls p ON c.candidate_name = p.candidate_name
WHERE c.Position = 'Chairperson'
GROUP BY c.candidate_name
ORDER BY votes DESC;
";

$cnameres = mysqli_query($conn, $sql);

while($cname = mysqli_fetch_array($cnameres))
{

    $v = $cname['candidate_name'];

    //total votes for each candidate
    $y = $cname['votes'];

    echo "<tr align='left'>"; 
    echo"<td>" .$v ."</td>";
    echo"<td>". $y. "</td>";
    echo "<tr/>";
}

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You should be counting the rows in the database. Then the ordering is trivial:

SELECT c.candidate_name, COUNT(p.candidate_name) as num_votes
FROM candidate c LEFT JOIN
     polls p
     ON c.candidate_name = p.candidate_name
WHERE c.Position = 'Chairperson'
GROUP BY c.candidate_name
ORDER BY COUNT(*) DESC;

I am not sure if the join is necessary. You have position_name and position redundantly in both tables.

Upvotes: 1

Related Questions