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?

yardie · Accepted Answer · 2015-12-07T19:52:16.810

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.

Each question should have a unique ID
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.

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

edited Dec 07 '15 at 19:52

answered Dec 07 '15 at 19:15

yardie

1,583
1
14
29

Thank you for the well-described answer! I understand your solution and creating a user table and the relation between the two tables will be easy. Problem is though, that I am very new to PHP and honestly don't quite know how I would go around looping through the questions from the question table, using fetch to get a count or even checking if User ID and Question ID is already matched, Could you possible specify in deeper detail so that I can hopefully figure it out? Again, thanks for the answer. – Nenn Dec 07 '15 at 19:35
@user2304993 - I will edit my answers with examples. I did not test the code I provided for syntax errors - there are many ways to skin a cat, so it can be re-written better to be more secured and efficient. But... It gives you the basis of what you need to do. I would advise going to php.net/manual/en/book.mysqli.php and do some reading. – – yardie Dec 07 '15 at 19:52
Thank you for all your time, I am trying to get through this slow and steady. I am though, confused as to what exactly you get from the 'if(isset($_GET['id']))'? And where the question_id / $id comes from? Also, I have followed your link and am reading up on the things you have mentioned, thanks! – Nenn Dec 07 '15 at 20:32
@user2304993 - `if(isset($_GET['id']))` is checking to see if the user clicked the vote button, the `id` variable will contain the `question ID` for the question that the user clicked vote on (visible in the browser URL) - and you will create a table called `users_vote` and a column inside there should be `question_id` so when the user clicks vote, you will insert the `id` into the `question_id` column, so onn and so forth. – yardie Dec 07 '15 at 20:36
Ah, okay, I get that now. There's still one more thing that I am a little unclear on though, where in the progress should I use my original piece of code to generate the html version of the questions and buttons? Cause you build the href while looping through the questions? – Nenn Dec 07 '15 at 20:55
@user2304993 - yes, You can replace my `// Loop through questions for voting` bit, with yours. It does essentially the same thing; Generate html + button for each question. – yardie Dec 07 '15 at 20:59
Okay, last question, I promise! Where does the $session_id come from? And don't I need to declare it first? Also, thank you loads for your help again! – Nenn Dec 07 '15 at 21:21
@user2304993 - Assuming the voting users are registered, then the `$session_id` would store their `unique ID` from the users table. If you do not have a registration system, (Users are guests) - then you can store their IP address in the `$session_id` variable in order to identify them uniquely. – yardie Dec 08 '15 at 13:10
1

And I would store their IP address with $_SERVER['REMOTE_ADDR'] right? Again thank you, I think I nearly have my own version working. – Nenn Dec 08 '15 at 17:13

Brian · Answer 2 · 2015-12-07T21:17:02.550

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
?>

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

2 Answers2