get select menu to dynamically show specific select option based of MySQL SELECT result

Question

I have a php page where users type in a specific id number in a text field and click a "SEARCH" button. Upon clicking "SEARCH", a php script runs to connect to a MySQL Database table "xxx"and grab the row id that matches the id number entered by the user. The SELECT statement grabs the database values: "productionstage" and "floornotes" for the identified row.

What I need to do is take those results and display them back on my form page:

A select menu needs to dynamically display the option corresponding to the "productionstage" value for the row and then a textarea needs to display the value from "floornotes".

MY CODE:

HTML:

<form id="workorderMovement" name='workorderMovement_form' action="workordermovementGET.php" method="post">



<fieldset id="userid">

<span>Welcome <?php echo $user ?> </span>

</fieldset> 




<fieldset id="sgnum">

<fieldset id="fieldset" style="text-align: center;"> 
    <span>Please enter the SG Number</span>
</fieldset>

<input type="text" name="sgnumber" id="sgnumber"> &nbsp;&nbsp;&nbsp; <input type="button" name="searchButton" id="searchButton" value="SEARCH">

</fieldset> 


<br/>
<br/>



<fieldset id="stageSelectField">

  <fieldset id="fieldset" style="text-align: center;"> 
    <span>Please select the Stage Completed</span>
  </fieldset>

<select name="stageSelect" id="stageSelect">
    <option value="Please Select">Please Select</option>
    <option value="Film Done">Film Done</option>
    <option value="Staged Done">Staged Done</option>
    <option value="Cleanroom Done">Cleanroom Done</option>
    <option value="GB2 Done">GB2 Done</option>
    <option value="Bagging Done">Bagging Done</option>
    <option value="Inspection Done">Inspection Done</option>
    <option value="LC Done">LC Inspection Done</option>
    <option value="IGU Done">IGU Done</option>
</select>

</fieldset> 


<br/>
<br/>


<fieldset id="floorNotesField">

  <fieldset id="fieldset" style="text-align: center;"> 
    <span>Please enter any new work order notes</span>
  </fieldset>

  <textarea type="text" name="floorNotes" id="floorNotes" class="floorNotesText"></textarea>

</fieldset>


<br/>
<br/>
<br/>

</form> <!-- End Work Order Movement Form -->

<fieldset id="doneButtonField">

  <input type="button" name="doneButton" id="doneButton" value="DONE">

</fieldset> 

MY AJAX:

         j("#searchButton").click(function(){


            //send Workorder Movement Data values to php using ajax.

                var sgnumber = j('#sgnumber').val();
                j.ajax ({
                    method: 'POST',
                    url: "workordermovementGET.php",
                    data: {sgNumber: sgnumber},
                    dataType: 'json',
                    success: function( data ){
                        if(data.status){
                            j("select#stageSelect option").filter(function() {
                                return j(this).val() == data.productionstage; 
                                }).prop('selected', true);
                                j("textarea#floorNotes").val(data.floornotes);
                            }

                    }
                 });

        });

MY PHP:

include('inc.php');


//Get Table Options.
if (isset($_POST['sgNumber'])) {
    $sgNumber = $_POST['sgNumber'];

    //connect  to the database 
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if(mysqli_connect_errno() ) {
        printf('Could not connect: ' . mysqli_connect_error());
        exit();
    }

    $conn->select_db($dbname);

    if(! $conn->select_db($dbname) ) {
        echo 'Could not select database. '.'<BR>';
    }

    $sql= "SELECT productionstage, floornotes FROM invoices WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('i', $sgNumber);
    $stmt->execute();
    $stmt->store_result();     

    if ($stmt->num_rows == 1) {
        $stmt->bind_result($productionstage, $floornotes);
        $stmt->fetch();

        echo json_encode(array('status' => true, 'productionstage' => $productionstage, 'floornotes' => $floornotes));
    } else {
        echo json_encode(array('status' => false));
    }


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


//Free the result variable. 
 $result->free();


//Close the Database connection.
 $conn->close();


}//End If statement

?>

I need to know how to properly post part of my ajax result as a select option (the database value will match one of the predefined select options) and and take the rest of the results and display in the textarea. Also, if I can do this dynamically, that would be perfect.

Thank you!

Answer 1

You need to make few changes in your code, such as:

• You need to place the closing </form> tag beneath the DONE button, like this:

      ...
  
      <fieldset id="doneButtonField">
          <input type="button" name="doneButton" id="doneButton" value="DONE">
      </fieldset> 
  </form>
  

  Your question doesn't really explain how you're planning to use this DONE button but I'm sure you have something in mind regarding this. However, this answer revolves around SEARCH button and the associated jQuery/AJAX and PHP functionality.

• Change your jQuery/AJAX script in the following way,

  $("#searchButton").click(function(){
      var sgnumber = $('#sgnumber').val();
      $.ajax ({
          method: 'POST',
          url: "workordermovementGET.php",
          data: {sgNumber: sgnumber},
          dataType: 'json',
          success: function( data ){
              if(data.status){
                  $("select#stageSelect option").filter(function() {
                      return $(this).val() == data.productionstage; 
                  }).prop('selected', true);
                  $("textarea#floorNotes").val(data.floornotes);
              }
          }
      });
  });
  

  It selects a particular productionstage from the dropdown list and populates the floornotes data based on the id value entered by the user in input text field.

• And finally, process your AJAX request in the following way i.e. your PHP code should be like this:

  <?php
      include('inc.php');
  
      if (isset($_POST['sgNumber'])) {
          $sgNumber = $_POST['sgNumber'];
  
          //connect  to the database 
          $conn = new mysqli($servername, $username, $password, $dbname);
  
          // Check connection
          if(mysqli_connect_errno() ) {
              printf('Could not connect: ' . mysqli_connect_error());
              exit();
          }
  
          $conn->select_db($dbname);
  
          if(! $conn->select_db($dbname) ) {
              echo 'Could not select database. '.'<BR>';
          }
  
          $sql= "SELECT productionstage, floornotes FROM invoices WHERE id = ?";
          $stmt = $conn->prepare($sql);
          $stmt->bind_param('i', $sgNumber);
          $stmt->execute();
          $stmt->store_result();     
  
          if ($stmt->num_rows == 1) {
              $stmt->bind_result($productionstage, $floornotes);
              $stmt->fetch();
  
              echo json_encode(array('status' => true, 'productionstage' => $productionstage, 'floornotes' => $floornotes));
          } else {
              echo json_encode(array('status' => false));
          }
      }
  ?>
  

  It uses prepared statement, so you can be sure that it's fairly secure from SQL injection attacks. Furthermore, this is a good read on how you can prevent SQL injection in PHP?

Answer 2

You need to make an array with your database results and echo json_encode($your_array);. That will return your data as a JSON object to the Ajax success function. Then you can parse that object for the data for the select and the data for the textarea. I can't remember how you change the selected option with JS, but I know it is usually the first or second link on a Google search/