autofill form fields based on value from select field

Question

using php and html, I have a form which is used to input property data into an sql database, the property's each have a primary key called 'property_id' which is autogenerated for them whenever a new property is added via the form. I have created another form so that instead of inserting new property data, I would update existing property data the below code is that form.

<fieldset>
        Select Property:<br>
        <select required name="id">
        <option value"">Select a property</option>
            <?php
            //Database connection
            (It's there but I've removed it for this question)
            // Create connection
            $conn = new mysqli($servername, $username, $password, $dbname);
            // Check connection
            if ($conn->connect_error) {
                die("Connection failed: " . $conn->connect_error);
            } 

            $result = $conn->query("select * FROM property");

            while ($row = $result->fetch_assoc()) {
                unset($id, $unit, $street, $suburb);
                $id = $row['id'];
                $unit = $row['unit'];
                $street = $row['street'];
                $suburb = $row['suburb'];
                echo "<option value='$id'>$unit $street $suburb</option>"; 
            }
            echo "</select>"; ?>
        <br />
        Unit:<br>
        <input type="number" name="unit" required value="<?php echo $unit;?>">
        <br />
        Street:<br>
        <input type="text" name="street" required value="<?php echo $street;?>">
        <br />
        Suburb:<br>
        <input type="text" name="suburb" required value="<?php echo $suburb;?>">
        <br />
        Postcode:<br>
        <input type="number" name="postcode" required value="<?php echo $postcode;?>">
        <br />
        State:<br>
        <select required name="state">
        <option value="">Please Select</option>
        <option value="ACT">ACT</option>
        <option value="NSW">NSW</option>
        <option value="NT">NT</option>
        <option value="QLD">QLD</option>
        <option value="SA">SA</option>
        <option value="TAS">TAS</option>
        <option value="VIC">VIC</option>
        <option value="WA">WA</option>
        </select>
        <br />
        Employee ID:<br>
        <input type="number" name="employee" required value="<?php echo $employee;?>">
        <br />
        Owner ID:<br>
        <select required name="property">
        <option value"">Select an owner</option>
            <?php
            //Database connection
            (It's there but I've removed it for this question)
            // Create connection
            $conn = new mysqli($servername, $username, $password, $dbname);
            // Check connection
            if ($conn->connect_error) {
                die("Connection failed: " . $conn->connect_error);
            } 

            $result = $conn->query("select owner_id, first_name, last_name FROM property_owner");

            while ($row = $result->fetch_assoc()) {
                unset($id, $unit, $street, $suburb);
                $id = $row['owner_id'];
                $fName = $row['first_name'];
                $lName = $row['last_name'];
                echo "<option value='$id'>$fName $lName</option>"; 
            }
            echo "</select>"; ?>
        <br />
        Type of Property:<br>
        <input type="radio" name="type" value="Apartment" required checked>Apartment<br />
        <input type="radio" name="type" value="Town House">Town House<br />
        <input type="radio" name="type" value="House">House<br />
        <br />
        Rent:<br>
        <input type="number" name="rent" value="<?php echo $rent;?>"><br />
        Bedroom:<br>
        <input type="number" name="bedroom" value="<?php echo $bedroom;?>"><br />
        Bathroom:<br>
        <input type="number" name="bathroom" value="<?php echo $bathroom;?>"><br />
        Furnished:<br>
        <input name="furnished" type="radio" value="1" checked>Yes<br>
        <input name="furnished" type="radio" value="0" checked>No<br>
        Property Description:<br>
        <textarea name="description" cols="50" rows="15"></textarea>
        <br />
        <p></p>
        <input type="submit" name="SUBMIT" value="Submit">

I have been following the tutorial from W3 school and taking bits and pieces from other stackflow questions. What I wish to know is, using the dropdown menu at the top (select property followed by a dynamic list of properties by their ID) how may I fill the different form fields depending on the existing values from the sql database?

Answer 1

If I understand correctly you are trying to get values from a database every time a select option is selected correct? This requires you to make a server request since you cannot do it client side.

You will need to add an event handler to your select box and then add an ajax call that returns the values you need.

Using Jquery (a bit more readable) //include this in head

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>

//include and modify the javascript below:

<script type="text/javascript">
    $(document).ready(function(){
        $('select[name=id]').on('change', function(){
            var id = $(this).val();
            $.ajax(url: 'getDataFromSQL.php?id=' + id, 
                success: function(data){
                    /*prepop the form from the data returned*/
                    /* e.g. if you are returning json */
                    /* $('input[name=id]').val(data.rent); */
                }
            );
        });
    });
</script>

Or you could output all variables from the sql database on page load. Then an ajax call would not be needed but your form would not be as realtime as possible.

What you'll need to understand:

You have to know the difference between client side and server side https://softwareengineering.stackexchange.com/questions/206254/difference-between-a-server-and-a-client

You may want to look up ajax http://www.codeproject.com/Articles/14142/AJAX-Explained

You may want to explore event listeners: https://api.jquery.com/category/events/

---