Inserting values into database from form with drop downs

Question

I'm trying to insert values input from the user in a form into my database.

I am trying to create 2 drop down lists, with the first deriving the options for the second. For example the first drop down list for Faculty, with the second drop-down list containing the schools within the selected faculty.

I am also then wanting to insert the gathered information into my database however I can focus on that after getting the drop-down's correct first.

My register page is on one page with the getSchool.php on a different file, I have a feeling the connection between the two could be my issue.

The register.php is below. This is the page the form is on

 <?php
 session_start();

 include('dbConnect.php');

 $queryStr=("SELECT * FROM faculty");
  $dbParams=array();
   // now send the query
  $results  = $db->prepare($queryStr);
  $results->execute($dbParams);

 ?>
 <html>
 <head>
 <TITLE>Faculty & School</TITLE>
 <head>
 <!-- Help for code to create dynamic drop downs -->
 <script src="https://code.jquery.com/jquery-2.1.1.min.js" 
 type="text/javascript"></script>
 <script>
 function getFaculty(val) {
  $.ajax({
  type: "POST",
  url: "getFaculty.php",
  data:'facultyID='+val,
  success: function(data){
    $("#schoolList").html(data);
 }
  });
  }

  function selectFaculty(val) {
  $("#search-box").val(val);
  $("#suggesstion-box").hide();
 }
  </script>
  </head>
  <body>
  <div class="frmDronpDown">
  <div class="row">
  <label>Faculty:</label><br/>
  <select name="faculty" id="facultyList" class="demoInputBox" 
  onChange="getFaculty(this.value);">
  <option value="">Select Faculty</option>
  <?php
  foreach($results as $faculty) {
  ?>
  <option value="<?php echo $faculty["facultyID"]; ?>"><?php echo 
  $faculty["facultyName"]; ?></option>
  <?php
  }
  ?>
  </select>
  </div>
  <div class="row">
  <form action="addBlood.php" method="post">
  <label>Test:</label><br/>
  <select name="test" id="test-list" class="demoInputBox">
  <option value="">Select Test</option>
  </select>
  </div>
  </div>

  <label>Result:</label><input class="input" name="result" type="text"><br>
  <label>Date:</label><input class="input" name="date" type="date"><br>
  <input class="submit" name="submit" type="submit" value="Submit">
  </form>

Below is the getSchool.php which gets all the schools

 <?php
 include('dbConnect.php');


 if(!empty($_POST["facultyID"])) {
 $queryStr=("SELECT * FROM school WHERE facultyID = '" . $_POST["facultyID"] 
 . "'");
 $dbParams=array();
 // now send the query
 $results  = $db->prepare($queryStr);
 $results->execute($dbParams);

 ?>
 <option value="">Select School</option>
 <?php
foreach($results as $school) {
?>
<option value="<?php echo $school["schoolID"]; ?>"><?php echo 
$school["schoolName"]; ?></option>
<?php
}
}
?>

Thanks in advance for any feedback and help.

Simon

Please be aware that your code is **vulnerable** to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection). Never use `$_POST` directly in SQL statements! You should use [**prepared statements**](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead, binding to variables. Also ensure that your database user only has the [**required privileges**](https://en.wikipedia.org/wiki/Principle_of_least_privilege). You can refer to [**this post**](http://stackoverflow.com/questions/60174) for further information on how to prevent SQL injection in PHP :) — Obsidian Age, Apr 04 '18 at 21:41
My question @smith is regards to why my list of schools are not displaying in my second drop down. For example. Clicking on the Business Faculty should bring up options such as the School of Marketing and so. — Simon C, Apr 04 '18 at 21:46
There seem to be some overarching organizational issues here. You have references to elements that don't exist, i.e. `#schoolList`, `#search-box`, and `#suggestion-box`. I'd also update your naming convention so that it reflects what's happening in the code. — Kang, Apr 04 '18 at 22:29

score 0 · Answer 1 · 2018-04-04T22:32:36.160

0

   url: "getFaculty.php",
   data:'facultyID='+val,
    success: function(data){
     $("#schoolList").html(data);

Where is the #schoolList element ? Why getFaculty.php? Should it not be getSchool.php ?

edited Apr 04 '18 at 22:32

answered Apr 04 '18 at 21:55

Thanks for your response @Jean-Marc Leinher, I have amended it to 'getSchool.php' however would you mind elaborating on the #schoolList widget? Very new to this. – Simon C Apr 04 '18 at 22:06
`getFaculty.php` is actually correct there. The call to that function is, in fact, returning the different faculty names. `#schoolList` refers to a portion of your page which doesn't seem to exist. – Kang Apr 04 '18 at 22:12
Actually, you were right. The whole naming convention is just off, so it tricked me. It should be getSchool, but the function name should probably be changed as well. – Kang Apr 04 '18 at 22:23

score 0 · Answer 2 · answered Apr 04 '18 at 22:59

First, just to re-iterate what was already mentioned, update your getFaculty() to get getSchool() and make sure it points to getSchool.php.

Now, you need to create a div following your first drop-down with an id schoolList.

<div class="row" id="schoolList"></div>

Now, update your getSchool.php so that it generates the full form/selection. Something along the lines of:

<?php
include('dbConnect.php');


if(!empty($_POST["facultyID"])) {
$queryStr=("SELECT * FROM school WHERE facultyID = '" . $_POST["facultyID"] 
. "'");
$dbParams=array();
// now send the query
$results  = $db->prepare($queryStr);
$results->execute($dbParams);

?>
<label>Schools:</label><br/>
<select name="schoolSelect" id="schoolSelect" class="demoInputBox">
<option value="">Select School</option>
<?php
foreach($results as $school) {
?>
<option value="<?php echo $school["schoolID"]; ?>"><?php echo 
$school["schoolName"]; ?></option>

Once you've got those ideas down, you'll have to make sure you have the full flow of your page the way you want it. Then follow similar standards for posting any inputs to the php page you use for database manipulation.

As noted in earlier posts, this solution still leaves you vulnerable to injection. That's for another post, another day.

Inserting values into database from form with drop downs

2 Answers2