use ajax to query mysql db onchange event from php drop down menu

I want to do an ajax or javascript query of db based on what is select from php based drop down menu.

If user selects fut_sym = abc, then displaythe price in an element like this <td ' id="priceDisplay"></td>
This form creates a drop down menu from names in db
 
<form>
	<table width = '980px' border='0px'>
			<TD ><select name="fut_sym" onchange="changeSymbol()">
            <?php 
			//query to populate expdate drop down menu on submit form
				$query = "SELECT DISTINCT `fut_sym` FROM `dbTable`
				WHERE `prod_type` != 'xxx'
				ORDER BY `fut_sym`";
				$sql = mysql_query("$query");
                $num = mysql_num_rows($sql);
				$i=0;
                      while ($i < $num) {
						$item = mysql_result($sql,$i,"fut_sym");
						$i++;
						echo  "<option value=\"$item\" ";
						if ($fut_sym == $item){ echo " selected ";}  
						echo ">".$item."</option>";   }
           ?>
		   </select>
			</TD>
	</table>
</form>
 
<script type="text/javascript">
	function changeSymbol()
	{
	query db select price from dbtable
                 where fut_sym = symbol from form
        var price = query results
        document.getElementById('priceDisplay').innerHTML = price;
	}

Open in new window

mcgilljdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bui_trung_hieuCommented:
Please try this code below

On actions.php, you write out only your query results.

First page : 
 
<form>
        <table width = '980px' border='0px'>
                        <TD ><select name="fut_sym" onchange="changeSymbol(this.value)">
            <?php 
                        //query to populate expdate drop down menu on submit form
                                $query = "SELECT DISTINCT `fut_sym` FROM `dbTable`
                                WHERE `prod_type` != 'xxx'
                                ORDER BY `fut_sym`";
                                $sql = mysql_query("$query");
                $num = mysql_num_rows($sql);
                                $i=0;
                      while ($i < $num) {
                                                $item = mysql_result($sql,$i,"fut_sym");
                                                $i++;
                                                echo  "<option value=\"$item\" ";
                                                if ($fut_sym == $item){ echo " selected ";}  
                                                echo ">".$item."</option>";   }
           ?>
                   </select>
                        </TD>
        </table>
</form>
 
<script type="text/javascript">
      function changeSymbol(_value)
{
	try {
		var HttpRequest = window.XMLHttpRequest ? new XMLHttpRequest() : new ActiveXObject("Microsoft.XMLHTTP");
		if (!HttpRequest) return;
		var actionURL = "actions.php?value="+ window.encodeURIComponent(_value)+ "&rd=" + Math.random();
		HttpRequest.open("GET",actionURL);
		HttpRequest.onreadystatechange = function()
		{
		   if (HttpRequest.readyState == 4 && HttpRequest.status == 200)
		   {
			  document.getElementById('priceDisplay').innerHTML = HttpRequest.responseText;
		   }
		}
		HttpRequest.send(null);
		return;
	}catch(ex){}
}

Open in new window

hieloCommented:
There are two files attached. Your main page - where the select exists, and the prices.php - where you are supposed to query the db for the price based on the item selected.
mainpage.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd"> 
<html>
<head>
<title></title>
<script type="text/javascript">
var request=null;
function GetXmlHttpObject()
{
var xmlHttp=null;
try
  {
  // Firefox, Opera 8.0+, Safari
  xmlHttp=new XMLHttpRequest();
  }
catch (e)
  {
  // Internet Explorer
  try
    {
    xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
    }
  catch (e)
    {
    xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
    }
  }
return xmlHttp;
} 
function changeSymbol(val)
{
	if( request=GetXmlHttpObject() )
	{
		request.open("GET",'prices.php?fut_sym='+ encodeURIComponent(val), true );
		request.onreadystatechange=function()
		{
			if( request.readyState==4 && request.status==200)
			{
				updatePrice(request.responseText);
			}
		}
	}
	
}
function updatePrice(price)
{
	document.getElementById('priceDisplay').innerHTML = price;
	request = null;
}
</script> 
</head>
<body>
<form>
      <table width = '980px' border='0px'>
                  <TD ><select name="fut_sym" onchange="changeSymbol( this.value )">
            <?php 
                  //query to populate expdate drop down menu on submit form
                        $query = "SELECT DISTINCT `fut_sym` FROM `dbTable`
                        WHERE `prod_type` != 'xxx'
                        ORDER BY `fut_sym`";
                        $sql = mysql_query("$query");
                $num = mysql_num_rows($sql);
                        $i=0;
                      while ($i < $num) {
                                    $item = mysql_result($sql,$i,"fut_sym");
                                    $i++;
                                    echo  "<option value=\"$item\" ";
                                    if ($fut_sym == $item){ echo " selected ";}  
                                    echo ">".$item."</option>";   }
           ?>
               </select>
                  </TD>
      </table>
</form>
</body>
</html> 
  
 

prices.php
<?php
//put your db connection info here 
$result = mysql_query("select price from dbtable where `fut_sym`='" . mysql_real_escape_string($_REQUEST['fut_sym']) . "'") or die(mysql_error());
if( mysql_num_rows($result) > 0)
{
	$row = mysql_fetch_assoc($result);
	echo $row['price'];
}
else
{
	echo "0.00";
}
exit;
?>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcgilljdAuthor Commented:
What should actions.php look like?
bui_trung_hieuCommented:
My actions.php look like the same with prices.php in hielo's previous post.

On actions.php, you only write out what you want to display in "priceDisplay" div. Because after calling AJAX function, the HttpRequest object will execute that page, and then, retrieve all rendered text to property HttpRequest.responseText.

<?php
//put your db connection info here 
$result = mysql_query("select price from dbtable where `fut_sym`='" . mysql_real_escape_string($_REQUEST['fut_sym']) . "'") or die(mysql_error());
if( mysql_num_rows($result) > 0)
{
        $row = mysql_fetch_assoc($result);
        echo $row['price'];
}
else
{
        echo "0.00";
}
exit;
?>

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.