SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    google type search

    hi,

    when i search the database for a word i want the output to display a set amount of text that surrounds the matched word.

    e.g

    ...im searching for a keyword because im lost...
    http://redgoals.com ... my site ... nuff said

  2. #2
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok i managed to get this far with my attempt..

    PHP Code:
    $title=$r["title"];
    $article=$r["article"];
    $match preg_replace("/($keyword)/i"'<font color="#ff0000"><B>\1</B></font>'$article);
    $pos strpos($match"$keyword");
    $result substr("$match"$pos-100100);
    echo 
    "$result | $title | $pos<br>"
    example of what it returns

    blah blah blah keyword blah blah blah

    or sometimes if there is a keyword with about 10 characters left in substr it prints out a portion of the opening font tag meaning the proceding text gets coloured red .
    Last edited by redgoals; May 3, 2002 at 15:30.
    http://redgoals.com ... my site ... nuff said

  3. #3
    gingham dress, army boots... silver trophy redux's Avatar
    Join Date
    Apr 2002
    Location
    Salford / Manchester / UK
    Posts
    4,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    red, just an idea without looking too hard at your code...why not do the preg_replace AFTER you isolated the $result ?

    PHP Code:
    $title=$r["title"];
    $article=$r["article"];
    $pos strpos($match"$article");
    $result substr("$match"$pos-100100);
    $result preg_replace("/($keyword)/i"'<font color="#ff0000"><B>1</B></font>'$result);
    echo 
    "$result | $title | $pos<br>"
    re·dux (adj.): brought back; returned. used postpositively
    [latin : re-, re- + dux, leader; see duke.]
    WaSP Accessibility Task Force Member
    splintered.co.uk | photographia.co.uk | redux.deviantart.com

  4. #4
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thnx that worked .
    http://redgoals.com ... my site ... nuff said

  5. #5
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    another thing how could i make sure the first letter returned is a whole word.

    ..ello

    to

    ...hello
    http://redgoals.com ... my site ... nuff said

  6. #6
    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)
    You should do this in your SQL, that way your query doesn't have to return the entire field.

    PHP Code:
    $term strtolower($term);
    $search mysql_query("SELECT LCASE(SUBSTRING(field, LOCATE('$term', LCASE(Chp_Text)) - 50, 100)) as synopsis FROM table WHERE field like '%$term%'"$db);
    if (!
    $search) {  echo("<P>Error performing query: " mysql_error() . "</P>");  
    exit();

    That will return 50 characters to the left and the right of the term. Notice I put everything in lowercase - if you want a case sensitive search you'd remove that part.

    Then I do this:

    PHP Code:
            $numResults mysql_num_rows($search); 
        if(
    $numResults != 0){
            while ( 
    $main mysql_fetch_array($search) ) {  
                    
    print(
    strip_tags(str_replace($term"<b>$term</b>"$main["synopsis"])

                }

    Never do a regular expression when a str_replace() would work, regular expression functions should only be used when you need to actually use regular expressions, not a simple find and replace. The regular expression functions require more processing.

    I also striptags from the result. My text field contains HTML like <br> tags. If one of those <br> tags is within the 100 characters it will make the results look odd on the search page.
    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

  7. #7
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanx for the reply aspen,

    i couldnt get the keyword highlighted in the search results. Here is where im at...

    PHP Code:
    <?
    if($search)
    {
    $keyword strtolower($keyword);
    $search mysql_query("SELECT title, LCASE(SUBSTRING(article, LOCATE('$keyword', LCASE(article)) - 50, 100)) as synopsis FROM news WHERE article like '%$keyword%'");
    if (!
    $search) {
    echo(
    "<P>Error performing query: " mysql_error() . "</P>");
    exit();
    }
    $numResults mysql_num_rows($search);
    if(
    $numResults != 0){
    while ( 
    $main mysql_fetch_array($search) ) {
    print(
    strip_tags(str_replace($keyword"<b>$keyword</b>"$main["synopsis"])));
    }
    }
    }
    ?>
    <FORM NAME="form1" METHOD="post" ACTION="<? PHP_SELF ?>">
    <INPUT TYPE="text" NAME="keyword">
    <INPUT TYPE="submit" NAME="search" VALUE="Submit">
    </FORM>
    http://redgoals.com ... my site ... nuff said

  8. #8
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    fixed that problem, now how do i prevent the output from printing rows which do not contain the keyword?
    http://redgoals.com ... my site ... nuff said

  9. #9
    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)
    put the where clause in your SQL like I have above.

    You can also put other things in that query, I stripped it down to just the important parts.
    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

  10. #10
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i did put the where clause in the query, problem is when there is no keyword found in a row it prints out a blank line.
    http://redgoals.com ... my site ... nuff said

  11. #11
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorted.
    http://redgoals.com ... my site ... nuff said

  12. #12
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    one other thing can i use locate to locate the second keyword matched, if so how?
    http://redgoals.com ... my site ... nuff said

  13. #13
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *bump*
    http://redgoals.com ... my site ... nuff said

  14. #14
    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)
    The example I gave was for phrase searches. For boolean searches you need to use a fulltext index.

    http://www.mysql.com/doc/F/u/Fulltext_Search.html

    It gets complicated but it is exactly what you need if you want a boolean search.
    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

  15. #15
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why do i get the error...

    You have an error in your SQL syntax near 'BOOLEAN MODE)' at line 1

    on...

    PHP Code:
    $search mysql_query("SELECT * FROM news WHERE MATCH (article,title) 
    AGAINST ('
    $keyword' IN BOOLEAN MODE)") or die (mysql_error()); 
    http://redgoals.com ... my site ... nuff said

  16. #16
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i believe IN BOOLEAN MODE only works in MySQL 4.0+.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  17. #17
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *sigh*

    ok, with the folowing bit of code im looking to match a keyword in a string.

    PHP Code:
    $search mysql_query("SELECT id,title, 

    LCASE(SUBSTRING(article, LOCATE('
    $keyword', LCASE(article)) 0, 150)) as first, 

    SUBSTRING(article, +150, 150) as short, 

    LOCATE('
    $keyword', LCASE(short)) as second

    FROM news WHERE article like '%
    $keyword%'") or die (mysql_error()); 
    however i get the error "Unknown column 'short' in 'field list'"

    the "short" field is used to select the next 150 words from the column article. I do that so i can match the keyword twice in the article and then output the result like so...

    ...first part of the article with the keyword...second part with the keyword in....

    thats how google displays results.

    any idea how i could get output as i want it?
    Last edited by redgoals; Jun 1, 2002 at 08:51.
    http://redgoals.com ... my site ... nuff said

  18. #18
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about this?
    PHP Code:
    $search mysql_query("SELECT id,title, 



    LCASE(SUBSTRING(article, LOCATE('
    $keyword', LCASE(article)) 0, 150)) as first,
    LOCATE('
    $keyword', LCASE(SUBSTRING(article, +150, 150))) as second



    FROM news WHERE article like '%
    $keyword%'") or die (mysql_error()); 

  19. #19
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm having one of those days , why i didnt see that obvious solution is completely beyond me *sigh*. Oh well...thanx for that qslack
    http://redgoals.com ... my site ... nuff said

  20. #20
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, i think i spoke too soon.

    The query as it is now does locate the fist occurrance of the keyword in the string.

    However the second locate function does not locate the second occurance of the keyword in the next 150 words of the string.

    Ideally i wanted the second locate function to search through the second 150 words but trim off any words before the position of the keyword.

    Did that make sense?
    http://redgoals.com ... my site ... nuff said

  21. #21
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi all,

    does anyone know how to count the number of occurences of a word in a record?, using a query?
    http://redgoals.com ... my site ... nuff said

  22. #22
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think this is what you want but the following should give you the number of times a word occurs in a column.

    SELECT articlecontent FROM table WHERE articlecontent LIKE '%searchterm%'

    Then for each record do:
    print substr_count($articlecontent, "searchterm");

  23. #23
    SitePoint Zealot mrWoot's Avatar
    Join Date
    Jan 2004
    Location
    Wisconsin
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am trying to do the same thing. I've been working at this for some time. I was wondering if you got it to work. If you have, could you post it here?


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
  •