> How do I dynamically replace text in multiple rows in my
> database? Background: Have two tables:
> Table 1 fields include: ContactID, Groups
> The groups field is a text field that is filled dynamically
> through a series of checkboxes. Example, the field might contain
> a text string like, "Group1, Group2, Group4".
>
> Table 2 fields include: GroupID, GroupName
>
> The user has the option to edit a group name in table two through
> an editing form. I can get it to update table two through a standard
> CFUPDATE, but I'm having trouble getting the changed group name to
> filter through the table 1 "Goups" field. I've tried using the replace
> function in an Update, but the results are not good.
>
> <cfquery datasource=3D"datasource">
> UPDATE Table1
> SET Groups=3D'#Replace(Groups, "OldGroup", "GroupName", "ALL")#'
> </cfquery>
>
> Where OldGroup=Group name before change and GroupName=Group name
> after change
I've got two answers for you.
1. You can do this by finding each row in Table 1 that has a match,
retrieving the string for that row, doing your replace, then updating that
row. So, if you've got twenty rows that referenced a group with a changed
name, you'll end up executing twenty update statements. You can do this
within CF, or better yet within your database if you can write a stored
procedure. Here's what it might look like in CF:
<cfquery name="GetChangedRows" datasource="foo">
SELECT ContactID, Groups
FROM Table1
WHERE Groups LIKE '%#NewGroupName#%'
</cfquery>
<cfloop query="GetChangedRows">
<cfset NewGroupList = Replace(GetChangedRows.Groups, OldGroup,
NewGroupName)>
<cfquery name="UpdateRow" datasource="foo">
UPDATE Table1
SET Groups = '#NewGroupList#'
WHERE ContactID = #GetChangedRows.ContactID#
</cfquery>
</cfloop>
Keep in mind that this is an incredibly inefficient approach, but your
current database design prevents you from doing this efficiently.
2. The best solution, of course, would be for you to normalize your
database. You wouldn't face problems like this with normalized data. Instead
of using a list of names in a field in Table1, build a linking table, which
would have two fields: ContactID and GroupID. You'd use this linking table
to build a many-to-many relationship between contacts and groups. I can't
overstate the importance of a well-designed data schema.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
