Loop through database fields PHP MySQL

Question

I am working on an election system and I am having difficulty solving a problem. Following is how my application looks like.

At the moment I get these results by adding the query multiple times on my php file. (for example run it where RaceID = 9, RaceID = 10 .........) I am sure there should be a way of reading the RaceID as an array or some other way and get the results that way. Since these are JOIN tables I am also looking for a way of retrieving the RaceName (Presidential Names, Justice Supreme Court ... etc) and MainRaceName(Titles with red, blue, gray) as well. I hope I am making some sense so far... Following is my code for

<!-- MAIN ELECTION TICKET  MainID loop should control this -->
<div class="panel panel-red margin-bottom-40">
<div class="panel-heading">
    <h2 class="panel-title"> <strong>REPUBLICAN NATIONAL</strong>  </h2>
</div>

<!-- End then SUB ELECTION TICKET    RaceID loop should control this section-->
<h3 style="background-color:#f5f5f5; margin:30px; padding:5px; font-weight:Bold ">Presidential Race  </h3>


<!-- Finally Results section ELECTION RESULTS -->
<table class="table table-hover">
    <thead>
    <tr>
        <th></th>
        <th>Candidate</th>
        <th>Votes</th>
        <th>%</th>
    </tr>
    <!-- This area gets the sum of the votes for a specific RaceID -->
    <?php
    $result = mysql_query('SELECT SUM(CandidateVotes) AS value_sum FROM candidates WHERE RaceID =9');
    $row = mysql_fetch_assoc($result);
    $sum = $row['value_sum'];
    ?>
    </thead>
    <tbody>
    <?php
    $query = "SELECT candidates.CandidateName, candidates.CandidateVotes, candidates.Party, mainrace.MainRaceName, race.RaceName, candidates.win
                                        FROM candidates
                                        JOIN race ON race.RaceID = candidates.RaceID
                                        JOIN mainrace ON mainrace.MainID = candidates.MainID
                                        WHERE candidates.RaceID = 9";

    $result = mysql_query($query) or die(mysql_error());
    for($i=0; $row = mysql_fetch_array($result); $i++){
        ?>

        <tr>
            <!--Show winner Icon if the win field is selected as 1 from database -->
            <td width="5px">
                <?php if ($row['win']==1)
                {

                    echo "<span class=\"glyphicon glyphicon-check\"></span>";
                }

                else
                {
                    echo "<span class=\"glyphicon glyphicon-unchecked\" style=\"color:#cccccc\"></span>";
                }
                ?>


            </td>

            <td><?php if ($row['win']==1){
                    echo "<strong>";
                    echo $row['CandidateName'];
                    echo "</strong>";
                }
                else {
                    echo $row['CandidateName'];
                }

                ?>
            </td>

            <td width="20%"><?php echo $row['CandidateVotes']; ?></td>
            <td width="30%"><?php
                if ($row['CandidateVotes']>0){
                    echo number_format((float)(($row['CandidateVotes']/$sum)*100), 2, '.', ''). ' %';
                }

                else{
                    echo "N/A";
                }
                ?>
            </td>

        </tr>
        <?php
    }
    ?>

    <tr>
        <td></td>
        <td><strong>Total Votes:</strong></td>
        <td><strong><?php echo $sum ?></strong></td>
        <td></td>
    </tr>

    </tbody>

</table>

I really appreciate if you can provide some samples (loops) how I can resolve this problem. Your help is much appreciated. Sincerely,

Answer 1

You should be able to pull down everything you need in one query.

In order to sum up votes by candidate, you need to make sure you GROUP BY the candidate name.

Try something like this:

SELECT
  SUM(candidates.CandidateVotes) AS value_sum,
  candidates.CandidateName, 
  candidates.Party,
  mainrace.MainRaceName,
  race.RaceName,
  candidates.win
FROM candidates
JOIN race 
  ON race.RaceID = candidates.RaceID
JOIN mainrace
  ON mainrace.MainID = candidates.MainID
WHERE candidates.RaceID = :raceId
GROUP BY candidates.CandidateName