homepage Welcome to WebmasterWorld Guest from 54.145.183.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL text search
Can I get surrounding words?
sned

10+ Year Member



 
Msg#: 12727 posted 11:43 pm on May 3, 2006 (gmt 0)

Say I have a text field, filled with a few hundred words.

On a search (SELECT * FROM tbl WHERE fld LIKE '%word%'), is it possible to display only a few lines of the result, specifically those 'surrounding' the searched word?

Something like:

... this is the text surrounding word but I don't want it to go on and on ...

I know I can do this with php string parsing, but I'm curious to see if anyone knows how to do this in the query itself.

Thanks!
-sned

 

jatar_k

WebmasterWorld Administrator jatar_k us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 12727 posted 12:25 am on May 4, 2006 (gmt 0)

if in the select you grab the whole thing then you use strpos, find out where the word is and do substr at -10 and +10 then cut out partial words

something like that should work

hakre

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 12727 posted 7:23 am on May 4, 2006 (gmt 0)

this is for php, but you can do similar with mysql, too:


INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.


SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. Beginning with MySQL 4.1.0, it is possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

MySQL Manual string functions [dev.mysql.com]

with these and other functions you should be able to implement it directly into your query and reduce the amount of data returned from your database server.

--hakre

sned

10+ Year Member



 
Msg#: 12727 posted 3:23 pm on May 4, 2006 (gmt 0)

Thanks for the suggestions, I didn't realize had all those string functions.

Thanks!
-sned

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved