CODEWITHSUNDEEP
loopsrandomcoldfusionwhile-loopduplicates
frequent
frequent

Reputation: 28513

how to check for duplicate values when creating a random string in Coldfusion?

I'm running Coldfusion8/MySQL 5.0.88 and have table with ids, which are random strings a-z 0-9

I want to avoid duplicate entries when creating new records in the table, but I'm not sure how to set it up correctly.

Here is what I have:

<cfset variables.listOfAppIds = "">

<!--- get all ids --->
<cfquery datasource="db" name="app_ids">
    SELECT app_id FROM apps
</cfquery>

<!--- create comma-separated list --->
<cfloop query="app_ids">
    <cfset variables.listOfAppIds = variables.listOfAppIds & "," & app_ids.app_id>
</cfloop>

<!--- create random string and test if it is in listOfAppIds --->
<cfloop condition="#ListFindNoCase(variables.listOfAppIds, variables.rndString, ',')#">
    <cfset variables.stringLength = 10>
    <cfset variables.stringList = "0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z">
    <cfset variables.rndString = "">
    <cfloop from="1" to="#variables.stringLength#" index="i">
        <cfset variables.rndNum = RandRange(1, listlen(variables.stringList))>
        <cfset variables.rndString = variables.rndString & listGetAt(variables.stringlist, variables.rndNum)>
    </cfloop>
    <cfset variables.current_appId = variables.rndString>
</cfloop>

Question:
I'm not sure I'm using the while-loop correctly. Will this make sure no duplicates are found?

Thanks for help!

Upvotes: 1

Views: 816

Answers (1)

barnyr
barnyr

Reputation: 5678

I would re-consider the approach. You could use a GUID and then not check at all (which a lot of apps do).

Or create the new random ID then query the DB to see if you have a collision. That way the database is handling the same logic for you. Your logic would then be:

collision=true;
while (collision=true) {
    newID=RandomIdFunction();
    SELECT app_id FROM apps where app_id = newID;
    if query had no rows, set collision=false;
}

That will become slower as the database fills up the the chance of collisions gets higher, that's why I'd probably use the GUID approach if you can.

Upvotes: 5

Related Questions