SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    California
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dynamically updating a table using SQL on the same webpage

    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">&nbsp;</TD>
    <TD colspan="2">Please select a Stake and/or Wardbr><Font size="-1">&nbsp;&nbsp;&nbsp;&nbsp;[Hold down Ctrl for multiple selections]</Font></TD>
    <TD>&nbsp;</TD>
    <TD colspan="2">Please select a Calling(s)br><Font size="-1">&nbsp;&nbsp;&nbsp;&nbsp;[Hold down Ctrl for multiple selections]</Font></TD>
    </TR>
    <TR>
    <TD width="5">&nbsp;</TD>
    <TD width="13">&nbsp;</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">&nbsp;</TD>
    <TD width="13">&nbsp;</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">&nbsp;</TD>
    <TD width="13">&nbsp;</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">&nbsp;&nbsp;<input name="Reset" type="button" value="Reset"">&nbsp;&nbsp;
    <font size="-1">select some things and click submit</font>&nbsp;&nbsp;
    <input name="Submit" type="button" value="Submit" onclick="processSubmit();">&nbsp;&nbsp;</TD>
    </TR>
    <td height="22"></TR>
    <TR><TD colspan="6">Selected Stake/Ward(s):&nbsp;&nbsp;<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):&nbsp;&nbsp;<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>

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    way too much code for people to have to read to help you

    btw, what's your question?



    incidentally, you can replace this --

    <cfloop query="fillbox">
    <cfoutput>
    <option value="#ID#">#Description#</option>
    </cfoutput>
    </cfloop>

    with this --

    <cfoutput query="fillbox">
    <option value="#ID#">#Description#</option>
    </cfoutput>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    California
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    way too much code for people to have to read to help you

    btw, what's your question?

    Sorry about that. what I want is for the 2 text boxes on the screen to be able to dynamically update the webpage with the results from the search page. If possible I want this to be done on the same web page. Hope this helps out

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm afraid that's not a question, that's a request for someone to do development work for you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •