Solved

Coldfusion and JavaScript question

Posted on 2004-11-30
8
167 Views
Last Modified: 2013-12-24
Hi,

I wanted to know.

Is it possible to have a query that for example returns 5 firstnames and 5 lastnames

Like:

<cfquery name="getNames" datasource="#ds#">
select firstname, lastname
from contacts
</cfquery>

The results would be:
Martin Short
Bob Beep
Joe Doe
Mary Lou
Rick Smith

---

I then want to pass these names to javascript in the form of an array or structure or something.  I have this drop down, where a user chooses a company name, based on that name i want to display a list of firstname/lastname's associated with that company, however I am trying to do this without trips to the server.  The query that gets the names is run once, and it loads all the companys and contacts associated with each company, so I have all these available.  I want the user to people to choose a new company in the drop down and then have some "onChange" function that would pull the contacts from each company and display them on the page without querying the db again.

How can I do this.. I hope i made sense.

-ws
0
Comment
Question by:Westside2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 2

Expert Comment

by:dcodez
ID: 12710503
ws,

The following code is an example of how you can create 2 simple select lists and update them based on the options that are selected.

Based on the info you provided above, I assume you have a seperate db table that stores companies and that the contacts table has a companyID to link the contacts to a company.  If that's not the case, I'm sure you can easily adapt the following code to your db schema and make it work.



<cfquery name="getInfo" DataSource="#Request.DataSource#" username="#Request.DataSourceLogin#" password="#Request.DataSourcePass#">
      select *
      from contacts cons inner join testcompanies tc on cons.companyID = cons.companyID            
      order by cons.companyID      
</cfquery>
<script language="JavaScript"><!--
       ArrayOfCompanies = new Array();
      
       
 <cfoutput query="getInfo" group="companyID">
      tmpCompany = new Object();
      tmpCompany.cname =  '#companyname#';
       tmpCompany.empList = new Array();
      <cfoutput>
            tmpCompany.empList[tmpCompany.empList.length]='#name#';
      </cfoutput>
      ArrayOfCompanies[ArrayOfCompanies.length] = tmpCompany;
</cfoutput>  
function updateCompany(x){
       if(x >= 0){
            testform.empSelect.disabled = false;
            testform.empSelect.options.length = 0;
            empList = ArrayOfCompanies[x].empList;
            for(i=0;i<empList.length;i++){
                  testform.empSelect.options[i] = new Option(empList[i],empList[i]);  
            }
      }  
      else{
            testform.empSelect.disabled = true;      
            testform.empSelect.options.length = 0;
            testform.empSelect.options[i] = new Option('Select Company Above','Select Company Above');  
            
      }
}

--></script>

<form name="testform">
<select name="compSelect" onchange="updateCompany(this.selectedIndex -1)">
      <option>Select Company</option>
      <cfoutput query="getInfo" group="companyID">
            <option>#companyname#</option>
      </cfoutput>
</select>
<br>
<select name="empSelect" disabled>
      <option>Select Company Above</option>
</select>

</form>
0
 
LVL 2

Expert Comment

by:dcodez
ID: 12710524
ws,

I had a couple of typos in that last code example.  I've pasted an updated version below.   You can also see the code in action here: http://www.cnsusa.com/untitled.cfm





<cfquery name="getInfo" DataSource="#Request.DataSource#" username="#Request.DataSourceLogin#" password="#Request.DataSourcePass#">
      select *
      from contacts cons inner join testcompanies tc on tc.companyID = cons.companyID            
      order by cons.companyID      
</cfquery>
<script language="JavaScript"><!--
       ArrayOfCompanies = new Array();
      
       
 <cfoutput query="getInfo" group="companyID">
      tmpCompany = new Object();
      tmpCompany.cname =  '#companyname#';
       tmpCompany.empList = new Array();
      <cfoutput>
            tmpCompany.empList[tmpCompany.empList.length]='#name#';
      </cfoutput>
      ArrayOfCompanies[ArrayOfCompanies.length] = tmpCompany;
</cfoutput>  
function updateCompany(x){
       if(x >= 0){
            testform.empSelect.disabled = false;
            testform.empSelect.options.length = 0;
            empList = ArrayOfCompanies[x].empList;
            for(i=0;i<empList.length;i++){
                  testform.empSelect.options[i] = new Option(empList[i],empList[i]);  
            }
      }  
      else{
            testform.empSelect.disabled = true;      
            testform.empSelect.options.length = 0;
            testform.empSelect.options[0] = new Option('Select Company Above','Select Company Above');  
            
      }
}

--></script>

<form name="testform">
<select name="compSelect" onchange="updateCompany(this.selectedIndex -1)">
      <option>Select Company</option>
      <cfoutput query="getInfo" group="companyID">
            <option>#companyname#</option>
      </cfoutput>
</select>
<br>
<select name="empSelect" disabled>
      <option>Select Company Above</option>
</select>

</form>
0
 
LVL 1

Author Comment

by:Westside2004
ID: 12712268
Hi,

This does make sense, however I want to have the name displays as hyperlinks as opposed to a drop down.

The companies should be in a drop down as you have it, but the individual contacts within each company, should be listed as hyperlinks if possible.

Then when you click the hyperlink (which is the user's firstname and lastname) it will drill down and show the individual record details.  I can do the drilling down, etc, but I wanted to have the ability to choose a different company and have the firstname lastname come up without requerying the database each time.

Please let me know..

in the meantime I will try to modify your code (the 2nd drop down) and convert it to a list of hyperlinks...

-ws
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 2

Expert Comment

by:dcodez
ID: 12718130
ws,

yes this is possible, I'll post a code snippet soon modified with hyperlinks.
0
 
LVL 2

Accepted Solution

by:
dcodez earned 250 total points
ID: 12718245
Here I use a div to fill with hyperlinks that show the name.  The links don't go anywhere since you mentioned that you would handle the drill down, etc.  This should give you what you need to work with.

http://www.cnsusa.com/untitled.cfm



<cfquery name="getInfo" DataSource="#Request.DataSource#" username="#Request.DataSourceLogin#" password="#Request.DataSourcePass#">
      select *
      from contacts cons inner join testcompanies tc on tc.companyID = cons.companyID            
      order by cons.companyID      
</cfquery>
<script language="JavaScript"><!--
       ArrayOfCompanies = new Array();
      
       
 <cfoutput query="getInfo" group="companyID">
      tmpCompany = new Object();
      tmpCompany.cname =  '#companyname#';
       tmpCompany.empList = new Array();
      <cfoutput>
            tmpCompany.empList[tmpCompany.empList.length]='#name#';
      </cfoutput>
      ArrayOfCompanies[ArrayOfCompanies.length] = tmpCompany;
</cfoutput>  
function updateCompany(x){
       if(x >= 0){
            empList = ArrayOfCompanies[x].empList;
            docstring = '';
            for(i=0;i<empList.length;i++){  
            docstring = docstring +      '<a href="">'+empList[i]+'</a><br>';
            }
             document.all.emps.innerHTML = docstring;
      }  
      
}

--></script>

<form name="testform">
<select name="compSelect" onchange="updateCompany(this.selectedIndex -1)">
      <option>Select Company</option>
      <cfoutput query="getInfo" group="companyID">
            <option>#companyname#</option>
      </cfoutput>
</select>
<br>


<div ID="emps">

</div>

 
</form>
0
 
LVL 2

Expert Comment

by:dcodez
ID: 12718270
oops, i left out.  but it should make sense.


if(x >= 0){
...}
else{
 document.all.emps.innerHTML = "";
}
0
 
LVL 1

Author Comment

by:Westside2004
ID: 12722968
Hey..

That worked.. thanks a bunch..

So to recap...

Basically you pull all the data you need once, and then build an array with it.  Than you manipulated the array?

thx

-Ws
0
 
LVL 1

Author Comment

by:Westside2004
ID: 12900091
Hi,

I did have one question, regarding the way the links are outputted.

How can I output the "contactID" for example.. in the query I pull a contactID

For my drill down pages I will need this contact ID

function updateCompany(x){
      if(x >= 0){
          empList = ArrayOfCompanies[x].empList;
          docstring = '';
          for(i=0;i<empList.length;i++){  
          docstring = docstring +     '<a href="">'+empList[i]+'</a><br>';
          }
           document.all.emps.innerHTML = docstring;
     }  
     
}

I see the <a href=""> and I tried wrapping that function in a <cfoutput> and then using queryname.contactID, but I ended up getting multiple names in my list.  Any idea on how I can add a contactID to the <a href> so when each firstname, lastname is display, when you roll over it, you will see the contactID as part of the URL..

Thanks

-ws
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question