how to use jquery variable in mysql query

I'll try to help with an answer here, but there are some really awful/dangerous programming practices in the code you've posted, and I would be remiss if I did not try to help you move toward a "better practices" pattern.

You must get off MySQL.  It's a time-bomb in your code.  We have known about the danger for years, so there is no excuse.  Your work is living on borrowed time, and if a client is paying you for this, you need to explain to them how much danger they face.  If a PHP upgrade occurs, their entire application will immediately stop working!  If a security exploit is found in the existing PHP version, and you try to get PHP to fix it, they will say, "Tough luck - just upgrade."  The upgrade is a Hobson's choice.  Your only option is to upgrade, and the only question you must answer is, "Should I do it now, when I can do it with care, or should I wait until everything is collapsing around me and there is an emergency?"  No currently supported version of PHP still includes MySQL.  You might want to think about using E-E Gigs to get a professional developer to help you with this urgently necessary task.

You must filter/sanitize and escape the external variables.  Never  use an external variable in a query string!  This is security 101.

Avoid the use of MySQL_fetch_Array() and its analogs.  It fetches twice as much information as you need, making it the least efficient way to get the data back from the DB engine.  Choose fetch_assoc() or better yet fetch_object() instead.

---

There are three parts to this application.  On the first HTTP request, your server will use a query to prepare a response that is a web page with <select> and <option> tags.  You seem to have this covered in the first code snippet.  

When the client makes the selection, there will be a second request that will occur when the submit button is fired or there is some kind of AJAX trigger, perhaps via jQuery.  This time, on the server-side of things, you want to receive a variable, sanitize it, and use it in a database query.  Your server side script will gather the query results set and make a response.  

The client browser will display the response, either as a whole web page, or as a replacement for part of the DOM.  

It's important to think clearly about the timeline of HTTP request/response .

This article covers the basics of a jQuery/AJAX interaction between client and server.  HTH, ~Ray

Let's take this one step at a time. First a simple lesson in how client server web works.

Server: This is where your PHP script runs. The script runs in the context of the web server driving it (Apache / IIS) - the latter launches the script process and passes the specified PHP file to it for processing. When PHP has finished processing the script the output is handed back to the HTTP server which sends it back to the client. This is an oversimplification of the process but will do for now.

When the client receives the output from the script the script that generated it is dead and buried - there is no more communication or exchange of data that can happen.
Now at some point the client needs to go back to the server - either to submit data or request it. This requires a completely new request to the server - when the server receives the request it has no knowledge of any previous interactions it has had with your browser - you have to tell it through sessions or cookies.

When you have javascript/jquery code running that needs to exchange data with the server - the only way to do this is to setup a HTTP request and do a GET / POST / PUT / DELETE back to the server with relevant parameters. This can be done through a link click, a form submission (both of which will refresh the page) or an AJAX request (that might be initiated in place of the aforementioned or as a completely independent request). with appropriate parameters.

Let's look at your client side 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>

Select allOpen in new window

My first question is - why are you pulling the .html of the selected option when option has a perfectly good value attribute that you can store (and should store) data needed to send back to the server.

My second question is - where does this code AJAX back to your PHP script - from what I can see you have a lovely PHP script waiting to do something but nobody ever calls it. Somewhere you need to initiate a request to that script. This would look something like this.

If you are retrieving data

$.get('scriptname.php',data).done(function(resp) {
   // do something with the response
})

Select allOpen in new window

If you are submitting data

$.post('scriptname.php',data).done(function(resp) {
   // do something with the response
})

Select allOpen in new window

In both cases do you see the data parameter is used? That is how you send data to the script. data is a JavaScript object or string that contains your request parameters - they are the same for GET and POST. You could do something like this

var data = {
    dept: $('#dept').val()
}

Select allOpen in new window

If you call the $.get() function then the above will result in the query string dept=123 being sent to your PHP script which you will be able to access with

$id = isset($_GET['dept']) ? $_GET['dept'] : false;

Select allOpen in new window

If you used $.post()

$id = isset($_POST['dept']) ? $_POST['dept'] : false;

Select allOpen in new window

Give that a try and post back with any questions you have.

@Julian Thanks input.

1). The reason for that is that I need to get the html of the select so I can do checking for wether a user enters a dept or not and display the approaprite message.

2) I have made a mock up of my code and it displays the correct code in console.log, but where I am struggling is, how do I use this value in my mysql query which is in php.

<script type="text/javascript">
    var depts;
    $('#dept').on('change', function() {
				
	depts = $('#dept option:selected').html();
	  $.ajax({
            url: 'deptdata.php',
            type: "POST",
            /*contentType: "application/json; charset=utf-8",*/
            data: {val : depts },
            dataType: 'json',
            success: function (data) {
                console.log(data);

            }
        });
			
//			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>

Select allOpen in new window

php backend

<?php

		$dept = $_POST['val'];

			
		$conn = mysql_connect("localhost","root","");
		mysql_select_db("sample",$conn); 
		$result = mysql_query("SELECT * FROM departments WHERE name = '$dept'");
		$row = mysql_fetch_array($result);
		$value = $row['name'];
		
		echo $value;
//		$str = json_encode($value);
//		echo trim($str, '"');
?>

Select allOpen in new window

Try changing the $_POST to $_GET so you can test from the browser.

Test for, and visualize errors any errors that occur when you run the script with something like this:

<?php // demo/temp_peter_cooper.php
/**
 * https://www.experts-exchange.com/questions/29017873/how-to-use-jquery-variable-in-mysql-query.html#a42103739
 *
 * References for PHP and MySQL(i):
 *
 * http://php.net/manual/en/mysqli.overview.php
 * http://php.net/manual/en/class.mysqli.php
 * http://php.net/manual/en/class.mysqli-stmt.php
 * http://php.net/manual/en/class.mysqli-result.php
 * http://php.net/manual/en/class.mysqli-warning.php
 * http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
 *
 * http://php.net/manual/en/mysqli.construct.php
 * http://php.net/manual/en/mysqli.real-escape-string.php
 * http://php.net/manual/en/mysqli.query.php
 * http://php.net/manual/en/mysqli.errno.php
 * http://php.net/manual/en/mysqli.error.php
 * http://php.net/manual/en/mysqli.insert-id.php
 *
 * http://php.net/manual/en/mysqli-result.num-rows.php
 * http://php.net/manual/en/mysqli-result.fetch-array.php <-- DO NOT USE THIS
 * http://php.net/manual/en/mysqli-result.fetch-object.php
 */
error_reporting(E_ALL);


// THE RESPONSE VARIABLE
$str = NULL;


// DATABASE CONNECTION AND SELECTION VARIABLES
$db_host = "localhost"; 
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// SET DEFAULT VALUE AND ESCAPE THE FOREIGN VARIABLE
$dept = !empty($_GET['dept']) ? $_GET['dept'] : 'unknown'; // <-- CHANGED TO GET SO IT CAN BE TESTED FROM THE BROWSER
$dept = $mysqli->real_escape_string($dept);


// TRY THE QUERY, VISUALIZE ERRORS, IF ANY
$sql = "SELECT * FROM departments WHERE name = '$dept' LIMIT 1"; // <-- Column names?? LIMIT, ORDER, GROUP CLAUSES??
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// NOW WE CAN USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS


// ARE THERE ANY RESULTS?
if ($res->num_rows)
{
    $row = $res->fetch_object();
    $str = $row->name;
}


// SEND THE RESPONSE
echo $str;

Select allOpen in new window

@Ray Excellent post. I shall start using mysqli and start to update and read up on it. However, back to my problem. I am getting the correct data from backend

'http://localhost/sample/users/deptdata.php?val=DEMO'

Select allOpen in new window

but need to now populate #boxdest2 with that value in my search query. Thanks

The reason for that is that I need to get the html of the select so I can do checking for wether a user enters a dept or not and display the approaprite message.

That is what the value is for

<select id="dept">
  <option value="123">123</option>
  <option value="124">124</option>
  <option value="125">125</option>
</select>
...
$('#dept').change(function() {
  alert($(this).val());
});

Select allOpen in new window

Shouldn't be any reason to get the actual HTML.

but where I am struggling is, how do I use this value in my mysql query which is in php.

Does not make sense - you are using it - Lines 3 and 8 of your PHP script.

You Must Not continue to use the Old Rust Bucket MySql, Before you try and do any AJAX, you MUST learn how to use the Improved faster, better MYSQLI

you show this code -

    $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 allOpen in new window

NO, NO, NO, don't even think about the old MySql.

Here is my Simplistic mysqli code substitution -

$conn = mysqli_connect("localhost", "dbUserName", "dbPassword", "sample");

$dept = isset($_POST['val']) ? $_POST['val'] : false;
     // test to see if the $dept is false or an empty string
if (!$dept) exit("error");// send back some KIND of ERROR string if $dept is EMPTY

     // IMPORTANT IMPORTANT ! !
    // You must ALWAYS safe ESCAPE a browser Variable from AJAX
$dept = mysqli_real_escape_string($conn, $dept); // escape with the mysqli function

$result = mysqli_query($conn , "SELECT * FROM boxes where department = '$dept' and status = 1 ORDER BY custref ASC");

     // you need to TEST here to see if any ROWS were returned
    // I did not do this to Simplify coding, without further explanations

$row = mysqli_fetch_assoc($result); // get ONE table row
   // send back just the NAME string
echo $row['name'];

Select allOpen in new window

also to effectively  use AJAX , you will need to know Javascript operations and Jquery code methods

but need to now populate #boxdest2 with that value in my search query. Thanks

Not entirely sure what you are asking but here are the steps to close the AJAX loop
1. Define the HTML (have to guess based on what has been posted)

<form>
<label>Department</label>
<select id="dept">
  <option value="123">123</option>
  <option value="124">124</option>
  <option value="125">125</option>
</select>
<input id="boxdest2" />
</form>

Select allOpen in new window

2. The JavaScript /jQuery. We act on the change event of the select, AJAX the dept to the server, receive the response in the callback and put it into the required location.
Note: Your PHP does not json encode the results - but as you are turning a string - it will still be parsed by jQuery

<script>
$(function() {
  $('#dept').change(function() {
    var depts = $(this).val();
    $.ajax({
      url: 'deptdata.php',
      type: "POST",
      data: {val : depts },
      dataType: 'JSON'
    }).done(function(resp) {
        // We specified a JSON return so resp will have already been
        // parsed into a JavaScript object - you are returning a string but it will still be accessible

       // To add to boxdest2 (which I assume is a control) do as follows
       $('#boxdest2').val(resp);

       // if boxdest2 is not a control then use .html() instead of .val();
       // Any other code you want to run on  return
    });
  });
});
</script>

Select allOpen in new window

getting the correct data from backend

Good, that means progress.  Please copy the data and post it here in the code snippet so we can use the actual data in the examples that will show you how to use it.

need to now populate #boxdest2 with that value

That is a bit of a mystery.  I could find nothing on this page that is called "boxdest2" so I'm at a bit of a loss to show you a well-targeted example.

Applications like this are made up of the client-side technologies including HTML for semantic markup, CSS for appearance and placement, JavaScript (often AngularJS or jQuery) for animation.  On the server side, we have PHP and maybe some kind of database.  In order to understand the application we need to consider all of the parts and the way they interact.  That's why it's important to find "boxdest2" in your HTML, so please post that part of the question.

In this article, the server-side script sends some data back in response to the AJAX request.
https://www.experts-exchange.com/articles/10712/The-Hello-World-Exercise-with-jQuery-and-PHP.html

Look for the HTML elements identified by <div id="output"> and <p id="target">.  This is a nested data structure.  The JavaScript that puts the response data into the target element of the output element looks like this:

$("#output p#target").html(response);

Select allOpen in new window

Reading from left to right, this is saying Use jQuery to find the element with the id=output, and inside that locate the <p> tag with the id=target.  Replace its HTML with the contents of the response variable.

I am trying to change my thinking and method of application to incorporate using mysqli and jquery. I have done a lot of reading and it is starting to sink in. So with that in mind, I decided to try and create a loop in the script that Ray Paseur coded earlier and not getting expected results.

I can see that data is being returned in console html tab but when my for script runs in ajax success, it populates the select with several items saying 'undefined'. There should only be 1 item in that particular dept but trying to learn so I can allow for items that have more than 1 item. I have changed the datatype from json to 'text' because it wouldn't return items from backend if I used 'json'.

So to move on from this, how have I slipped up. I have only posted the code that is causing the problem Thanks

ajax code

$.ajax({
            url: 'deptdata.php',
            type: "POST",
            //contentType: "application/json; charset=utf-8",
            data: {val : depts },
            dataType: "text",
            success: function (data) {
                
		$('#boxdest').empty();
		var len = data.length;

		for (var i = 0; i < len; i++) {
									
		var name = data[i]['custref'];

		$("#boxdest").append("<option value='" + name + "'>" + name + "</option>");
		}
            }
        });

Select allOpen in new window

while loop code

// ARE THERE ANY RESULTS?
if ($res->num_rows)
{
    while($row = $res->fetch_object())
	{
	     echo $row->custref;
	}   
}

Select allOpen in new window

Peter, do you have a wireframe for this application?  I feel like over the last several questions that I am seeing an evolving set of scripts with changes (some better than others) that mutate pieces of the front-end and back-end.  But I'm not seeing the overall design and I don't really understand the semantic markup.  It's like we're dealing with the forest one tree at a time.  These little fragments are hard to follow when we have to read the entire question, or questions, and try to mentally reconstruct an entire application of PHP, MySQL, HTML, JavaScript.  This is where the concept of the SSCCE would be valuable, especially the part about "short" and "self-contained."

I seem to remember that in one question you wanted to move information from a left-side selection to a right-side selection, and only enable the submit button when there was information in the right-side.  The goal was to let the user choose among options, moving options left and right.  The submit control would only submit the collection on the right, and would only be active if there was at least one option on the right.

It appears now, and please correct me if I'm wrong, that you want to make an intermediate step.  If the user selects one of the left-side options, you want to make an AJAX request for verification before moving the selection to the right.  While it's technically possible to do that, it doesn't make sense.  The collection of options the user may choose is completely under the control of your server!  Your server creates the web page that gives the initial state of the two selection lists.  You don't need to filter or test any of the options as they are moved back and forth between the left and right selection lists.  You only need to sanitize the input when the user says, "use this collection" by firing the submit control.

The way to design/build an application like this one, or any other one, is to isolate the components into functional units that can be individually built, tested, and mocked.  A central goal of the design process is to create a separation of concerns between the components of the application.  Your HTML should be completely independent of the JavaScript.  Your database queries should need no knowledge of the PHP script.  Your AJAX requests should be able to call the live script or a dummy script that returns a mock response.  As you can decouple these components, you will find that you can think about one thing at a time, and you won't find yourself trying to do jQuery and MySQLi all at once.  Formal unit-testing is beyond the scope of this effort, but that's the way to think about the single responsibilities of the components.

Here is how I would go about this task if it were mine.  First, I would get the mock database working - a small set of test data that always gives predictable responses to a small set of queries.  Next, I would get the PHP working - receiving requests, calling the mock database, returning responses.  After that I would write the HTML markup, assigning meaningful names to the id and class attributes.  Then I would start on the appearance and animation via JavaScript (jQuery), and get everything on the client-side page right.  Once I have the server-side working and the client-side working, then it's time to integrate these with the AJAX requests.

Isolating the components and testing each change seems like a lot of work at first, but really it does not add time or work to the process of building software.  It actually makes the process faster and more accurate.  I think you will find it helpful to your project.

@Ray I totally agree with you. I seem to have gone from a jquery variable to how big is a piece of moon cheese :-) I think that the best option is to close this question and create a new one only working one topic at a time.

Further to the ways of understanding JSON...
https://www.experts-exchange.com/articles/22519/Understanding-JSON-in-PHP-and-JavaScript-Applications.html

@Ray Many thanks for that. Working 99%. However, if there is no data from query, it dosen't empty #boxdest but leave the data there from last time it was populated. How would i clear items if there are no results. Thanks

I don't know.  If your PHP script generated the collection of possible selections, how could there be no results?  Wouldn't it be using the same database to generate the select lists and to process the client selection?

I guess I'm not understanding the application logic very well, sorry.

peter-cooper, , unfortunately the whole AJAX set-up, operations, De-bugging, and results in the browser are not easy to understand and use, especially if you are not experienced in javascript (Jquery) and PHP. As you have found out, there's several important factors, that need to be understood, in each of the three computer phases of ajax (browser-server-browser). Also me and the other experts here, do not seem to understand what HTML elements you need to use on your page, and what you need done (the goal of your efforts).

Some ajax beginners, I have tried to help with, got some understanding, by starting ajax programming, with easy examples, with simple operations, that were a copy and paste for them. so at least they got code that works to start out with. The code you started with here, had so many errors and half-errors and misconceptions about Ajax operations, that so far, you have not been able to untangle.

the last code you gave for the AJAX server return -

success: function (data) {
                
		$('#boxdest').empty();
		var len = data.length;

		for (var i = 0; i < len; i++) {
									
		var name = data[i]['custref'];

		$("#boxdest").append("<option value='" + name + "'>" + name + "</option>");
		}

Select allOpen in new window

has so much wrong with it,
and the "thinking and concept" about what the javascript return as
data
has in it, and what you can do with it, is not even close.
You may want to start you code use with a simple, and up to date WELL FORMED ajax code structure that works, and then try changing it (adding to the complexity) , one thing at a time, and get each new part to work, before you add several parts on the javascript and the PHP, that you can not tell whats happening.

Just my opinion.

$.ajax({
  url: 'deptdata.php',
  type: "POST",
  data: {val : depts },
  dataType: "JSON"
  }).done(function(data) {
    // Simply remove the if statement I put that in to not clear if no items found
    $('#boxdest').empty();
    for (var i = 0; i < data.length; i++) {
      var name = data[i];
      $("#boxdest").append($('<option/>', {val: name}).html(name));
    }
  })
});

Select allOpen in new window

Thanks very much

You are welcome.