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,

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 21 '16 at 19:43
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 21 '16 at 19:43
Thanks for the recommendations I will change my code to PDO. — CyberFla, Apr 21 '16 at 19:46

David Wyly · Answer 1 · 2016-04-21T20:39:41.003

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

edited Apr 21 '16 at 20:39

answered Apr 21 '16 at 19:58

David Wyly

1,671
1
11
19

Thanks David. I will try this query and see the results. It makes sense. – CyberFla Apr 21 '16 at 20:06

Loop through database fields PHP MySQL

1 Answers1