Slow query-building function

["George Pitcher" <george.pitcher@xxxxxxxxxxx>]

Hi,

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.

I've got it working using the following function:

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){
      $output="";
/* search for substring conditions */
      $tempy = str_replace(" +","|+",str_replace(" !","|!",str_replace("
*","|*",str_replace(" ^","|^",($input)))));
      $temp = explode("|",$tempy);
      $i=0;
      while($i < sizeof($temp)){
        if(substr($temp[$i],0,1)=="*"){
          $temp[$i]=" and ".$field." LIKE
'%".strim(str_replace("*","",$temp[$i]))."%'";
          $output.= $temp[$i]."|";
        }elseif(substr($temp[$i],0,1)=="!"){
          $temp[$i]=" and ".$field." NOT LIKE
'%".strim(str_replace("!","",$temp[$i]))."%'";
          $output.= $temp[$i]."|";
        }elseif(substr($temp[$i],0,1)=="+"){
          $temp[$i]=" and ".$field." LIKE
'%".strim(str_replace("+","",$temp[$i]))."%'";
          $output.= $temp[$i]."|";
        }else{
          $temp[$i]="and ".$field." LIKE '%".strim($temp[$i])."%'";
          $output.= $temp[$i]."|";
        }
        $i++;
      }
      $output = strim(substr($output,0,strlen($output)-1));
      if(substr(strtolower(strim($output)),0,3)=='and'){
        $output = str_replace("|"," ",$output);
      } else {
        $output = substr($output,4,strlen($output)-1);
      }
/* search for whole string conditions */
    } elseif(substr($input,0,1)=="*"){
      $output=$field." LIKE '%".strim(str_replace("*","",$input))."%'";
    } elseif(substr($input,0,1)=="!"){
      $output=$field." NOT LIKE '%".strim(str_replace("!","",$input))."%'";
    } elseif(substr($input,0,1)=="="){
      $output=$field."='".strim(str_replace("=","",$input))."'";
    } else {
      $input = str_replace(" ","|",$input);
      $output ="";
/* search for all substrings */
      if(substr_count($input,"|")>0){
        $temp = explode("|",$input);
        $d=0;
        while($d < sizeof($temp)){
          $temp[$d]=" and ".$field." LIKE '%".rtrim($temp[$d])."%'";
          $output.= $temp[$d]."|";
          $d++;
        }
      } else {
        $output=$field." LIKE '%".rtrim(str_replace("|", " ",$temp))."%'";
      }
    }
  } else {
    $output="";
  }
  if(substr(strtolower(strim($output)),0,3)=='and'){
    $query = str_replace("|"," ",$output);
  } else {
    $query = substr($output,4,strlen($output)-1);
  }
  $output = str_replace("|"," ",$query);
  return str_replace("and and","and",$output);
}

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

My usage example is:

$sqlp = "select supplier_id from suppliers where (supplier_id>0 ";
$sqlp.= sql_fltr($sqlp, 'imprint', $pubname);
$sqlp.=")";
echo "<br><br><br>".$sqlp."<br><br><br>";

I just add extra sql_fltr() lines for additional fields.

The main problem with this function is that takes 12 seconds on my XP
laptop, to build the query (I'm not even hitting the DB, but pasting that
into my DB front end gives a quick result.

I'm running PHP5.1RC on the laptop (PHP5.0.2 on my server) - both with
Apache 2.

Can anyone suggest a solution that will improve performance while retaining
the functionality?


George

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php