Filter through SQL query and populate <select>

Question

The code below runs and populates a dropdown. The 2nd, 3rd, and 4th query also populate a dropdown, just not in this example. I want to take what is chosen in the first dropdown and filter the data from the other 3. Here is the PHP:

<?php
require_once('conn.php');
include '_menu.php';

//Get Processes
$smt = $stamp->prepare('SELECT tblProcess.PID, tblProcess.pName
                    FROM tblProcess
                    WHERE tblProcess.pActive=1
                    ORDER BY tblProcess.pOrder');
$smt->execute();
$data = $smt->fetchAll();

//Get Categories
$smt1 = $stamp->prepare('SELECT tblCategory.CID, tblCategory.PID, tblCategory.cName
                    FROM tblCategory
                    WHERE (((tblCategory.cActive)=1))
                    ORDER BY tblCategory.cOrder;');
$smt1->execute();
$data1 = $smt1->fetchAll();

//Get SubCategories
$smt2 = $stamp->prepare('SELECT tblCategorySub.CSID, tblCategorySub.CID, tblCategory.PID, tblCategorySub.csName
                    FROM tblCategory INNER JOIN tblCategorySub ON tblCategory.CID = tblCategorySub.CID
                    WHERE (((tblCategorySub.csActive)=1))
                    ORDER BY tblCategorySub.csOrder;');
$smt2->execute();
$data2 = $smt2->fetchAll();

//Get Cause
$smt3 = $stamp->prepare('SELECT tblCause.CauseID, tblCause.caName
                    FROM tblCause
                    WHERE (((tblCause.caActive)=1))
                    ORDER BY tblCause.caSortOrder');
$smt3->execute();
$data3 = $smt3->fetchAll();
?>

Here is the HTML that populates one dropdown. I only included one to shorten this question.

<html>
<head>
    <script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>

    <link type="text/css" rel="stylesheet" href="stylesheet.css"/>  
     <script src="../script/_DDDisable.js"></script>
    <title></title>
</head>
<body>
    <div id="header">
        <p id="name">STAMPING TROUBLE REPORT</p>
        <a href="mailto:[email protected]"><p id="email"></p></a>
    </div>
    <div class="right">
        <h5>Process&nbsp&nbsp&nbsp<select class="form-control" name="Process" id="Process">
            <option value="select">--Select Process--</option>
            <?php foreach ($data as $row): ?>
                <option value=$row[PID]><?=$row["pName"]?></option>
            <?php endforeach ?></select></h5>
    </div>
</body>

Ohgodwhy · Accepted Answer · 2016-01-14 21:20:36Z

Create your HTML markup like so, the data-belongsto attribute tells us which dropdown our value should populate in the DOM. The ID we will send to the server for a use case in a switch statement.

<select class="form-control" name="Process" id="Process" data-belongsto="Category">


$('select.form-control').on('change', function(e){
    e.preventDefault();

    //cacheable reference to our select
    var $this = $(this);
    $.ajax({
        url: '/path/to/my/file.php',
        type: 'POST',
        data: {
            'request_type'  : $this.prop('id'),
            'request_value' : $this.val()
        },
        dataType: 'json',
        success: function(response){
            //loop over our response and append to the `belongsto` we defined earlier
            $.each(response.option, function(i,opt){
                $('#'+$this.data('belongsto').append('<option value="'+opt.PID+'"> '+opt.pName+'</option>');
            });
        }
    });
});

Then in your file just do switch statements with a default. We assume that process will always run first.

if(!isset($_POST['request_type']){
    //Get Processes
    $smt = $stamp->prepare('SELECT tblProcess.PID, tblProcess.pName
                    FROM tblProcess
                    WHERE tblProcess.pActive=1
                    ORDER BY tblProcess.pOrder');
    $smt->execute();
    $data = $smt->fetchAll();

} elseif(isset($_POST['request_type']) && isset($_POST['request_value'])) {
    switch($_POST['request_type']):
        case 'Categories':

           //Get Categories
           $smt1 = $stamp->prepare('SELECT tblCategory.CID, tblCategory.PID, tblCategory.cName
                    FROM tblCategory
                    WHERE (((tblCategory.cActive)=1))
                    ORDER BY tblCategory.cOrder;');
           $smt1->execute();
           $data = $smt1->fetchAll();
           break;

        case 'SubCategories':
           //Get SubCategories
           $smt2 = $stamp->prepare('SELECT tblCategorySub.CSID, tblCategorySub.CID, tblCategory.PID, tblCategorySub.csName
                    FROM tblCategory INNER JOIN tblCategorySub ON tblCategory.CID = tblCategorySub.CID
                    WHERE (((tblCategorySub.csActive)=1))
                    ORDER BY tblCategorySub.csOrder;');
           $smt2->execute();
           $data = $smt2->fetchAll();

        case 'Cause':

            //Get Cause
            $smt3 = $stamp->prepare('SELECT tblCause.CauseID, tblCause.caName
                    FROM tblCause
                    WHERE (((tblCause.caActive)=1))
                    ORDER BY tblCause.caSortOrder');
            $smt3->execute();
            $data = $smt3->fetchAll();
            break;
    endswitch;

    //exit to make sure nothing else is sent back that would alter our json response types validity
    echo json_encode($data, true); exit();
}

Now just modify your SQL statements to check for their respective $_POST['request_value'] and you'll be good.

In your jQuery when ever for some reason you select on option than another, then back and forth you`ll be always appending options to the next select also appending alot is much more performance costing than appending/concatanating a string, which brings me to say populate a variable with html markup than at the end of the loop set the selects content to that of the variable
@Ohgodwhy, I am a bit confused by why you said I need to modify my SQL statement as they already check for their value.

Collectives™ on Stack Overflow

Filter through SQL query and populate <select>

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related