Hi there
I have now got part of this working.
Basically I have got 3 tables in the database now:
Tbl_staff, tbl_practiceareas and tbl_staffpracareas
Tbl_staffpracareas is a link table to link staff to practice areas.
I have got the following query:
<cfquery name="qGetPracticeAreas" datasource="#request.db_dsn#"
username="#request.db_login#" password="#request.db_pwd#">
SELECT p.practiceid, p.practicearea, s.staffid
FROM tbl_practiceareas p LEFT JOIN tbl_staffpracareas s ON p.practiceid =
s.practiceid AND s.staffid = #url.staffid# ORDER BY p.practicearea
</cfquery>
Then I display the results on the page (this is an update page)
<table width="90%" align="left" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2"> </td>
</tr>
<cfoutput query="qGetPracticeAreas">
<tr>
<td width="10%"><input <cfif (#staffid# IS NOT
"")>checked="checked"</cfif> name="#qGetPracticeAreas.practicearea#"
type="checkbox" value='1'></td>
<td width="90%"
class="OrderFormName">#qGetPracticeAreas.practicearea#</td>
</tr>
</cfoutput>
</table>
So basically my question is this
I do an update of the tbl_staff table as follows:
<cfquery name="qUpdateStaff" datasource="#request.db_dsn#"
username="#request.db_login#" password="#request.db_pwd#">
UPDATE tbl_staff
SET firstname = '#form.firstname#',
lastname = '#form.lastname#',
staffposition = '#form.staffposition#',
staffquals = '#form.staffquals#',
staffprofmbrships = '#form.staffprofmbrships#',
phone = '#form.phone#',
fax = '#form.fax#',
email = '#form.email#',
sortorder = '#form.sortorder#',
onweb = '#onweb#',
staffbio = '#form.staffbio#',
staffpic = '#form.staffpic#',
phototext = '#form.phototext#',
dateupdated =
#CreateODBCDateTime(form.dateupdated)#,
updatedby = '#session.auth.name#'
WHERE staffid = #form.staffid#
</cfquery>
I am thinking that I should probably put the code above within a
<cftransaction> tag. Then when I do the update I also would update the
tbl_staffpracareas table.
I have a question - I am presenting the information to the user OK, as far
as the correct checkboxes being selected. However if a user changes the
checkboxes were initially selected then just doing an update would not work.
I am thinking that once a user presses the Update button that I need to
delete all records in the tbl_staffpracareas for a specific staffed and then
insert the correct records into the table.
Any comments would be greatfully accepted.
Cheers
-----Original Message-----
From: Jim Wright [mailto:[EMAIL PROTECTED]
Sent: Friday, 24 November 2006 1:39 PM
To: CF-Talk
Subject: Re: multiple selection drop down list
Peter Tanswell wrote:
>
> I will probably save the selected practice areas in one field
> probably as a string.
I would recommend against this...create a table to store the individual
practice areas associated with a employee...something like
tblEmployeePracticeArea
employeeid
practiceid
Then when editing, you could do your query as something like...
<cfquery name="qGetPracticeAreas" datasource="#request.db_dsn#"
username="#request.db_login#" password="#request.db_pwd#">
SELECT p.practiceid,p.practicearea,e.employeeid
FROM tbl_practiceareas p LEFT JOIN tblEmployeePracticeArea e ON
p.practiceid = e.practiceid AND e.employeeid = #employeeiamediting#
ORDER BY p.practicearea
</cfquery>
Then in your select...
<select name="practicearea" size="4" multiple="multiple">
<option value="0">Please Select</option>
<cfoutput query="qgetpracticeareas">
<option value="#practiceid#"<cfif employeeid IS NOT ""> selected</cfif>>
#practicearea#</option></cfoutput>
</select>
You might also want to think about using check boxes instead of the
multi select list. I've found that users often find the holding of the
"ctrl" key thing to be cumbersome.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261626
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
