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

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>;
               }