PHP not inserting multiple data in MySQL table

Question

I am trying to build a multiple choice exam portal. It's working fine but when I am adding a question in my add.php file it get inserted but the choices of the question is not inserted in database

Here is my database:

Choices table:

choices table

Questions table:

questions table

Here is my code for add.php

<?php include 'includes/header.php'; ?>
<?php include 'config/config.php'; ?>
<?php include 'lib/Database.php'; ?>
<?php
$db = new Database();
if (isset($_POST['submit'])) {
    //Grab Post Data
    $question_number = $_POST['question_number'];
    $question_text = $_POST['question_text'];
    $correct_choice = $_POST['correct_choice'];
    $choices = array();
    $choices[1] = $_POST['choice1'];
    $choices[2] = $_POST['choice2'];
    $choices[3] = $_POST['choice3'];
    $choices[4] = $_POST['choice4'];

    //Insert question into database
    $query = "INSERT INTO `questions`(question_number, text) VALUES('$question_number','$question_text')";
    $insert_row = $db->insert($query);
    //validate
    if ($insert_row) {
        foreach ($choices as $choice => $value) {
            if ($value != '') {
                if ($correct_choice == $choice) {
                    $is_correct = 1;
                } else {
                    $is_correct = 0;
                }
                //Choice Query
                $query = "INSERT INTO `choices`(question_number,is_correct,text) VALUES('$question_number','$is_correct',
                             '$value')";
                //insert row
                $insert_row = $db->insert($query);
                if ($insert_row) {
                    continue;
                } else {
                    die($mysqli->error);
                }

            }
        }
    }
}
$query1 = "SELECT * FROM `questions`";
$result = $db->select($query1);
$total = $result->num_rows;
$next = $total + 1;
?>
 <form class="form-horizontal" action="signup.php" method="POST">
  <fieldset>
            <div id="legend">
              <legend class="text-center">Add Questions</legend>
            </div>
            <div class="control-group">
              <label class="control-label" for="username">Question Number</label>
               <div class="controls">
                <input name="question_number" value="<?php echo $next; ?>" placeholder="" class="form-control input-lg" type="number"/>
              </div>
            </div>
             <div class="control-group">
              <label class="control-label" for="text">Question Text</label>
              <div class="controls">
                <input name="text" placeholder="" class="form-control input-lg" type="text">
              </div>
            </div>
            <div class="control-group">
              <label class="control-label" for="choice1">#Choice 1</label>
              <div class="controls">
                <input  name="choice1" placeholder="" class="form-control input-lg" type="text">
              </div>
            </div>
            <div class="control-group">
              <label class="control-label" for="username">#Choice 2</label>
              <div class="controls">
                <input id="choice2" name="choice2" placeholder="" class="form-control input-lg" type="text">
              </div>
            </div>
            <div class="control-group">
              <label class="control-label" for="username">#Choice 3</label>
              <div class="controls">
                <input id="choice3" name="choice3" placeholder="" class="form-control input-lg" type="text">
              </div>
            </div>
            <div class="control-group">
              <label class="control-label" for="username">#Choice 4</label>
              <div class="controls">
                <input id="username" name="choice4" placeholder="" class="form-control input-lg" type="text">
              </div>
            </div>
            <div class="control-group">
              <label class="control-label" for="username">Correct Choice Number</label>
               <div class="controls">
                <input id="username" name="correct_choice" placeholder="" class="form-control input-lg" type="number"/>
              </div>
            </div>
            <input type="submit" name="submit" class="btn btn-block btn-primary" value="Submit" class="submit"/>
  </fieldset>         
 </form>
<?php include 'includes/footer.php';?>

Now only the question is adding in the database but not the choices.

Some odd stuff in here. Have you checked your `php error log` — RiggsFolly, May 11 '16 at 17:39
it is from database.php it is a function that perform select operation from the current query — Rohit Jain, May 11 '16 at 17:51
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. Using prepared statements will probably fix your escaping. — tadman, May 11 '16 at 18:02

ebahi · Answer 1 · 2016-05-11T18:01:12.130

0

The syntax appears to be correct, but you can delete simple quotes around numbers. In addition, it's better to make one request to insert all choices.

//validate
if ($insert_row) {
    $values = [];
    foreach ($choices as $choice => $value) {
        if ($value == '')
            continue;
        $is_correct = $correct_choice == $choice ? 1 : 0;
        $values[] = "($question_number, $is_correct, '$value')";
    }
    if (count($values) > 0)
    {
        $query = "INSERT INTO choices (question_number, is_correct, text) VALUES ".implode(',', $values);
        $insert_row = $db->insert($query);
        if (!$insert_row)
            die($mysqli->error);
    }
}

And your variables $value and $question_number come from $_POST variable, you have to use prepared statement to protect your query from SQL injection.

edited May 11 '16 at 18:01

answered May 11 '16 at 17:40

ebahi

536
2
7

well I tried this code now even the question are not inserting in database – Rohit Jain May 11 '16 at 17:59
Can you retry, I edit my post to replace ($value = '') to ($value == '') i'ts a typing error, sorry. – ebahi May 11 '16 at 18:03
nothing happens in the old code questions are being added to the database now both choices and questions are not added to the database – Rohit Jain May 11 '16 at 18:08
It's very curious... I don't know the source of the problem. Debug the variables of your program to know what's happened. – ebahi May 11 '16 at 18:17

PHP not inserting multiple data in MySQL table

1 Answers1