How to insert data into another table from dynamic dropdown list

Question

I have two tables office1 and Office2

I want to insert the chosen from dropdown list into

table saveoffice

drop.php

    <!DOCTYPE html>

<html dir="rtl"
lang="ar">
<head>
<meta charset="utf-8">
    <title>dependent dropdown list using jquery Ajax?</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
    <link rel="stylesheet" href="http://demo.itsolutionstuff.com/plugin/bootstrap-3.min.css">
</head>
<body>
  <form action="savedata.php" name="myForm" method="POST"/>
<div class="container">
    <div class="panel panel-default">
      <div class="panel-heading">Select office 1 and get bellow Related office 2</div>
      <div class="panel-body">
      <div class="form-group">
                <label for="title">offices 1:</label>
                <select name="office1" id="office2" class="form-control">
                <option value="">--- choose office 1 ---</option>

                    <?php
                        require('config.php');
                        $sql = "SELECT * FROM office1";
                        $result = $con->query($sql);
                        while($row = $result->fetch_assoc()){
                            echo "<option value='".$row['id']."'>".$row['off1']."</option>";
                        }
                    ?>
                        </select>
                        </div>


                        <div class="form-group">
                        <label for="title">offices 2</label>
                        <select name="office2" id="office2" class="form-control" style="width:350px">
                        </select>

                        </div>
                      </br>
            <div class="col-md-4">
            <input type="submit" name="submit" value="Insert Data" class="btn btn-danger">
            </div>

      </div>

    </div>

</div>


<script>
$( "select[name='office1']" ).change(function () {
    var stateID = $(this).val();
    if(stateID) {
        $.ajax({
            url: "ajaxpro.php",
            dataType: 'Json',
            type:'POST',
            data: {'id':stateID},
            success: function(data) {
                $('select[name="office2"]').empty();
                $.each(data, function(key, value) {
                    $('select[name="office2"]').append('<option value="'+ key +'">'+ value +'</option>');
                });
            }
        });
    }else{
        $('select[name="office2"]').empty();
    }
});

</script>


</body>

</html>

ajaxpro.php

  <?php


   require('config.php');
   $sql = "SELECT * FROM office2
         WHERE off2_id LIKE '%".$_POST['id']."%'";
   $result = $con->query($sql);


   $json = [];
   while($row = $result->fetch_assoc()){
        $json[$row['id']] = $row['off2'];
   }
   echo json_encode($json);
?>

savedata.php < it does not work

   <?php
include('config.php')

$off1 = $_POST['$offices1'];
$off2 = $_POST['$offices2'];
$query = " INSERT INTO `saveoffice`(`id`, `offices1`, `offices2`) VALUES (0,".$office1.",".$offices2.") ";

    $query_execute=mysqli_query($con,$query);
          if($query_execute)
                    {
                          header("location:drop.php?q=success");
                    }

          else
                    {
                            echo mysqli_error($con);
                            header("location:drop.php?e=error");
                    }

?>

May I request what changes I need to make to savedata.php to insert data into the table saveoffices

score 0 · Answer 1 · answered Oct 14 '18 at 04:22

It looks like you're creating variables named $off1 and $off2 but you're using other variables in your SQL statement named $offices1 and $offices2:

$off1 = $_POST['$offices1'];
$off2 = $_POST['$offices2'];
$query = " INSERT INTO `saveoffice`(`id`, `offices1`, `offices2`) VALUES (0,".$office1.",".$offices2.") ";

Try changing $offices1 to $off1 and $offices2 to $off2.

score 0 · Accepted Answer · answered Oct 14 '18 at 06:06

1) Your form element names are offices1 and offices2, but you're using $offices1, $offices2 as your $_POST array keys. They should be offices1, offices2.

2) you assign your $_POST values to $off1, $off2, then build the query with $offices1, $offices2.

Follow all your variable names through the code and ensure they correspond. I think you're having some confusion between the array keys and the variable names. They are separate things. The $_POST array keys should match the name= elements in the form.

Also, and this is VERY important, you need to be sanitizing your input to the database. You're leaving yourself open to SQL injection attacks.

You need to use mysqli_real_escape_string on all user supplied variables. Additionally, you might consider validating the input further. Say, for example, you're expecting a number between 1 and 100. Check that you've received a number between 1 and 100, display an error otherwise.

Furthermore, there are a few other things to consider such as protecting against Cross Site Scripting (XSS). Here is another post that goes into detail on the subject.

What's the best method for sanitizing user input with PHP?

How to insert data into another table from dynamic dropdown list

2 Answers2