Submit data into table based on certain input field in HTML form-CodePudding

I am currently creating a survey where the answers are entered into a database.

I have 2 main tables:

questions, with 2 columns: questionID and questionBody
answers, with 3 columns: answerID, questionID (I want this to be tied to the column in table questions) and answerBody.

On the HTML page I am planning to create there will be multiple questions with multiple text boxes to fill in correlating to each quesiton. Is it possible that when the person submits the form, the answers are inserted into table answers with the questionID being based on what field was filled out?

So for example, If I have questionBody as "What is this Question asking?" and the questionID as 1 in table questions, when I submit the form I want table answers to also have questionID 1 in there.

At the moment this is my code:

//Check if error variables have any values assigned
    if (empty($answerError))
    {
        //Prepare database insert
        $sql = "INSERT INTO answers (questionID, answerBody) VALUES (?,?)";

        //Check if the statement has the connect and sql variables
        if ($statement = mysqli_prepare($connect, $sql))
        {
            //Add variables to the statement
            mysqli_stmt_bind_param($statement, "ss", $paramQuestion, $paramAnswer);

            //Set the parameter to the answer
            $paramQuestion = getQuestionName($connect);
            $paramAnswer = $answer;
            

            //Execute statement with entered variable
            if (mysqli_stmt_execute($statement))
            {
                //Redirect user to success page
                header("location: thankyou.php");
            }
            else
            {
                echo "Something went wrong. Please try again later.";
            }

            //Close statement
            mysqli_stmt_close($statement);
        }
    }

and for the function getQuestionName():

function getQuestionName($connect)
{
    $query = "SELECT * FROM questions";
    $result = mysqli_query($connect, $query);

    if ($result)
    {
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
        {
            $questionID = $row['questionID'];

            return $questionID;
        }
    }   
}

The code I am using to output the form into a HTML page is:

function getQuestions($connect)
{
    $query = "SELECT * FROM questions";
    $result = mysqli_query($connect, $query);

    if ($result)
    {
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
        {
            $body = $row['questionBody'];


            echo '<div >
        <div ><h3>' . $body . '</h3>
        
        
        <form action="survey.php" method="POST">
            <input type="text" name="answer" size="50" />
            <input type="submit" value="Submit" name="submit" />
        </form>
        </div>
        </div>';
        }
    }

Any help on this would be greatly appreciated :)

CodePudding user response：

Yes it's completely possible. Just put the question ID as a hidden field in the form, and it will be submitted along with the answer data when the form is submitted. Then you can retrieve it from the $_POST data just like the answer, and use it in your SQL query.

For example:

HTML form:

<form action="survey.php" method="POST">
    <input type="hidden" name="questionID" value="<?php echo $row["questionID"]; ?>" />
    <input type="text" name="answer" size="50" />
    <input type="submit" value="Submit" name="submit" />
</form>

survey.php:

$paramQuestion = $_POST["questionID"];

CodePudding user response：

From your question, I will suggest you make use of input with a hidden attribute.

something like this

<input type='text' name='question-id' value="<?php echo $questionId ;?>" hidden>

The user doesn't see the input it get filled from whatever you are providing into it.

Editing your code, you should do something like this.

function getQuestions($connect)
{
    $query = "SELECT * FROM questions";
    $result = mysqli_query($connect, $query);

    if ($result)
    {
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
        {
            $body = $row['questionBody'];
            $questionId = $row['questionId'];

            echo '<div >
        <div ><h3>' . $body . '</h3>
        
        
        <form action="survey.php" method="POST">
            <input type="text" name="answer" size="50" />
            <input type="number"name="question-id" value="'.$questionId.'" hidden>
            <input type="submit" value="Submit" name="submit" />
        </form>
        </div>
        </div>';
        }
    }