I decided to keep stuff in the 'login' table after all: trying to explain to
non-technical users that if they change their fullname that will change their login
username too seems like it's a bit too unintuitive for them, even though I think it's
a very good idea. In any case one of the things they can do in the CMS is to change
their login details if they want to.
this is the code I'm using, and it seems to work (although I have a query about
string11): I just wondered if there was an easier way of tidying up the strings?
any comments appreciated
Ian W
________________________________________________________________
// get everything from directory except for a few specified records
<cfquery datasource="#dsn#" name="info">
SELECT id, fullname
FROM directory
WHERE id <> 1
AND id <> 219
AND id <> 131
AND id <> 50
</cfquery>
// whack everyhting form login except for a few specified users
<cfquery datasource="#dsn#">
DELETE FROM login
WHERE id <> 1
AND id <> 3
AND id <> 4
AND id <> 5
AND id <> 6
</cfquery>
<cfset string1 = ' '>
<cfset string2 = ''>
<cfset string3 = '&'>
<cfset string4 = '&'>
<cfset string5 = '('>
<cfset string6 = ')'>
<cfset string7 = ':'>
<cfset string8 = '/'>
<cfset string9 = '\'>
<cfset string10 = '.'>
<cfset string11 = '''>
// this to clean up the text. haven't tried string 11 yet, but I bet that doesn't
work. do I need to escape it to search for an apostrophe? so '\'' ? or use its ASCII
code?
<cfoutput query="info">
// after ReplaceList it isn't a list anymore just a text string, so can use Replace
<cfset fullname = Lcase(fullname)>
<cfset fullname = ReplaceList(variables.fullname, string1, string2)>
<cfset fullname = Replace(variables.fullname, string3, string2)>
<cfset fullname = Replace(variables.fullname, string5, string2)>
<cfset fullname = Replace(variables.fullname, string6, string2)>
<cfset fullname = Replace(variables.fullname, string7, string2)>
<cfset fullname = Replace(variables.fullname, string8, string2)>
<cfset fullname = Replace(variables.fullname, string9, string2)>
<cfset fullname = Replace(variables.fullname, string10, string2)>
<cfset fullname = Replace(variables.fullname, string11, string2)> // see above
// this to generate the random password
<cfscript>
MyLength = 9;
MyString = '';
for (i=1; i LTE MyLength; i=i+1) {
WhichSet = RandRange(1,3);
if (WhichSet EQ 1) {
// numeric
MyString = MyString&RandRange(0,9);
} else if (WhichSet EQ 2) {
// upper case alpha
MyString = MyString&Chr(RandRange(65,90));
} else {
// lower case alpha
MyString = MyString&Chr(RandRange(97,122));
}
}
</cfscript>
// this to write each record to the db
<cfquery datasource="#dsn#">
INSERT INTO login (directoryid, username, password, type) VALUES (#id#,
'#variables.fullname#', '#MyString#', 'other')
</cfquery>
</cfoutput>
