SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Google style search output

    I'm looking for a way to get a search output that shows the 100 characters before and 100 characters after the search word in the output, just like google does. Now I found this and it works great, but only when the searchword does not appear more than once. If the searchword does appear more than once it still works but the outputtext (in this case $outputtext) is empty. Any idea ? Here's the query

    PHP Code:
    SELECT LCASESUBSTRINGpost_txt_htmlcleanLOCATE'$search'LCASEpost_txt_htmlclean ) ) -150230 ) ) AS outputtext 
    FROM blog WHERE post_txt_htmlclean 
    LIKE 
    '%$search%' 

  2. #2
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Be aware that SUBSTRING works like strpos in that if you provide a negative number it "wraps around" to the end of the string.

    Thus, if your LOCATE(search, post) - 150 expression happens to be negative, you will get a snippet from the end of the post -- it doesn't just start from 0.

    Considering the amount of work you will likely want to do -- inserting HTML to colorize the search keywords, etc.--you may want to handle this in PHP, not SQL.

    =Austin
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  3. #3
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Austin,

    so I'd need to use something like ..

    PHP Code:
    $pos strpos($outputtext$search);
    $left $pos-100;
    $right $pos+100;
                                    
    $result substr($outputtext$left$right); 
    This works .. sometimes .. but not always, and I can't even see any logic behind it when it works and when not . Any idea ?

  4. #4
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Php behaves the same way -- if the $pos - 100 is less than zero, it goes to the end.

    Thing is, it's easy to fix in php:
    PHP Code:
    $pos strpos($outputtext$search);
    $left $pos 100 $pos 100 0;
    $right $pos 100 <= strlen($outputtext) ? $pos 100 strlen($outputtext); 
    Something like that. (I haven't tested this.)

    =Austin
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  5. #5
    SitePoint Wizard bronze trophy bigalreturns's Avatar
    Join Date
    Mar 2006
    Posts
    1,295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to do a similar thing atm, and came up with the solution below. This grabs 50chars either side of the 1st occurence of the first term, and outputs this, but with the search term in bold and italicised. Took me a while to get it working properly, mainly due to problems with strings repeating when they were to short, and also because of problems adding enough characters to the string to allow for the bold and italic tags...eventually solved by reordering the string functions. Anyway, hope it can be of some use to you, now if only i didnt have exams to revise for I could get on with multiple search terms and a sorting function, but I dont have that many hours to spare!!
    Alex
    EDIT: apologies for the lack of comments, any confusing sections just ask!

    Code:
    $searchsub=$_GET[searchsub];
    $sfield=$_GET[sfield];
    $sparam=$_GET[sparam];
    $i=0;
    $j=0;
    
    IF (!empty($searchsub) AND !empty($sfield)) 
    {
    $squery = $sparam . " LIKE " . "'%$sfield%'";
    $query = "SELECT bid,title,author,descript FROM books WHERE " . $squery;
    $result=mysql_query($query);
      
      WHILE ($row=mysql_fetch_row($result)) 
      {
      $bid[$i]=$row[0];
      $title[$i]=$row[1];
      $author[$i]=$row[2];
      $descript[$i]=$row[3];
      $i++;
      }
    
      IF ($i) 
      {
      $i=$i-1;
        WHILE ($j<=$i) 
        {
        $title[$j]=str_replace($sfield,"<b><i>" . $sfield . "</b></i>",$title[$j]);
        echo ("<br><a href=\"viewlisting.php?bid=$bid[$j]\">" . $title[$j] . "-" . $author[$j] ."</a><br>");
        $pos=strpos($descript[$j],$sfield);
       
          IF ($pos-50+strlen($sfield)<0) 
          {
          $start1=0;
          }
          ELSE
          {
          $start1=$pos-50+strlen($sfield);
          }
    
          IF (strlen($descript)-50>$pos+strlen($sfield)) 
          {
          $start1=strlen($descript-100)+strlen($sfield);
          $start2=strlen($descript)-50+strlen($sfield);
          }
          ELSE 
          {
          $start2=$pos+strlen($sfield);
          }
    
        $excerpt=substr($descript[$j],$start1,50-strlen($sfield)) . $sfield . substr($descript[$j],$start2,50);
        $sfield=strtolower($sfield);
        $excerptmod=strtolower($excerpt);
    
          IF (strlen($descript[$j])<100)
          {
          $exclen=strlen($descript[$j]);
          }
          ELSE
          {
          $exclen=125;
          }
        
          echo ("..." . str_replace($sfield,"<b><i>" . $sfield . "</b></i>",substr($excerptmod,0,$exclen)) . "...<br>");
          
        $j++;
        }
      }
     
        
      ELSE 
      {
      echo ("Sorry, no listings were found matching");
      }
    
    }
    ELSE IF (empty($sfield)) 
    {
    echo ("Please enter a search term");
    }
    ELSE 
    {
    echo ("Please enter this page via the homepage");
    }
    "The proper function of man is to live - not to exist."
    Get a Free TomTom


  6. #6
    is_empty(2); foofoonet's Avatar
    Join Date
    Mar 2006
    Posts
    1,000
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems a shame we cant do this in the select statement from the database.

  7. #7
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can, it's just harder to read and understand.
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  8. #8
    is_empty(2); foofoonet's Avatar
    Join Date
    Mar 2006
    Posts
    1,000
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought I'd find how to do that in the fulltext sections of some tutorials, but I couldnt find anything. Is there a name for that type of select? Or have you seen a site dealing with that kind of sql work?

  9. #9
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so far, almost done.

    Now the last problem,

    PHP Code:
    $pos strpos($outputtext$search); 
    will find "test" in "this is a testtext", but not in "this is a test" ..

    means if the search words stands alone rather than beeing part of a word strpos doesn't find it. Any solution.

    Thanks

  10. #10
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's just string-chopping, Foo. There's nothing 'interesting' enough for anyone to write a journal article about it.
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  11. #11
    SitePoint Addict
    Join Date
    Dec 2004
    Posts
    240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by smorb
    Thanks so far, almost done.

    Now the last problem,

    PHP Code:
    $pos strpos($outputtext$search); 
    will find "test" in "this is a testtext", but not in "this is a test" ..

    means if the search words stands alone rather than beeing part of a word strpos doesn't find it. Any solution.
    Thanks
    Actually it finds it:
    PHP Code:
    echo strpos("this is a testtext""test")."<br>";
    echo 
    strpos("this is a test""test"); 

  12. #12
    SitePoint Wizard bronze trophy bigalreturns's Avatar
    Join Date
    Mar 2006
    Posts
    1,295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Slightly off topic, but I have a search using fulltext, and strangely when searching, for example,

    the quick brown fox jumped over the lazy dog

    it will find quick,brown,fox,lazy,over,dog, but not find 'the'. Not sure why this would be.

    Alex
    "The proper function of man is to live - not to exist."
    Get a Free TomTom



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •