how can I use jquery variable in mysql query

Question

At the moment, I am using a $_GET to query mysql and populate a select statement, which works fine. However, I now need to query db using jquery variable and am unable to find a way to use 'depts' instead of '$_GET['dept']'.

I have declared the var global, but realise that you cannot use var in query.

I would be grateful if someone could show me how to amend my code to achieve this. Thanks

php code to populate select

<?php
    $conn = mysql_connect("localhost", "root", "");
    mysql_select_db("sample", $conn);
    $result = mysql_query("SELECT * FROM boxes where department = '{$_GET['dept']}' and status = 1 ORDER BY custref ASC");
?>

    <select name="boxdest[]" id="boxdest" size="7" multiple="multiple">

<?php
    $i=0;
    while($row = mysql_fetch_array($result)) {
    ?>
    <option value="<?php echo $row["custref"];?>"><?php echo $row["custref"];?></option>
    <?php
    $i++;
    }
?>

    </select>

jQuery change event code

<script type="text/javascript">

    var depts;

    $('#dept').on('change', function() {

    depts = $('#dept option:selected').html();

    if (depts === 'Select a Department') {

       $('#deptResult').html('<p>ERROR: You must Select a department to proceed<p/>').css({'color':'red'});
       $( "#submit" ).prop( "disabled", true );
       return;
    }
      $('#deptResult').html('<p>SUCCESS: You have selected the following dept: ' + depts + '</p>').css({'color':'black'});
    });
</script>

Answer 1

Can you? Yes

You have to use AJAX. I can recommend crafting simple API for this task. Example using JSON:

api.php

<?php

function output($arr) {
    echo json_encode($arr);
    exit();
}

if (!isset($_GET['dept'])) {
    output([
        'success' => false,
        "message" => "Department not defined"
    ]);
}

$mysqli = new mysqli("localhost", "root", "", "test");
if ($mysqli->connect_errno) {
    output([
        'success' => false,
        'dept' => $_GET['dept'],
        'message' => "Connect failed: ". $mysqli->connect_error
    ]);
}

$result = $mysqli->query("SELECT DISTINCT(`department`) FROM `boxes`");
if (!$result) {
    output([
        'success' => false,
        'dept' => $_GET['dept'],
        'message' => "Query failed"
    ]);
}

$departments = [];
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
    $departments[] = $row['department'];
}

if (!in_array($_GET['dept'], $departments)) {
    output([
        'success' => false,
        'dept' => $_GET['dept'],
        'message' => "Department not present in database"
    ]);
}

$result = $mysqli->query("SELECT `custref` FROM `boxes` WHERE `department`='". $_GET['dept'] ."' ORDER BY `custref` ASC");
if (!$result) {
    output([
        'success' => false,
        'dept' => $_GET['dept'],
        'message' => "Query failed"
    ]);
}

$custref = [];
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
    $custref[] = $row['custref'];
}
output([
    'success' => true,
    'dept' => $_GET['dept'],
    'custref' => $custref
]);

$result->free();
$mysqli->close();

$(function () {
    $('select[data-key][data-value]').each(function (i, element) {
        var key = $(element).data("key");
        var value = $(element).data("value");
        var $originSelector = $('[name="'+ key +'"]');

        /**
         * Get options from element by name
         */
        function getOptions () {
            var request = {};
            request[key] = $originSelector.val();
            $.ajax({
                url: "./api.php",
                method: "GET",
                dataType: "json",
                data: request
            }).done(function(data) {
                setOptions(data);
            });
        }
        
        /**
         * Remove old options
         */
        function clearOptions () {
            $(element).find('option').remove();
        }
        
        /**
         * Put new options in input
         */
        function setOptions (data) {
            if (data['success'] && data[value] !== undefined) {
                clearOptions();
                $.each(data[value], function (i, option) {
                    $(element).append('<option value="'+ option +'">'+ option +'</option>');
                });
            }
        }

        getOptions();
        $originSelector.on("change", function () {
            getOptions();
        });
    });
});

<select name="dept">
    <option value="accounting">Accounting</option>
    <option value="it">Information technology</option>
</select>

<select name="boxdest[]" id="boxdest" size="7" multiple="multiple" data-key="dept" data-value="custref"></select>

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

Answer 2

Use jquery ajax() like:

$.ajax({
    url     : 'process.php',
    method  : 'get',
    async   : false,
    data    : {
        variable : value,  
        // you can pass multiple variables like this and this is available in php like $_REQUEST['variable']
    },
    success : function(response){
        // do what ever you want with the server resposne
    }
});

process.php:

$variable = $_REQUEST['variable'];  // you can use $variable in mysql query