asked on

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

bui_trung_hieu

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

ASKER CERTIFIED SOLUTION

hielo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcgilljd

ASKER

What should actions.php look like?

bui_trung_hieu

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