PHP/MySQL - Post/Question approval system?-CodePudding

So, I'm making a trivia game--and I have a page where users can submit their own trivia questions which will later be put into games (separate part of the game that isn't too relevant here). Anyway, so prevent spam, and irrelevancy, or false submitting/trolls, I'm making a moderator approval page; this page displays individual "pending" questions that users submit. (When they submit a question, it goes into a "pending" database table called 'usertriviadata'.

Then, it displays each of the pending questions on the moderator approval page, with a submit button where a mod/admin can approve it.

Step by step this is how it works:

The page displays (per trivia category) each individual pending question on the approval page, each one has a submit button.
A moderator can view it, and if they want to approve it, they click the "approve" button.
If the "approve" button is clicked, the system deletes the question from the "pending" 'usertriviadata' table, and inserts it into the 'approved' database table where I will use the data in that table for later.

The problem I'm having, and can't quite figure out how to fix--when I click the approve button, it approves ALL/ANY pending questions in that particular category. Let's say there is 3 pending questions in the "geography" category. I click approve on any of those 3 questions, and it approves all of them. Basically, the deletion and insertion (swapping data between the two database tables) works, but I want to individualize it. I've tried a few different things, but I can't quite get it right.

Any suggestions? The code is below this image (image shows the approval page to get a general idea of what it looks like when there are multiple pending questions):

[enter image description here][1]


    <div >
    <h3>Geography</h3>
    <?php
    $sql = "SELECT questionID, category, uploaderUsername, question, correctAnswer, answerTwo, answerThree, answerFour FROM usertriviadata WHERE category='geography'";
    $result = $conn->query($sql);
    
    
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
    
            $questionID = $row['questionID'];
            $category = $row['category'];
            $uploaderUsername = $row['uploaderUsername'];
            $question = $row['question'];
            $correctAnswer = $row['correctAnswer'];
            $answerTwo = $row['answerTwo'];
            $answerThree = $row['answerThree'];
            $answerFour = $row['answerFour'];
            echo "<div class='individuals'><p>Question ID: $questionID</p> <p>Category: $category</p> <p>Uploader Username: $uploaderUsername</p> <p>Question: $question</p> <p>Correct Answer: $correctAnswer</p> <p>Answer 2: $answerTwo</p> <p>Answer 3: $answerThree</p> <p>Answer 4: $answerFour</p> <form action='' method='GET'><input type='submit' name='submit' value='Approve'/></form> </div> ";
            
            if (isset($_GET["submit"])) {
    
                // Move question to approved table
                $sql = "INSERT INTO approved (category, uploaderUsername, question, correctAnswer, answerTwo, answerThree, answerFour)
                VALUES ('$category', '$uploaderUsername', '$question', '$correctAnswer', '$answerTwo', '$answerThree', '$answerFour')";
                // Error Handles
                if ($conn->query($sql) === TRUE) {
                    echo "<p class='green'>Question approved.</p>";
                } else {
                    echo "Error: " . $sql . "<br>" . $conn->error;
                }
    
                // Delete question from pending/usertriviadata table
                $sql = "DELETE FROM usertriviadata WHERE questionID='$questionID'";
                if ($conn->query($sql) === TRUE) {
                    echo "<p class='green'>Question removed from pending/usertriviadata database table. Please wait 5 seconds before approving another post.</p>";
                    echo "<meta http-equiv='refresh' content='5; URL=../triviaApproval/moderatorApproval' />";
                } else {
                    echo "Error: " . $sql . "<br>" . $conn->error;
                }
                
            }
    
    
        }
      } else {
        echo "No posts need approved here.";
      }
      
    
    ?>
    </div>```
    
    If you want the CSS as well, it is here:
    
    ```html {
        background-color: #AC6A6C;
        font-family: "Trirong", serif;
        color: #DEF706;
        text-align: center;
    }
    .option-a {
        text-align: center;
        display: inline;
        background-color: gray;
        color: #DEF706;
        font-size: 125%;
        width: 100px;
        padding: 0.5%;
        text-decoration: none;
    }
    .option-a:hover {
        cursor: pointer;
        background-color: #4E4E4D;
    }
    .option-a:focus {
        padding: 0.3%;
        background-color: blue;
    }
    .individuals {
        padding: 1%;
        display: inline-block;
        border: 1px solid;
    }
    .green {
        color: green;
    }

```[enter image description here][2]

CodePudding user response：

You have this, a separate form for each question pending approval:

<form action='' method='GET'>
  <input type='submit' name='submit' value='Approve'/>
</form>
<form action='' method='GET'>
  <input type='submit' name='submit' value='Approve'/>
</form>
<form action='' method='GET'>
  <input type='submit' name='submit' value='Approve'/>
</form>

But note they're all the same. There's no ID to indicate which form represents which question. Pressing any one of these buttons is going to do the same thing. In the script that handles the submit, you run this query:

    $sql = "SELECT ... FROM usertriviadata WHERE category='geography'";

Note there's no WHERE clause to limit the select to a single question. So, you're using the same query to render the question list as you are to approve. It seems that you're trying to use if (isset($_GET["submit"])) { inside the question loop as a condition to determine which of the questions was selected. However, this can't work. There is only one $_GET["submit"] -- there is not one per question. So clicking any one of the form buttons is going to copy all of the questions.

You're confusing yourself by having one script do both the form rendering and the form processing. I'd separate those two functions for simplicity -- have one file render the list and a second file process the form submit. So, your form render would do something like this to include the question id in each form:

<form action='approve.php' method='POST'>
  <input type='hidden' name='questionId' value='<?= $questionID =?'>
  <input type='submit' name='submit' value='Approve'/>
</form>

And then, in approve.php, use $_POST['questionId'] in your WHERE clause to process the activation.

Other recommendations:

Use POST for form submits, not GET. In general, if the request results in a change to the database, it should be a POST.
If you build a form that has one checkbox per question (instead of one whole form per question) then you can allow multiple questions to be approved at once.
Fix your SQL injection now. Shrugging it off to "I'll just fix it later" is a really bad habit, especially when it's no harder to just do it correctly from the start.

CodePudding user response：

EDIT: No it doesn't. It's a step I feel in the right direction but it's still messed up. Taking a break on this issue. I'm so frustrated.

Okay, so... this kind of falls under what you were saying, and I'm not sure if this is exactly what you were getting me to do, BUT, I did this and it works flawlessly.


    <div >
    <h3>Geography</h3>
    <?php
    $sql = "SELECT questionID, category, uploaderUsername, question, correctAnswer, answerTwo, answerThree, answerFour FROM usertriviadata WHERE category='geography'";
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
    
            $questionID = $row['questionID'];
            $category = $row['category'];
            $uploaderUsername = $row['uploaderUsername'];
            $question = $row['question'];
            $correctAnswer = $row['correctAnswer'];
            $answerTwo = $row['answerTwo'];
            $answerThree = $row['answerThree'];
            $answerFour = $row['answerFour'];
            echo "<div class='individuals'><p>Question ID: $questionID</p> <p>Category: $category</p> <p>Uploader Username: $uploaderUsername</p> <p>Question: $question</p> <p>Correct Answer: $correctAnswer</p> <p>Answer 2: $answerTwo</p> <p>Answer 3: $answerThree</p> <p>Answer 4: $answerFour</p> <form action='' method='POST'><input type='hidden' name='questionId' value='<?= $questionID =?'><input type='submit' name='submit' value='Approve'/></form> </div> ";
        }
      } else {
        echo "No posts need approved here.";
      }
    ?>
    </div>

At the bottom of the page:


    <?php
    
    if (isset($_POST["submit"])) {
    
        // Move question to approved table
        $sql = "INSERT INTO approved (category, uploaderUsername, question, correctAnswer, answerTwo, answerThree, answerFour)
        VALUES ('$category', '$uploaderUsername', '$question', '$correctAnswer', '$answerTwo', '$answerThree', '$answerFour')";
        // Error Handles
        if ($conn->query($sql) === TRUE) {
            echo "<p class='green'>Question approved.</p>";
        } else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
    
        // Delete question from pending/usertriviadata table
        $sql = "DELETE FROM usertriviadata WHERE questionID='$questionID'";
        if ($conn->query($sql) === TRUE) {
            echo "<p class='green'>Question removed from pending/usertriviadata database table. Please wait 5 seconds before approving another post.</p>";
            echo "<meta http-equiv='refresh' content='5; URL=../triviaApproval/moderatorApproval' />";
        } else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
        
    }
    
    
    
    $conn->close();
    ?>

Now I'll work on SQL injection.