CODEWITHSUNDEEP
mysqlcoldfusionlucee
HPWD
HPWD

Reputation: 2240

Populating Autocomplete list with new tags

I'm using Lucee 5.x and Maria DB (MySQL).

I have a user supplied comma delimited list. I need to query the database and if the item isn't in the database, I need to add it.

user supplied list
green blue purple white

database items
black white red blue pink orange lime

It is not expected that the database list would grow to more than 30 items but end-users always find 'creative' ways to use the tools we provide them.

So using the user supplied list above, only green and purple should be added to the database.

Do I compare the user supplied list against the database items or vice versa? Would the process change if the user supplied list count exceeds what is in the database (meaning if the user submits 10 items and the database only contains 5 items)? I'm not sure which loop is the better way to determine which items are new. Needs to be in cfscript and I'm looking at the looping options as outlined here (https://www.petefreitag.com/cheatsheets/coldfusion/cfscript/)

FOR Loop
FOR IN Loop (Array)
FOR IN Loop (Query)

I tried MySQL of NOT IN but that left me with the existing database values in addition to the new ones. I know this should be simple and I'm over complicating this somewhere and/or am too close to the problem to see the solution.

Upvotes: 2

Views: 135

Answers (2)

Shawn
Shawn

Reputation: 4786

Again, since this is an operation that the database can do, I'd feed the input data to the database and then let it decide how to deal with multiple keys. I don't recommend using CF to loop through your values to check them and then doing the INSERT. This will require multiple trips to the database and then processing on the application server that isn't really needed.

My suggestion is to use MariaDB's INSERT....ON DUPLICATE KEY UPDATE... syntax. This will also require that whatever field you are trying to insert on actually has a UNIQUE constraint on it. Without that constraint, then your database itself doesn't care if you have duplicate data, when can cause its own set of issues.

For the database, we have 

CREATE TABLE t1 (mycolor varchar(50)
  , CONSTRAINT constraint_mycolor UNIQUE (mycolor)
) ; 

INSERT INTO t1(mycolor)
VALUES ('black'),('white'),('red'),('blue'),('pink'),('orange'),('lime') 
;

The ColdFusion is:

<cfscript>

myInputValues = "green,blue,purple,white" ;

myQueryValues = "" ;

function sanitizeValue ( String inVal required ) {
    // do sanitization stuff here
    var sanitizedInVal = arguments.inVal ;
    return sanitizedInVal ;
}

myQueryValues = myInputValues.listMap( 
    function(i) {
        return "('" & sanitizeValue(i) & "')" ;
    } 
) ;

// This will take parameterization out of the cfquery tag and
    preform sanitization and validation before building the 
    query string.

myQuery = new query();
myQuery.name = "myQuery";
myQuery.setDataSource("dsn");

sqlString = "INSERT INTO t1(mycolor) VALUES " 
    & myQueryValues 
    & " ON DUPLICATE KEY UPDATE mycolor=mycolor;" 
;

myQuery.setSQL(sqlString);
myQueryResult = myQuery.execute().getResult();

</cfscript>

First, build up your input values (myInputValues). You'll want to do validation and sanitization on them to prevent nastiness from entering your database. I created a sanitizeValue function to be the placeholder for the sanitization and validation operations.

myQueryValues will become a string list of the values in the proper format that we will use to insert into the database.

Then we just build up a new query(), using myQueryValues in the sqlString to get our query. Again, since we are building a string for multiple values to INSERT, I don't think there's a way to user queryparam for those VALUES. But since we cleaned up our string earlier, it should do much of what cfqueryparam does anyway.

We use MariaDB's INSERT INTO .... ON DUPLICATE KEY UPDATE ... syntax to only insert unique values. Again, this requires that the database itself has a constraint to prevent duplicates in whatever column we're inserting.

For a demo: https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=4308da3addb9135e49eeee451c6e9e58

This should do what you're looking to do without beating up on your database too much. I don't have a Lucee or MariaDB server set up to test, so you'll have to give it a shot and see how it performs. I don't know how big your database is or will become, but this should still query pretty quickly.

Upvotes: 0

Alex Baban
Alex Baban

Reputation: 11732

You could do this:

  • get a list with existing items from database
  • append user supplied list
  • remove duplicates
  • update db if items were added
<cfscript>

    var userItems = '"green","blue","purple","white"';
    var dbItems = '"black","white","red","blue","pink","orange","lime"';
    var result = ListRemoveDuplicates( ListAppend(dbItems, userItems));

    if (ListLen(result) neq ListLen(dbItems)) {
      // update db
    }

</cfscript>

Update (only new items)

<cfscript>

    var userItems = '"green","blue","purple","white"';
    var dbItems = '"black","white","red","blue","pink","orange","lime"';
    var newItems = '';

    ListEach(userItems, function (item) {
        if (not ListFind(dbItems, item)) {
          newItems = ListAppend(newItems, item);
        }
    })

</cfscript>

trycf.com gist:
(https://trycf.com/gist/f6a44821165338b3c10b7808606979e6/lucee5?theme=monokai)

Upvotes: 1

Related Questions