How to update table row +1 when element is clicked?

Question

I am building a voting system for questions. Visitors of the site can vote, once a day or some such, on a question they like the most. How can I +1 to the QuestionVotes row when the button for the specific question is clicked?

My code:

<?php
    $connection = mysqli_connect('localhost', 'root', '', 'test');
    mysqli_set_charset($connection, 'utf8');
    if (!$connection) {
        die("Database connection failed: " . mysqli_error());
    }
    $sql = "SELECT QuestionHeader, QuestionText, QuestionVotes FROM question ORDER BY QuestionVotes DESC LIMIT 3";
    $result = $connection->query($sql);

    if ($result->num_rows > 0) {
         // output data of each row
         while($row = $result->fetch_assoc()) {
             echo "<div class=\"col-md-4\"><h2>". $row["QuestionHeader"]. "</h2><p>". $row["QuestionText"]. "</p><p><a class=\"btn btn-success\"> " . $row["QuestionVotes"] . "</a></p></div>";
         }
    } else {
         echo "0 results";
    }

    $connection->close();
?>

I would guess I have to store the QuestionID somehow and then retrieve it when the button is clicked, but I am clueless as to how? And how do I avoid people voting twice on the same question?

Answer 1

The biggest hurdle you will have is identifying unique users. The best way is to force registration and login. That's a discussion for another topic.

Regardless of that your table needs to have 2 other columns.

QuestionID MediumINT (15), Unsigned, Primary Index, Auto Increment. This should be the very first column.

QuestionVoters Text, NULL. This field will hold a json encoded array of userid's that have voted. array('123', '38', '27', '15')

In your While() loop check if the user's ID is in the QuestionVoters array.

If it exists, then don't give them a voting action. Otherwise build out a form using a button to submit to a processing page.

<?php
// Need to assign the user's ID to a variable ($userID) to pass to the form.
$userID = '123'; // this needs to be handled on your end.

// updated sql to include Id and voters
$sql = "SELECT QuestionID, QuestionHeader, QuestionText, QuestionVotes, QuestionVoters FROM question ORDER BY QuestionVotes DESC LIMIT 3";

while($row = $result->fetch_assoc()) {

    $voters = json_decode($row['QuestionVoters'], true); // array of userid's that have voted
    IF (in_array($userID, $voters)) {
        // user has voted
        echo "\n
        <div class=\"col-md-4\">
            <h2>". $row["QuestionHeader"]. "</h2>
            <p>". $row["QuestionText"]. "</p>
            <p>" . $row["QuestionVotes"] . "</p>
        </div>";
    }ELSE{
        // user has not voted
        echo "\n
        <div class=\"col-md-4\">
            <form action=\"vote_processing.php\" name=\"voting\" method=\"post\">
            <input type=\"hidden\" name=\"qid\" value=\"".$row['QuestionID']."\" />
            <input type=\"hidden\" name=\"userid\" value=\"".$userID."\" />
            <h2>". $row["QuestionHeader"]. "</h2>
            <p>". $row["QuestionText"]. "</p>
            <p><button type=\"submit\" value=\"Submit\">" . $row["QuestionVotes"] . "</button></p>
            </form>
        </div>";
    }

}
?>

vote_processing.php (example)

<?php
IF (isset($_POST['qid'])) {

    $qid = htmlspecialchars(strip_tags(trim($_POST['qid']))); // basic sanitization
    $userid = htmlspecialchars(strip_tags(trim($_POST['userid']))); // basic sanitization

    IF ( (is_int($qid)) && (is_int($userid)) ) { // validate that both are integers

        // db connection
        $connection = mysqli_connect('localhost', 'root', '', 'test');
        mysqli_set_charset($connection, 'utf8');
        if (!$connection) {
            die("Database connection failed: " . mysqli_error());
        }

        // Get voters array
        $sql = "SELECT QuestionVoters FROM question WHERE QuestionID = '".$qid."'";
        $result = $connection->query($sql);
        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
                IF (!empty($row['QuestionVoters'])) {
                  // decode users array
                  $voters = json_decode($row['QuestionVoters'], true);
                }ELSE{
                  $voters = array(); // create array
                }
            }
            mysqli_free_result($result);

            // re-validate the userID "is not" in array
            IF (!in_array($userid, $voters)) { // note the ! [meaning NOT].

                $voters[] = $userid; // add userid to voters array
                $qvoters = json_encode($voters); // encode voters array

                // update vote
                $sql_upd = "UPDATE question SET QuestionVotes = QuestionVotes + 1, QuestionVoters = $qvoters WHERE QuestionID = '".$qid."'";
                $upd_result = $connection->query($sql_upd);

            }

        }

        mysqli_close($connection);

    }

}

// redirct back to previous page
?>

Answer 2

Well, you will need to alter your DB table or create additional tables that links together and have a 1 to many relationship, the question table is the 1 and the table that stores each user's vote is the many side.

1. Each question should have a unique ID

2. Loop through the questions from the Questions table as you have above. Each row should have a button that when clicked passes the question ID + user ID/(IP Address - if the system is open to non registered users) to the user's vote table.

   2a. To increment the count each time a unique user clicks the vote button, you will have to Fetch to get a Count from the user's vote table to see how many times that Question ID exists.

3. But, before storing the data in the DB, do a check on the user's vote table to see if that user ID + Question ID is already matched, if so; return a message telling the user that they already voted on that question (Or you can get fancy and do a if check on the page, if there is a match - disable the vote button)

    $dbname = "DB HERE";
    $servername = "HOST HERE";
    $username = "DB USER HERE";
    $password = "DB PASSWORD HERE";
   
     // Create connection
     $conn = mysqli_connect($servername, $username, $password, $dbname);
   
                    if(isset($_GET['id']))
               {
                   ///Check to see if user already voted
                   $result = $conn->query("SELECT * FROM User_Votes where user id = $session_id and question_id = $id");
                   $row_cnt = $result->num_rows;
   
                   if($row_cnt < 1)
                   {
                       ///SQL to insert vote into Users Votes table
                   }else
                   {
                       //Vote already exists
                   }
   
               }
   
               // Loop through questions for voting
               $result = mysqli_query($conn,"select * from questions");
               while($db_questions = mysqli_fetch_object($result))
               {   
                   echo $db_questions->question_title;
                   echo '- <a href="mypage.php?id=$db_questions->question_id">Click to Vote</a>;
               }