Phillip, her original code works, she just needs to make the form's method
"post"
----- Original Message -----
From: "Philip Arnold - ASP" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, February 27, 2001 3:49 AM
Subject: RE: Building Cold Fusion Search - Dynamic SQL
> OK, I'm going to break this down bit by bit
>
> > I'm trying to build some sql statements based on selections from
> > a list box:
> >
> > <select name="frmSkill">
> > <option> Skill1
> > <option> Skill2
> > <option> Skill3
> > <option> Skill4
> > (etc....this size will vary as it's dynamically populated)
> > </select>
> >
> > A user can do a multiple select, so let's say the user picked option 1
and
> > option 3.
>
> Is there a huge list of skills? If not, do it via Checkboxes as multiple
> selects really confuse people
>
> > On my action page, I want to pick ONLY the employees that have
> > the 2 skills selected.
> >
> > If I use an IN statement, I get employees with one skill or the
> > other, but I only want those with both:
> >
> > SELECT e.empId,es.skillId
> > from emp e,empSkills es
> > where skillId IN (#frmSkill#)
> > AND e.empId=es.empId
> >
> > How can I write the SQL to select only the employees with both,
> > or do I use Cold Fusion to do that?
>
> IN is effectively an OR, that's what it's there for... if you want to do
an
> AND it's going to be a lot more complicated
>
> I can't think of a quick way to do this in SQL, it might be a mixture of
CF
> and SQL (this code is ugly, but it'll work
>
> <cfquery name="myQuery" datasource="myDSN">
> select e.empId,es.skillId
> from emp e,empSkills es
> where skillId IN (#frmSkill#)
> AND e.empId=es.empId
> order by e.empId,es.skillId
> </cfquery>
> <cfscript>
> // Start with an array
> empArray=ArrayNew(1);
> // currently no person and skills
> thisEmp=0;
> thisEmpSkills="";
> // loop through the data
> for (i=1; i lte myQuery.RecordCount; i=i+1)
> {// if we're not building for the current employee, add the last
employee's
> stuff into the array
> if (myQuery.empID[i] neq thisEmp)
> {// If it's not blank
> if (thisEmp neq 0)
> {// build it in a structure as it's easier to get info out of
> tmpStruct=StructNew();
> tmpStruct.empID=thisEmp;
> tmpStruct.SkillList=thisEmpSkills;
> // add it to the array
> ArrayAppend(empArray,thisStruct);
> }
> // we're looking at "this" employee
> thisEmp=myQuery.empID[i];
> // start with blank skills
> thisEmpSkills="";
> }
> // add the current skill to the list
> thisEmpSkills=ListAppend(thisEmpSkills,myQuery.skillID[i]);
> }
> // Add the last person's entries to the array
> tmpStruct=StructNew();
> tmpStruct.empID=thisEmp;
> tmpStruct.SkillList=thisEmpSkills;
> ArrayAppend(empArray,thisStruct);
> </cfscript>
>
> You'll now have an array of the employees with their skills - so loop
> through them and remove the ones who don't have all of the skills
>
> As I say, ugly, but it should work
>
> Philip Arnold
> Director
> Certified ColdFusion Developer
> ASP Multimedia Limited
> T: +44 (0)20 8680 1133
>
> "Websites for the real world"
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
> **********************************************************************
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
