1

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 %'
Improve this question
3
  • 1
    Take a look at Full-Text Search Functions - dev.mysql.com/doc/refman/5.1/en/fulltext-search.html Commented Dec 16, 2011 at 4:35
  • I heard the fulltext functions isn't that great. my hosting company told me so. I ve never really used it so I am not sure why Commented Dec 17, 2011 at 4:52
  • Please make the title of your question more specific. Commented Jan 30, 2012 at 14:54

4 Answers 4

Reset to default
2

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;
        }
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

MySQL does case-insensitive searches, so you might want to change the first line in the function to compare the lower-case version of the key and the string. Otherwise, a search for "test" won't find the word "Test" in the results.
$search_word_fix should be in lower case before operations... @pbarney
That won't matter if $description isn't also in lower case, which I can't imagine anybody would want. It's just simpler to do $desc = explode(strtolower($key),strtolower($string)); In any case, it's a very handy function and I'm making good use of it. Thank you!
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;
    }
Improve this answer

4 Comments

hey Shoogle, what if I send a 2 keyword search like "hello world' ? I tried it it wouldn't highlight any of the words
Yh unfortunately this function was not written for it. I can write a function that will help, if you can wait few hours.
sure thing I've been trying to do it myself I failed so far, thank you Shoogle. $search = array(); $search[] = "keyword"; search is an array I thought it would work but I think not.
Check the new answer. Hope that helps. I might review it, but it should work fine
1

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 %'
Improve this answer

2 Comments

really great way, just want to ask one thing, the question is about performance, which is much faster this way or php way, i don't know about performance that's why i ask
@jogesh_p - I don't have any benchmarks. Your biggest performance hit is going to be WHERE lower(description) LIKE '% $search_word_fix %' That's going to be several orders of magnitude more expensive to process then anything else you're likely to do on anything more than a trivial table.
1

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;
        }
Improve this answer

2 Comments

Search array is usually an explode for the search term. for example: $search = explode(" ","Hello world");
I added a 3rd word it actually repeated john result. for 2 words it seems it works thanks Shoogle

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.