使用php更新数据库中正确行的问题

Question

I'm trying to create a voting system for artists played on my radio station. I'm using the source code from: http://dl.howcode.org/download/97ff383c7d4dc9939c65c9e6fab2a5dc

The problem I have found is that the votes update using the number from the first row in the database no matter which option is selected, thus if for instance the first row has 3 votes in and the user tries to vote on someone with 0 votes, it will change the votes for the correct artist to 4 instead of 1... I hope that makes sense?

The code I have is:

[EDIT] I have changed the queries to fetch assoc to make it easier to understand.

<?php

    $voteID = $_GET['voteID'];
    $connect = mysqli_connect('xxx', 'xxx', 'xxx', 'xxx');
    $query = "SELECT * FROM listenervotes WHERE voteID='$voteID'" ;
    $q = mysqli_query($connect, $query);
    while($row = mysqli_fetch_assoc($q)){
        $id = $row["id"];
        $voteTitle = $row["voteTitle"];
        $voteID = $row["voteID"];
        $ipaddress = $row["ipAddress"];
        echo "<h3>$voteTitle</h3>";
        ?>
        <table>
            <form action="" method="POST">
        <?php
            $artists = "SELECT * FROM artists WHERE voteID='$voteID'" ;
            $q2 = mysqli_query($connect, $artists);
            while($r = mysqli_fetch_assoc($q2)){
                $artist = $r["artistName"];
                $votes = $r["votes"];
                $genre = $r["genre"];
                $ip = $_SERVER['REMOTE_ADDR'];
                $newIpAddress = $ipaddress."$ip, ";
                $newVotes = $votes + 1;
                if (isset($_POST['vote'])) {
                    $voteOption = $_POST['voteOption'];
                    if ($voteOption == ""){
                        die("You haven't selected anyone!");
                    }else{

                        $ipaddressE = explode(",", $ipaddress);
                        if(in_array($ip, $ipaddressE)){
                            die("You have already voted!");
                        }else{
                            mysqli_query($connect, "UPDATE artists SET votes='$newVotes' WHERE voteID='$voteID' AND artistName='$voteOption'");
                            mysqli_query($connect, "UPDATE listenervotes SET ipaddress='$newIpAddress' WHERE voteID='$voteID'");
                            die('You voted successfully!<br><tr><td>'.$artist.'</td><td>'.$genre.'</td><td>'.$votes.' Votes</td></tr>');
                        }
                    }
                }
                echo '<tr><td>'.$artist.'</td><td>'.$genre.'</td><td><input type="radio" name="voteOption" value="'.$artist.'"</td></tr>';
            }

    }
?>

I could be missing something obvious, in my mind I'm thinking that I somehow need to iterate through the rows before setting the new value, if so, how and where?

Answer 1

It looks like you are always looping over all rows and updating the relevant row with the first value found. Adding a check on the ID should do:

<?php

$voteID = $_GET['voteID'];
$connect = mysqli_connect('xxx', 'xxx', 'xxx', 'xxx');
$query = "SELECT * FROM listenervotes WHERE voteID='$voteID'" ;
$q = mysqli_query($connect, $query);
while($row = mysqli_fetch_assoc($q)){
    $id = $row["id"];
    $voteTitle = $row["voteTitle"];
    $voteID = $row["voteID"];
    $ipaddress = $row["ipAddress"];
    echo "<h3>$voteTitle</h3>";
    ?>
    <table>
        <form action="" method="POST">
    <?php
        $artists = "SELECT * FROM artists WHERE voteID='$voteID'" ;
        $q2 = mysqli_query($connect, $artists);
        while($r = mysqli_fetch_assoc($q2)){
            $artist = $r["artistName"];
            $votes = $r["votes"];
            $genre = $r["genre"];
            $ip = $_SERVER['REMOTE_ADDR'];
            $newIpAddress = $ipaddress."$ip, ";
            $newVotes = $votes + 1;
            if (isset($_POST['vote'])) {
                $voteOption = $_POST['voteOption'];
                if ($voteOption == ""){
                    die("You haven't selected anyone!");
                }else{

                    $ipaddressE = explode(",", $ipaddress);
                    if(in_array($ip, $ipaddressE)){
                        die("You have already voted!");
                    }elseif ($voteOption === $artist) { // Don't run UPDATE when we're on the wrong row.
                        mysqli_query($connect, "UPDATE artists SET votes='$newVotes' WHERE voteID='$voteID' AND artistName='$voteOption'");
                        mysqli_query($connect, "UPDATE listenervotes SET ipaddress='$newIpAddress' WHERE voteID='$voteID'");
                        die('You voted successfully!<br><tr><td>'.$artist.'</td><td>'.$genre.'</td><td>'.$votes.' Votes</td></tr>');
                    }
                }
            }
            echo '<tr><td>'.$artist.'</td><td>'.$genre.'</td><td><input type="radio" name="voteOption" value="'.$artist.'"</td></tr>';
        }

}

?>