Hello all, I am rather new to SQL. But so far I have figured out some of it. I am having a problem on my Cold Fusion page, dynamically updating a table/list of people depending on certain search criteria which I want the user to be able to change. PLEASE HELP ME!!
Here's my code so far:
<cfquery name="Names" DATASOURCE="#dsn#" username="#dsnuser#" password="#dsnpass#">
SELECT personnel.firstname AS firstname, personnel.lastname as lastname, personnel.wardID as PWardID, personnel.callingID as PCallingID
FROM personnel
ORDER BY personnel.lastname
WHERE personnel.wardID = document.SelectForm.SelectedStakeWard.value and personnel.callingID = document.SelectForm.SelectedCallings.value
</cfquery>
I am using 2 listboxes that are dynamically updated from my MySQL server off of the 2 tables called ward & callings. The user can then select multiple items out of either list. When the user clicks the submit button, it goes through the list and adds the ID numbers together into a string (e.g. the string would be 11or13or111or15) I am trying to get SQL to read that and update a list on the same webpage dependent on this information. The complete code for the page is as follows:
<cfinclude template="../includes/Include.cfm">
<cfoutput>#myhiarchy#</cfoutput>
<HTML>
<Head>
<Title>Test - Selection for Stake/Ward & Callings</Title>
<!-----------BEGIN FUNCTION----------->
<cfquery name="fillbox" DATASOURCE="#dsn#" username="#dsnuser#" password="#dsnpass#">
SELECT calling.callingdesc AS Description, calling.callingID AS ID, userrole.roledesc, calling.hiarchy, calling.ismiss
FROM calling
INNER JOIN userrole ON calling.roleID = userrole.roleID
WHERE calling.hiarchy <300
</cfquery>
<cfquery name="fillbox2" DATASOURCE="#dsn#" username="#dsnuser#" password="#dsnpass#">
SELECT ward.wardID AS ID, stake.stakename as StakeName, ward.wardname AS UnitName
FROM ward
INNER JOIN stake ON ward.stakeID = stake.stakeID
ORDER BY stake.stakename
</cfquery>
<cfquery name="fillbox3" DATASOURCE="#dsn#" username="#dsnuser#" password="#dsnpass#">
SELECT ReportTable.ReportID As ReportID, ReportTable.ReportName AS ReportName
FROM ReportTable
</cfquery>
<cfquery name="Names" DATASOURCE="#dsn#" username="#dsnuser#" password="#dsnpass#">
SELECT personnel.firstname AS firstname, personnel.lastname as lastname, personnel.wardID as PWardID, personnel.callingID as PCallingID
FROM personnel
ORDER BY personnel.lastname
WHERE personnel.wardID=11 or personnel.wardID=15
</cfquery>
</Head>
<Body>
<Form Name="SelectForm">
<Table border=0>
<TR>
<TD width="5"> </TD>
<TD colspan="2">Please select a Stake and/or Wardbr><Font size="-1"> [Hold down Ctrl for multiple selections]</Font></TD>
<TD> </TD>
<TD colspan="2">Please select a Calling(s)
</TR>
<TR>
<TD width="5"> </TD>
<TD width="13"> </TD>
<TD width="220"><select name="UnitName" size="6" multiple>
<cfloop query="fillbox2">
<cfoutput>
<option value="#ID#">#StakeName# - #UnitName#</option>
</cfoutput>
</cfloop></select>
</TD>
<TD width="5"> </TD>
<TD width="13"> </TD>
<TD width="220"><select name="CallingID" size="6" multiple>
<cfloop query="fillbox">
<cfoutput>
<option value="#ID#">#Description#</option>
</cfoutput>
</cfloop></select></TD>
<TD width="5"> </TD>
<TD width="13"> </TD>
<TD width="220"><select name="Names" size="6" multiple>
<cfloop query="Names">
<cfoutput>
<option value="#firstname#">#PCallingID# #lastname# #firstname# (#PWardID#)</option>
</cfoutput>
</cfloop></select></TD>
</TR>
<TR><TD HEIGHT="8"></TD></TR>
<TR>
<TD colspan="6" align="right"> <input name="Reset" type="button" value="Reset"">
<font size="-1">select some things and click submit</font>
<input name="Submit" type="button" value="Submit" onclick="processSubmit();"> </TD>
</TR>
<td height="22"></TR>
<TR><TD colspan="6">Selected Stake/Ward(s): <font size="-1">Their values at least</font><Br>
<input name="SelectedStakeWard" type="text" value="" size="78"></TD></TR>
<TR><TD colspan="6">Selected Calling(s): <font size="-1">Their values at least</font><BR>
<input name="SelectedCalling" type="text" value="" size="78"></TD>
</TR>
</Table><BR><HR><BR>
</Form>
<script language="javascript">
function processSubmit()
{
checkStakeWards();
checkCalling();
with (document.SelectForm);
{
var count
var count = (document.SelectForm.SelectedStakeWard.value.length);
var newstring
var newstring = (document.SelectForm.SelectedStakeWard.value.substring(0, count-4));
(document.SelectForm.SelectedStakeWard.value) = newstring
var count = (document.SelectForm.SelectedCalling.value.length);
var newstring = (document.SelectForm.SelectedCalling.value.substring(0, count-4));
(document.SelectForm.SelectedCalling.value) = newstring
}
alert('Ready for the filter Elder Meyer!');
}
function checkStakeWards()
{
var count
var oldvalue = "";
with (document.SelectForm)
{
for (count=0; count<UnitName.options.length; count++)
{
// mark as selected
if (UnitName.options[count].selected==true) {addStake(UnitName.options[count].value)};
//if (UnitName.options[count].selected==true) {alert(UnitName.options[count].value + ' It is True'); addStake(UnitName.options[count].value)};
}
}
}
function addStake(passedvalue)
{
with (document.SelectForm)
{
var oldvalue = document.SelectForm.SelectedStakeWard.value
var totalvalue = "personnel.wardID="+passedvalue+" or "+oldvalue;
document.SelectForm.SelectedStakeWard.value = totalvalue;
var passedvalue = ""
}
}
function checkCalling()
{
var count
var oldvalue = "";
with (document.SelectForm)
{
for (count=0; count<CallingID.options.length; count++)
{
// mark as selected
if (CallingID.options[count].selected==true) {addCalling(CallingID.options[count].value)};
//if (CallingID.options[count].selected==true) {alert(CallingID.options[count].value + ' It is True'); addCalling(CallingID.options[count].value)};
}
}
}
function addCalling(passedvalue)
{
with (document.SelectForm)
{
if (passedvalue != "") {
if (passedvalue != null) {
var oldvalue = document.SelectForm.SelectedCalling.value
var totalvalue = "personnel.callingID="+passedvalue+" or "+oldvalue;
document.SelectForm.SelectedCalling.value = totalvalue;} }
var passedvalue = ""
}
}
function ViewNames()
{
}
</script>
</Body>
</HTML>
Originally Posted by r937
Bookmarks