Obtaining Values from combo box at run-time into text box

Hi,

I have a form from which I get user information. In the form I have a text box called price. The price depends on 3 criteria which the users select from the combo box.

I have the sql command to query from my database, I just need to create a change event on the 3 combo boxes which checks the value of the 3 combo boxes, and provides me the equivalent price in my text box on the same form.

I am using php and mysql as the database.

Thanks in advance!!
Dexter_1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Vel EousConnect With a Mentor Research & Development ManagerCommented:
Perhaps I have misunderstood somewhere, however you cannot perform the SQL execution from within the jQuery $.ajax() function.  The SQL has to be executed serverside, setting the data property value of $.ajax() to be a SQL query (SELECT FROM ...) provides no benefit and as I mentioned before, is a security risk (although I realise you've mentioned this is not an issue - I would hasten you reconsider, better to do it properly the first time even for a demo).

I took a gander at the code you provided and have devised a quick example of how the functionality you desire could be achieved (it has not been tested - just a proof of concept).


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript">
$(document).ready(function() {
		$("#getRate").click(function() {
			$.ajax({
				type: "POST",
				url: "ajax/perform-sql.php",
				data: "?timeWarrenty=" + $("#timeWarrenty").val() + "&origin=" + $("#origin").val() + "&destination=" + $("#destination").val();
				success: function(msg) {
					$("#rate").val(msg);
				}
			});
		});
});
</script>
</head>

<body>
<table>
    <tr>
        <td class="TrackMediumBlue">Time Warrenty:</td>
        <td>&nbsp;</td>
        <td>
            <select name="timeWarranty" id="timeWarranty">
                    <option value="24hrs">24Hours</option>
                    <option value="2days">2Days</option>
                    <option value="3days">3Days</option>
                    <option value="7days">7Days</option>
                    <option value="14days">14Days</option>
            </select>
        </td>
    </tr>
    <tr>
    	<td class="TrackMediumBlue">Origin:</td>
        <td>&nbsp;</td>
        <td><input type="text" id="origin" name="origin" maxlength="50" size="40" /><span class="REDLink">*</span></td>
    </tr>
    <tr>
    	<td class="TrackMediumBlue">Destination:</td>
        <td>&nbsp;</td>
        <td><input type="text" id="destination" name="destination" maxlength="50" size="40" /><span class="REDLink">*</span></td>
    </tr>
    <tr>
    	<td class="TrackMediumBlue">Package Type:</td>
        <td>&nbsp;</td>
        <td><input type="text" id="packageType" name="packageType" maxlength="50" size="40" /><span class="REDLink">*</span></td>
    </tr>
    <tr>
    	<td class="TrackMediumBlue">Rate:</td>
        <td>&nbsp;</td>
        <td><input type="text" id="rate" name="rate" maxlength="50" size="40" readonly="readonly" /></td>
    </tr>
    <tr>
    	<td></td>
        <td></td>
        <td><input type="submit" name="getRate" id="getRate" value="Get Rate" /></td>
    </tr>
</table>
</body>
</html>

<?PHP

// This file would be on your server to dela with processing the information
// It is a _basic_ proof of concept and would require validation of information (checking for nulls, invalid characters etc)

// Declare some variables for use
$timeWarrenty 	= NULL;
$origin 		= NULL;
$destination 	= NULL;
$packageType	= NULL;

// Determine if the required information has been sent from the form via jQuery $.ajax()
// Would require further validation of information other than isset()
if (isset($_POST['timeWarrenty']))
{
	// Advisable to perform some validation on the variable
	$timeWarrenty = $_POST['timeWarrenty'];
}
if (isset($_POST['origin']))
{
	// Advisable to perform some validation on the variable
	$origin = $_POST['origin'];
}
if (isset($_POST['destination']))
{
	// Advisable to perform some validation on the variable
	$destination = $_POST['destination'];
}
if (isset($_POST['packageType']))
{
	// Advisable to perform some validation on the variable
	$destination = $_POST['packageType'];
}

// Assuming all data has been validated, create a SQL query
$query = "SELECT `" . mysql_real_escape_string($timeWarrenty) . "` FROM `RATE` WHERE `Origin` = '" . mysql_real_escape_string($origin) . "' AND Destination = '" . mysql_real_escape_string($destination) . "' AND Type = '" . mysql_real_escape_string($packageType) . "'";
// Execute the SQL query
$result = mysql_query($query);
// Iterate through the results
while ($row = mysql_fetch_assoc($result))
{
	// Do something with each iteration
}

// This is returned to your HTML page via jQuery $.ajax()
// You can return whole tables of data if required
echo 'Rate: ' . $timeWarrenty;

?>

Open in new window

0
 
Vel EousResearch & Development ManagerCommented:
You could make use of jQuery for detecting change events on the combo boxes and then obtaining their values:

http://api.jquery.com/category/events/

http://elegantcode.com/2009/07/01/jquery-playing-with-select-dropdownlistcombobox/


Then make use of the jQuery AJAX method to send the values to your PHP/SQL script where it can be utilised and a value returned back to your textbox:

http://api.jquery.com/jQuery.ajax/


That is obviously assuming you wish to perform this asynchronously.
0
 
Mehul_PanchalCommented:
<form method="post">
<label>Select Critarea</label>
<select name="critarea" id="critarea" onChange="this.form.submit();">
<option value="0">select critarea</option>
<option value="1">critearea_1</option>
<option value="2">critearea_2</option>
<option value="3">critearea_3</option>
</select>
<br/>
<label>Price</label>
<input type="text" name="price" value="<?php echo $result['price'];?>" >

</form>
<?php

$sql="select price from pricetable where priceid=".$_POST['critarea']."";
$result=mysql_query($sql);



?>
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dexter_1Author Commented:
Ok

So I can use $("#ComboBox option:selected").text() to find the text from my combo boxes

and I can use $("#ComboBox").change(function() { /* do something here */ });

the do something part to add my query which checks the values from the 3 combo boxes and returns the equivalent price in my price text box.

Do I need to have this function in all three combo boxes?

Can you just give a short example of how the code will look, just dummy code for getting the results from the 3 combo boxes

So lets say you get A, B, and C from the combo box. I look in my table X, with query SELECT Price from table X where ColA= combo box1 value and Colb = combo box2 value and ColC = combo box 3 value

Can I use SQL with the dummy query typed above in the do something part. I can set a variable = query, and use the variable as value of my text box...
0
 
Dexter_1Author Commented:
Mehul,

I have 3 different combo boxes, and the price will change when I change either of the values in the combo box,

also I save all the information into my database once the customer clicks submit

So I want the customer to be able to see the prices as he changes the 3 criteria, and once selected, then I add to my database

I have all the code except the part to find the rate.

I have the query to find the rate on my next form which outputs the receipt, but I want the customer to be able to choose which rate he wants based on the 3 criteria.

Thanks for your help!
0
 
Vel EousResearch & Development ManagerCommented:
I cannot provide a guarantee that the following code works (been a while since I worked with jQuery honestly) however it provides an example of the principle.  A quick google / browse of the jQuery docs would show you where I may have gone wrong.

The principle of the code though is that the .change() function of jQuery is applied to all elements of the page that have a CSS class 'ComboBox' (. (period) denotes classes whilst # (hash) denotes IDs) meaning any ComboBox elements you desire to have the event applied to should be set accordingly (or change the CSS class attribute value etc).  If a change event is detected on said elements then the ajax function is called into play which sends the values of the three ComboBoxes to a PHP page via POST, the returned message is then added to a TextBox with the specified ID attribute value.
0
 
Vel EousResearch & Development ManagerCommented:
Poop, forgot to add the code to the previous post.  I blame having been awake all night.
<script type="text/javascript">
$(document).ready(function() {
	$(".ComboBox").change(function() {
		$.ajax({
			type: "POST",
			url: 'ajax/perform-sql.php',
			data: "?comboBox1=" + $("#comboBox1").val() + "&comboBox2=" + $("#comboBox2").val() + "&comboBox3=" + $("#comboBox3").val();
			success: function(msg) {
				$("#resultTextBox").Text(msg);
			}
		});
	});
});
</script>

Open in new window

0
 
Dexter_1Author Commented:
Can I input my SQL query in data?

That way I can output the result in my text box...
0
 
Vel EousResearch & Development ManagerCommented:
>>  Can I input my SQL query in data?

You could place your SQL query in the data property however due to the script running on the client, people could view your source code and therefore view the whole SQL statement.  This is obviously a security risk and therefore not advisable.  Unless I have misunderstood your meaning on this matter?

>>  That way I can output the result in my text box...

When the data is sent to the PHP file, the returned message is then processed by the success function:

success: function(msg) {
      $("#resultTextBox").Text(msg);
}

The above captures the returned message from the PHP file and sets the value of the TextBox to that of the returned message.

1) ComboBox change event detected
2) Values from all three ComboBoxes are captured and placed within the "data" property of the jQuery ajax function and sent to the PHP file
3) If the PHP file returns a message the "success" function captures the message and performs what ever action is stipulated
0
 
Dexter_1Author Commented:
I don't need to worry about any security breaches as it is only a demo...
0
 
Dexter_1Author Commented:
I basically check the origin, the destination, and the time frame the customer selects for the package

based on those 3 i provide the price

the 3 criteria mentioned above are in 3 different combo boxes

I have attached my code for the combo boxes, rate text box, and mySQL Query
<TR>

            <TD ALIGN="right" class="TrackMediumBlue">Time Warranty :</TD>
            <TD>&nbsp;</TD>
            <TD><select name="TimeWarranty" id="TimeWarranty">
                <option value="24Hours">24Hours</option>
                <option value="2Days">2Days</option>
                <option value="3Days">3Days</option>
                <option value="7Days">7Days</option>
                <option value="14Days">14Days</option>
            </select></TD>

<TR>
            <TD ALIGN="right" class="TrackNormalBlue">Origin : </TD>
            <TD>&nbsp;</TD>
            <TD><INPUT NAME="Origin" TYPE=TEXT id="Origin" maxlength="50" SIZE=40>
                <span class="REDLink">*</span> </TD>
          </TR>
          <TR>

            <TD ALIGN="right" class="TrackNormalBlue">Destination :</TD>
            <TD>&nbsp;</TD>
            <TD><INPUT NAME="Destination" TYPE=TEXT id="Destination" maxlength="50" SIZE=40>
                <span class="REDLink">*</span> </TD>
          </TR>

<TR>
            <TD ALIGN="right" class="TrackNormalBlue">Rate : </TD>
            <TD>&nbsp;</TD>
            <TD><INPUT NAME="Rate" TYPE=TEXT id="Rate" maxlength="50" SIZE=40>
                <span class="REDLink"></span> </TD>
          </TR>

If ($TimeWarranty == "24Hours") {
$query4 = "SELECT `24hrs` FROM `Rate` WHERE `Origin` = '$Origin' AND Destination = '$Destination' AND Type = '$Packagetype' ";
$result3 = mysql_query($query4) or die("Could not perform select query - " . mysql_error());
($row = mysql_fetch_array($result3, MYSQLI_ASSOC));
$x = "24hrs";
}
else if ($TimeWarranty == "2Days") {
$query5 = "SELECT `2days` from `Rate` WHERE `Origin` = '$Origin' AND  `Destination` = '$Destination' AND `Type` = '$Packagetype' ";
$result3 = mysql_query($query5) or die("Could not perform select query - " . mysql_error());
($row = mysql_fetch_array($result3, MYSQLI_ASSOC));
$x = "2days";
    }
else if ($TimeWarranty == "3Days") {
$query6 = "SELECT `3days` from `Rate` WHERE `Origin` = '$Origin' AND `Destination` = '$Destination' AND `Type` = '$Packagetype'";
$result3 = mysql_query($query6) or die("Could not perform select query - " . mysql_error());
($row = mysql_fetch_array($result3, MYSQLI_ASSOC));
$x = "3days";
    }
else if ($TimeWarranty == "7Days") {
$query7 = "SELECT `7days` from `Rate` WHERE `Origin` = '$Origin' AND `Destination` = '$Destination' AND `Type` = '$Packagetype'";
$result3 = mysql_query($query7) or die("Could not perform select query - " . mysql_error());
($row = mysql_fetch_array($result3, MYSQLI_ASSOC));
$x = "7days";
    }
else if ($TimeWarranty == "14Days") {
$query8 = "SELECT `14days` from `Rate` WHERE `Origin` = '$Origin' AND `Destination` = '$Destination' AND `Type` = '$Packagetype'";
$result3 = mysql_query($query8) or die("Could not perform select query - " . mysql_error());
($row = mysql_fetch_array($result3, MYSQLI_ASSOC));
$x = "14days";
    }
    else {
        echo ("Time Warranty not found");
    }

Rate: <?php echo $row[$x] ?> <BR>

Open in new window

0
 
Vel EousResearch & Development ManagerCommented:
Sorry, I left a copy & paste error on line 97;

>>  $destination = $_POST['packageType'];

Should be

$packageType = $_POST['packageType'];
0
 
Dexter_1Author Commented:
I'll check out the code tomorrow and accept the solution.

Thanks for all your help!
0
All Courses

From novice to tech pro — start learning today.