Re: Slow query-building function

On 5/25/06, George Pitcher <george.pitcher@ingenta.com> wrote:

Last year I switched from using FileMaker Pro to MySQL. One of FileMaker's quirks was that if, in a text field, you searched for say, 'free ass boo', it would find any records with those three substrings in that field (in this example, its 'Free Association Books'). I want to have this quirkiness on my site, along with the option of adding + or ! before substrings.

Have you considered using MySQL's full text or boolean search features?

I've got it working using the following function:

It looks messy, and I didn't try to find out how exactly it works, but I'll suggest some general improvements that will speed it up. If the speed improvements are not good enough, you should rethink how the function works, maybe come up with a different method using preg_replace and regular expressions.

function sql_fltr($sql,$field,$input){ $input = addslashes($input); if(strlen($input)>0){

    if(substr_count($input,"*")>0 || substr_count($input,"!")>0 ||

substr_count($input,"^")>0 || substr_count($input,"+")>0){

Don't use substr_count just to check if a character is in a string. Use php.net/strpos.

      $output="";

/* search for substring conditions */

      $tempy = str_replace(" +","|+",str_replace(" !","|!",str_replace("

*","|*",str_replace(" ^","|^",($input)))));

Don't chain str_replace's. str_replace can also accept arrays as parameters so you can do all that with just one call to str_replace. php.net/str_replace.

      $temp = explode("|",$tempy);
      $i=0;
      while($i < sizeof($temp)){

Don't have the sizeof in the loop. This will count the array in every iteration which will cause a performance hit. You want something like: $sz = sizeof($temp); while ($i < $sz) {

        if(substr($temp[$i],0,1)=="*"){
          $temp[$i]=" and ".$field." LIKE

'%".strim(str_replace("*","",$temp[$i]))."%'";

Since you're just removing the initial character, you don't need to use str_replace for it. Use substr($thestring, 1); instead. php.net/substr. Same thing for the other cases below.

[snipped other cases]

        }
        $i++;
      }
      $output = strim(substr($output,0,strlen($output)-1));

You don't need to do strlen and then subtract one to remove the last character. Just put a -1 for the length argument to substr and it will do the same thing. php.net/substr.

      if(substr(strtolower(strim($output)),0,3)=='and'){

Didn't you create the "and"? If so, why do you need to strtolower() it?

[stripped rest of code]

For the rest of the code, the same changes as above apply.

For info the strim() function combines ltrim() and rtrim().

Why? PHP has its own function to do that. php.net/trim. -- Rabin Vincent http://rab.in/