Populate datalist with mysql fields based on input from another field without reloading page

Question

I am trying to set up a datalist tag that uses fields from my database based on the input from the first field on the form.

I am trying to use AJAX to send the value in the first field (Builder) into my PHP file which will run a query to pull all the records based on that value. Then it echos the options into the datalist for all the contactnames available under that Builder.

<form id="newsurveyform" method="post" action="submitnewsurvey.php">
        ID: <input type="text" name="ID" readonly value=<?php print $auto_id; ?>>&nbsp;
Builder:<b style="color:red">*</b> <input list="builders" name="builders" onchange="findcontactnames(this.value)" required>
    <datalist id="builders">
    <?php 
        while ($builderinforow = mysqli_fetch_assoc($builderinfo)){
        echo'<option value="'.$builderinforow['Builder'].'">';
    }
    ?>
    </datalist>



       Contact Name:
       <input list="contactnames" name="contactnames" required>
        <datalist id="contactnames">
        <div id="contactnamesoptions"> </div>
        </datalist>
       </form>



        <!-- AJAX, send builder value to php file-->        
        <script>
        function findcontactnames(str) {
        if (str.length==0){
           document.getElementById("contactnames").innerHTML = "";
        return;}
        else{
        var xmlhttp = new XMLHttpRequest(); 
        xmlhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("contactnamesoptions").innerHTML = this.responseText;
            }};
        xmlhttp.open("POST", "contactnames.php", true)
        xmlhttp.send(str);
           }
           } 
        </script>   
<?php
include 'dbconnector.php';

$buildername = $_POST["Builder"];

$contactnames = $conn->query("SELECT ContactName FROM SurveyLog'.$buildername.'");

while ($contactnamerow = mysqli_fetch_assoc($contactnames)){
          echo'<option value="'.$contactnamerow['ContactName'].'">';

}
  ?>

Once the user enters the builder, they will click the contact name and it should be populated with all the contact names under that builder.

Right now, it is not populating anything into the contact name field.

Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, May 29 '19 at 19:50

CodeJunkie · Answer 1 · 2019-05-29T21:10:56.467

In index.php

  <form id="newsurveyform" method="post" action="submitnewsurvey.php">
    ID: <input type="text" name="ID" readonly value=<?php print $auto_id ?? 0; ?>>&nbsp;
    Builder:<b style="color:red">*</b> <input list="builders" name="builders" onchange="findcontactnames(this.value)" required>
    <datalist id="builders">
      <?php
        while ($builderinforow = mysqli_fetch_assoc($builderinfo)){
          echo'<option value="'.$builderinforow['Builder'].'">';
        }
      ?>
    </datalist>

    Contact Name:
    <input list="contactnames" name="contactnames" required>
    <datalist id="contactnames">
      <div id="contactnamesoptions"> </div>
    </datalist>
  </form>

  <!-- AJAX, send builder value to php file-->
  <script>
    function findcontactnames(str) {
      if (str.length==0){
        document.getElementById("contactnames").innerHTML = "";
        return;
      } else {
        var xmlhttp = new XMLHttpRequest();
        xmlhttp.onreadystatechange = function() {
          console.log(this);
          if (this.readyState == 4 && this.status == 200) {
            alert('Fired');
            document.getElementById("contactnamesoptions").innerHTML = this.responseText;
            // document.getElementById("contactnamesoptions").innerHTML = "working";
          }
        };
        xmlhttp.open("POST", "response.php", true)
        xmlhttp.send(str);
       }
     }
  </script>

in response.php

<?php echo "test"; ?>

Once you've verified that works, query the database

EDIT: Now that you've verified that, query the database in response.php

include 'dbconnector.php';
  $buildername = $_POST["Builder"];
  $contactnames = $conn->query("SELECT ContactName FROM SurveyLog WHERE `columnName` LIKE '.$buildername.' LIMIT 100");
  $queryHTML = '';
  $result = 0;
  while ($contactnamerow = mysqli_fetch_assoc($contactnames)){
    $result = 1;
    $queryHTML .= '<option value="'.$contactnamerow['ContactName'].'">';
  }
  if ($result == 1) {
    echo $queryHTML;
  } else {
    echo 'No Builders found';
  }

Note: Change the columnName in the query to your real column name

In contactnames.php echo "test"; and nothing else. Use chrome/moz developer tools->network to make sure you're posting and receiving the correct data — CodeJunkie, May 29 '19 at 20:11
I'm not getting any network errors. The fired alert worked, readystate showed as 4. But the response text came out as "test" — pao0720, May 29 '19 at 20:57
Keep in mind! this is very susceptible to SQL injection, escape strings on the $_POST['Builder'] string on this line $buildername = $_POST["Builder"]; — CodeJunkie, May 29 '19 at 21:13
Consider this https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — CodeJunkie, May 29 '19 at 21:48
@pao0720 yep! Just remembered to close the question as answered — CodeJunkie, May 30 '19 at 14:04

Populate datalist with mysql fields based on input from another field without reloading page

1 Answers1