CODEWITHSUNDEEP
phpmysqlsql
cppit
cppit

Reputation: 4564

Mysql search string

I have a search box that will search for a keyword in the field : description then return results. its done in mysql and php. the description can go from 10 to 600 characters long. I would like to return only a part of result. for example : I am searching for the keyword= John instead of displaying the whole field description as the result I want to see the sentence where the keyword is found only: ... Hello my name is john and I ... not the whole paragraph.

I can use sql or php whichever one is better. I tried substring but failed implementing it any example would be great.

SELECT  SUBSTR(description,2,100)  
FROM screenplays 
WHERE lower(description) like '% $search_word_fix %'

Upvotes: 1

Views: 412

Answers (4)

Shoogle
Shoogle

Reputation: 206

Modified answer, This can search better. In this example. I am searching for 'for example' or 'john'

        $str = 'I have a search box that will search for a keyword in the field : description then return results. its done in mysql and php. the description can go from 10 to 600 characters long. I would like to return only a part of result. for example : I am searching for the keyword= John instead of displaying the whole field description as the result I want to see the sentence where the keyword is found only: ... Hello my name is john and I ... not the whole paragraph.

            I can use sql or php whichever one is better. I tried substring but failed implementing it any example would be great.';
        $search = array();
        $search[] = "for example";
        $search[] = "john";
        echo descriptionSearch($str,$search);
        function descriptionSearch($desc,$words,$max_number_words = 30)
        {
            $positions = array();
            $i = 0;
            foreach($words as $w)
            {
                    $w = strtolower(trim($w));
                    $d = strtolower(trim($d));
                    if(strpos($desc,$w) !== false)
                    {
                        $positions[] = strpos($desc,$w);
                        $desc = str_replace($w,"<span style='font-weight: bold;'>".substr($desc,strpos($desc,$w),strlen($w))."</span>",$desc);
                    }
                $i++;
            }
            $max = 0;
            if(sizeof($positions) > 0)
            {
                foreach($positions as $j)
                {
                    if($max < 4)
                    {
                        $i = $j -16;
                        if($i < 0)
                        {
                            $i = 0;
                        }
                            $toreturn .= substr($desc,$i,80);
                        $toreturn .=  " ...";
                        $max++;
                    }
                }
            }
            else
            {
            $descs = explode(" ",$desc);
                for($i=0; $i < $max_number_words; $i++)
                {
                    $toreturn .=  $descs[$i]." ";
                }
            }
            return $toreturn;
        }

Upvotes: 1

Somnath Muluk
Somnath Muluk

Reputation: 57846

I thought by query, it will hamper performance. Because of string calculations. So I have done this by php substr function logic. 

SELECT  description 
        FROM screenplays 
        WHERE lower(description) like '% $search_word_fix %'

//$description will have longer string.
//$short_string will have shorter string.

    $short_string=get_less_string($description,'john',20);

    function get_less_string($string,$key,$num){
             $desc=explode($key,$string);
             $left='...'.substr($desc[0], strlen($desc[0])-$num,$num);
             $right=substr($desc[1],0,$num).'...';
             $final_string=$left.$key.$right;
             return $final_string;
        }

Upvotes: 2

Brian Hoover
Brian Hoover

Reputation: 7991

You CAN do it either way, and both will be about as efficient. PHP is probably a better more flexible solution, but just for kicks I tried to do it in MySQL, to see how it would work

SELECT SUBSTR(
          description,
          if(INSTR(description, '$search_word_fix') - 10 < 0,
             0,
             (INSTR(description, '$search_word_fix') - 10)),
          if(
             INSTR(description, '$search_word_fix') + 10 >
                length(description),
             length(description),
             NSTR(description, '$search_word_fix') + 10))
  FROM screenplays
 WHERE lower(description) LIKE '% $search_word_fix %'

Upvotes: 1

Shoogle
Shoogle

Reputation: 206

if you want to use php.. I had written a function few months back. It searches for keywords in a text and returns a string with 'bold' keywords. Hope this helps.

    $str = 'I have a search box that will search for a keyword in the field : description then return results. its done in mysql and php. the description can go from 10 to 600 characters long. I would like to return only a part of result. for example : I am searching for the keyword= John instead of displaying the whole field description as the result I want to see the sentence where the keyword is found only: ... Hello my name is john and I ... not the whole paragraph.

    I can use sql or php whichever one is better. I tried substring but failed implementing it any example would be great.';
    $search = array();
    $search[] = "example";
    echo descriptionSearch($str,$search);
    function descriptionSearch($desc,$words,$max_number_words = 30)
    {
                $descs = explode(" ",$desc);
                $positions = array();
                $i = 0;
                foreach($words as $w)
                {
                    $j = 0;
                    foreach($descs as $d)
                    {
                        $w = strtolower(trim($w));
                        $d = strtolower(trim($d));
                        if(strpos($d,$w) !== false)
                        {
                            $positions[] = $j;
                            $descs[$j] = str_replace($w,"<span style='font-weight: bold;'>".$d."</span>",$d);
                        }
                $j++;
            }
            $i++;
        }
        $max = 0;
        if(sizeof($positions) > 0)
        {
            foreach($positions as $j)
            {
                if($max < 4)
                {
                    $i = $j -5;
                    if($i < 0)
                    {
                        $i = 0;
                    }
                    while($i < ($j+10))
                    {
                        $toreturn .= $descs[$i]." ";
                        $i++;
                    }
                    $toreturn .=  " ...";
                    $max++;
                }
            }
        }
        else
        {
            for($i=0; $i < $max_number_words; $i++)
            {
                $toreturn .=  $descs[$i]." ";
            }
        }
        return $toreturn;
    }

Upvotes: 1

Related Questions