connecting two SQL query with Javascript

fic · Apr 10, 2000

I`ve got two tables in a database called countrys and cities. There is a select tag for each table with their SQL querys. If I select United States in the country select I want the cities select to be refreshed so i can only see the cities that belong to United States. Is there anyway to do this dynamically? Thanx.

DarkMan · Apr 11, 2000

You could do this with frames...   FRAME ONE <cfquery name="Countries" datasource="mydb">   select CountryID,Country from Country_Table   order by Country </cfquery> <form action="frame2.cfm" method="post" target="frame2">   <select name="CountryID">     <cfoutput query="Countries">       <option value="#CountryID#">#Country#</option>     </cfoutput>   </select>   <input type="submit" value="GO"> </form> FRAME TWO <cfparam name="CountryID" default=0> <cfif stateid>   <cfquery name="cities" datasource="mydb">     select CityID,City from Cities     where CountryID=#CountryID#     order by City   </cfquery>   <form action="frame3.cfm" method="post" target="frame3">     <select name="CityID">       <cfoutput query="cities">         <option value="#CityID#">#City#</option>       </cfoutput>     </select>     <input type="submit" value="GO">   </form> </cfif> FRAME THREE <cfparam name="CityID" default=0> <cfif CityID>   <cfquery name="city" datasource="mydb">      select * from cities      where CityID=#CityID#   </cfquery>   <cfoutput query="city">        </cfoutput> </cfif> Let me know if this works for you.  (btw, you could put javascript code in the select boxes to submit the form upon selection, it should still work...)

bigfella · Apr 13, 2000

I'm fairly new to CF so it took me quite some time to work this one out. This particular section uses Combo boxes (I don't know if that's how you wanted to do it).It works by using the CFWDDX cfml2js Action tag to output the query results into a javascript array. Javascript can then be used to dynamically update the combo's. Bit long, but it works. <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script language="JavaScript"> <CFINCLUDE template='/CFIDE/scripts/wddx.js'> // used in refereshTable() to remember the length of the previous array var oldLength = 0; function refreshTable() { var selectedArray = eval(document.genquery.chooseTable.value + "Array&quot

; // empty the contents of the options list for (var i=0; i < oldLength; i++) { document.genquery.chooseField.options = null; } // loop through the array, dynamically creating the 2nd combo options for (var i=0; i < selectedArray.length; i++) { /* the \u codes in this line are unicodes for special characters, in this case ( ' ) */ eval("document.genquery.chooseField.options = new Option \u0028\u0027" + selectedArray[0] + "\u0027,\u0027" + selectedArray[1] + "\u0027\u0029&quot

; } oldLength = selectedArray.length; } </script> </head> <body bgcolor="#FFFFFF"> <cfquery name="getTables" datasource="Intranet" dbtype="ODBC"> select * from TableList where tName = 'All' order by fValue </cfquery>  <cfquery name="getOrders" datasource="Intranet" dbtype="ODBC"> select tFields,fValue from TableList where tName = 'Orders' order by fValue </cfquery> <cfquery name="getQuotes" datasource="Intranet" dbtype="ODBC"> select tFields,fValue from TableList where tName = 'Quotes' order by fValue </cfquery> <script language="javascript"> ///////////////////////////////////////////// /* generate an array for each query result */ ///////////////////////////////////////////// <CFWDDX ACTION='cfml2js' input=#getOrders# topLevelVariable='q'> // get the number of rows from the query (getRowCount() comes from the above template file) var nRows = q.getRowCount(); // Init Array var oentArray = new Array(); // Init array's 2nd dimension for (i=0; i < nRows; i++) { oentArray = new Array(2); } // read descriptions and values into array for (i=0; i<nRows; i++) { oentArray[0] = q.getField(i, 'tfields'); oentArray[1] = q.getField(i, 'fvalue'); } <CFWDDX ACTION='cfml2js' input=#getQuotes# topLevelVariable='q'> // get the number of rows from the query (getRowCount() comes from the above template file) var nRows = q.getRowCount(); // Init Array var qenqArray = new Array(); // Init array's 2nd dimension for (i=0; i < nRows; i++) { qenqArray = new Array(2); } // read descriptions and values into array for (i=0; i<nRows; i++) { qenqArray[0] = q.getField(i, 'tfields'); qenqArray[1] = q.getField(i, 'fvalue'); } </script> <form method="post" action="" name="genquery">   <select name="chooseTable" onChange="refreshTable()">    <option selected value=""></option>     <cfoutput query="getTables"> <option value="#fValue#">#tFields#</option> </cfoutput>   </select>   <select name="chooseField">   </select>   <input type="button" value="Show Value..." onClick="alert(document.genquery.chooseField.value)"> </form> </body> </html>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

connecting two SQL query with Javascript

fic

Programmer

DarkMan

Programmer

bigfella

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor