Mysql搜索字符串

Question

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 %'

Answer 1

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;
    }