On Tue, Jul 15, 2008 at 2:07 PM, Yeti <yeti@xxxxxxxxxx> wrote:> The original problem was>> User X submits a character string A.>> A PHP scripts uses A to search for it's occurences in a DB, ignoring special> characters.>> The result of ze search is a list of character strings M-LIST with matches.>> This list gets outputted to the user X, but before that all the matching> strings should be replaced with '<span style="color: #FF0000">'..'</span>'>> If i clearly got the OP then he is using MySQL to perform the search.>> I guess he is doing it with MATCH. So MySQL already found the match and in> PHP it has to be done again ...>> eg.>> The table has 2 entries, string1 and string2 ..>> string1 = 'Thís ís an éxámplè stríng wíth áccénts.'>> string2 = 'This is an example string without accents.'>> Now the user searches for "ample":>> search = 'ample'>> Both string have matches due to accent-insensitivity (AI). Now the result is> outputted with highlighting ..>> Thís ís an éx<span style="color: #FF0000">ámplè</span> stríng wíth áccénts.>> This is an ex<span style="color: #FF0000">ample</span> string without> accents. Correct. > So since MySQL already did the job, why not get the occurances from it?>> I'm not an MySQL expert, but I know google and found something called string> functions. Especially a "locate" function got my interest.>> http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_locate>> Now shouldnt it be possible to create a query that searches the db for> matches and additionally uses the string function?>> I have no idea, but maybe some MySQL-expert out there has ...>> Yeti> There are definitely possibilities there. Personally, I tend to bebiased against using the database to format output for presentation,so I'd rather not push the task off there. Still, I know lots ofdevelopers do not share this bias, so I'll address a couple otherissues I see with this approach: 1) If the search word appears multiple times, LOCATE() will only findit once. I'd probably use REPLACE() instead. This leads to the nextproblem: 2) I'm not sure if the OP wants this or not, but if he wants tohighlight each of multiple search terms the way many sites do, hewould have to split the terms and build a SQL phrase that like this(there are probably other approaches available in MySQL to do the samething): -- search phrase 'quaint french cafe'SELECT REPLACE(REPLACE(REPLACE(`my_column`, 'quaint', '<spanclass="keysearch">quaint</span>'), 'french', '<spanclass="keysearch">french</span>'), 'cafe', '<spanclass="keysearch">cafe</span>') FROM ... In this case, he should get all instances of each word highlighted,but the accented characters would again be replaced with a particularstyle. (Not to mention the size and complexity of the query beingpassed from PHP to the database or the potential size of the resultbeing passed from the database to PHP since it now could have lots offormatting text embedded in it.) Andrew
