On 5/25/06, George Pitcher <george.pitcher@xxxxxxxxxxx> 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/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
