PHP Dependent Drop Down Lists

Question

I know several similar questions have been posted related to dependent drop down lists, but I can't quite find the answer I'm looking for - apologies if it's already been covered!

What I'm trying to do is this: I'm building a webpage which will have two related drop down lists; the second's values being dependent on the first's selection. The first is a "Product", the second is a "Size"... so far, so good. The options included in each list ultimately come from a database - Products list is as follows:

<select id="product-list">
    <option value="0">-- Please Select --</option>
    <?php
        if ($res2 = mysqli_query($con,$getproducts)) {
            while($row2 = mysqli_fetch_row($res2)){
                $pid= $row2[0];
                $pname= $row2[1];
                echo "<option value='" . $pid . "'>" . $pname . "</option>";
            }
        }
    ?>
</select>

This one works just fine so no problems there. However, I'm struggling to get my head around the second list...

The query which retrieves the Sizes is this:

$getsizes = "SELECT productid,displayname,price from productsizelookup where formatid = $formatid";

I only have two products (for now), so this query will retrieve all possible sizes, so what I now need is a subset of records from this query based on the product id being supplied by the value of the option in the first select list.

I don't know if this will require another query to the database, and I've read somewhere that I might need AJAX. Is anyone able to assist, or perhaps provide a gentle nudge in the right direction?

EDIT

I tried adding a div block as follows where I wanted the drop down to appear:

<div id="wrap_size"></div>

Then at the bottom of the page added the following script:

<script type="text/javascript">
    $(document).on('change', '#product-list', function(e) {
        var pid = $(this).val();
        var data = {'pid':pid};
        var url = 'size.php';
        $.post(url, data, function(res) {
            $('#wrap_size').html(res);
        });
    });
</script>

And finally, my size.php file:

<?php

include("include/dbvariables.php");

$con=mysqli_connect($host,$username,$password,$database);
if (mysqli_connect_errno()){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$pid = !empty($_POST["pid"]) ? $_POST["pid"] : 0;

$getsizes = "SELECT productid,displayname,price from productsizelookup where formatid = $formatid and productid = $pid";

echo '<select id="size-list">';
echo '<option>-- Please Select --</option>';

if ($res3 = mysqli_query($con,$getsizes)) {
    while($row3 = mysqli_fetch_row($res3)){
        $sid= $row3[0];
        $pid= $row3[1];
        $size= $row3[2];
        $price= $row3[3];
        echo '<option value="'.$sid.'">'.$size.'</option>';
    }
}
echo '</select>';
?>

The only option I now get in the list is the "-- Please Select --" option... Sorry if I'm way off course with this!!

Many thanks

Answer 1

Briefly, how I would do this:

I check the onChange event of the first select list using jQuery.

$(document).on('change', '#product-list', function(e) { ... });

I get the selected value and pass this as data via a jquery AJAX call to a server-side script.

var pid = $(this).val();
var data = {'pid':pid};
var url = 'select_size.php';
$.post(url, data, function(res) {
    $('#wrap_size').html(res);
});

The script uses the selected value to make a SQL call to get the option values for the second select list.

$pid = !empty($_POST["pid"]) ? $_POST["pid"] : 0;

// SQL QUERY HERE, CONSTRUCT SELECT LIST

The script then constructs and echos the HTML for this second select list.

This result of the AJAX call is then written into a container div '#wrap_size' where the second select list should appear.

EDIT:

If $formatid is created in the calling page and used in the size.php script, it needs to get passed through just like pid.

Change your javascript to this:

var data = {'pid':pid, 'formatid':<?php $formatid; ?>};

Or create a hidden input whose value is $formatid, and read that value: var formatid = $('input:hidden[name=formatid]').val(); var data = {pid:pid, formatid:formatid};

Of course, you also have to read its value in your script:

$formatid = !empty($_POST["formatid"]) ? $_POST["formatid"] : 0;

Answer 2

You have this piece of html:

<select name="first" id="first"><option value="1">1</option><option value="2">2</option></select>
<select name="second" id="second"><!-- to be updated --></select>

I will give a jquery example because is smaller:

<script type="text/javascript">
    $('#first').on('change', function() {
       // ajax request to your page that returns the sizes
       if($(this).val()) {
           $.get('/my/sizes/' + $(this).val(), function(data) {
              $('#second').html(data);
           });
       }
    });
</script>

Then '/my/sizes/product_id' page should return the exact elements based on that product id, similar with how you build the product list.