Skip to main content
WolfShade
WolfShade
Legend
April 14, 2011
Question

Bind, but only if input value length > 3

  • April 14, 2011
  • 1 reply
  • 468 views

Hello, everyone.

I'm working on a page that has a form; when someone starts typing something into a text input, this will bind to a CFDIV that contains a page that will send a query to the database, searching for records where columnA is like '%' + @string +'%'.

The problem is that the database contains almost one million records, and if the input value = 'b', it takes 24+ seconds to return over 700,000 records.  But it will take about half a second to return the 3000+ records that match 'bar'.

Is there a way to set it so that the bind does not occurr UNLESS the input value is at least three characters long?

Or is there a better way to do this?

Thanks,

^_^

    This topic has been closed for replies.

    1 reply

    ilssac
    ilssac
    Inspiring
    April 14, 2011

    I don't know about NOT doing the bind.

    But I can imagine a pretty easy way to NOT return records in the query until there are three characters.

    <cfquery ....>
      SELECT aField, bField, cField
      FROM aTable
      WHERE 
         1 = 0 -- return no records
         <cfif len(bindParameter) GT 2>
          OR -- return records when bindParameter 3 or more characters
          aField LIKE '%' + @bindParameter + '%'
         </cfif>
    </cfquery>

    This will return empty record sets, until such a time that the minum character lengh is reached.

    WolfShade
    WolfShadeAuthor
    Legend
    April 14, 2011

    I was kind of considering that as an alternative, if I can't find a way to prevent the bind.  I just didn't like the idea of the CFDIV loading with zero data to populate it.  Just didn't think it would look professional.

    But if I go that route, then the conditional would have to be length >=3 or length = 0 (they are supposed to be able to do a blank search, too, for EVERYTHING.)

    Thanks,

    ^_^

    A
    Anonymous
    April 14, 2011

    You can write some external Javascript to handle visibility of the CFDIV (e.g. if the div is empty, cause it to be hidden).

    In jQuery, for example:

    $('#cfdiv').change(function () {

       if($('#cfdiv').html() == '') {

         $('#cfdiv').hide();

       } else {

         $('#cfdiv').show();

       }

    });

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices