SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Working on my first 'proper' mysql search routine, and thanks to your kind pointers....

    I have a table of keywords that I search to return id numbers for pertinent records.

    At the moment , I fetch the record and use PHP to find the search word in the field and display just a portion of it, using strpos() to find the keyword , and substr() to bring back a portion of text including that keyword. It works.

    I know I can return a substring of a text field in MySQL using substring.. any ideas on how I can fetch this portion but starting say 30 characters before a given keyword (which I know exists), in that field.

    So knowing that "keyword" exists in a field, I want to return
    "blah blah blah blah blah keyword blah blah blah blah blah etc"

    any ideas?

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I must not understand you correctly because you list the functions that can be used to do what I think you want. Why cant you do this:

    $start = strpos($string, "keyword");

    and then:

    $newstring=substr($string, $start - 30, 60);

    to get 30 characters on either side of $start?

  3. #3
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes - its much better do do the substring operation in the SQL because it means the DB has to send less information that way.

    Code:
    $term = strtolower($term);
    
    
    
    $result_search = mysql_query("SELECT Fields, LCASE(SUBSTRING(Search_Field, LOCATE('$term', LCASE(Search Field)) - 50, 100)) as synopsis FROM Tables WHERE Field like 'criteria' and other_Field = 'some other criteria' and LCASE(Search_Field) like '%$term%'", $db);
    if (!$result_search) {  echo("<P>Error performing query: " . mysql_error() . "</P>");  
    exit();
    }
    
    $numResults = mysql_num_rows($result_search); 
    
    if($numResults == 0){
    
    print("We're sorry. Your search returned no results.");
    
    
    }
    else{
    print("Your search term was found in the following:<p>");
    
    while ( $main = mysql_fetch_array($result_search) ) 
    
    {  
    ..
    ..
    ..
    The above will return a area of text on either side of the search term by 50 characters.

    You can also do this afterwords:

    Code:
    print(strip_tags(str_replace($term, "<b>$term</b>", $main["synopsis"]), "<b>"));
    Which will print out the synopsis but first stripping all tags except bold (if you have html in your text like P or BR tags you should do this to help result formatting) and it adds bold tags around the search term.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  4. #4
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi 7stud, thats what I am doing already, but as Aspen mentions it is much more efficient to use MySQL to do this stuff, its just so much faster. I knew could be done but was getting a bit stuck on how to go about it, I could get the substring bit happening but had not a clue how to select a substring containing a specific word....

    until now!

    Aspen - thats very very cool ! thankyou.

  5. #5
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Hey, I was very careful to preface my answer with the
    disclaimer: "I must not understand you correctly", though
    upon re-reading your question, it now seems very clear what you wanted.
    That is one heck of a select statement!


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
  •