On Tue, Jul 15, 2008 at 2:07 PM, Yeti <[EMAIL PROTECTED]> 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 be biased against using the database to format output for presentation, so I'd rather not push the task off there. Still, I know lots of developers do not share this bias, so I'll address a couple other issues I see with this approach: 1) If the search word appears multiple times, LOCATE() will only find it once. I'd probably use REPLACE() instead. This leads to the next problem: 2) I'm not sure if the OP wants this or not, but if he wants to highlight each of multiple search terms the way many sites do, he would have to split the terms and build a SQL phrase that like this (there are probably other approaches available in MySQL to do the same thing): -- search phrase 'quaint french cafe' SELECT REPLACE(REPLACE(REPLACE(`my_column`, 'quaint', '<span class="keysearch">quaint</span>'), 'french', '<span class="keysearch">french</span>'), 'cafe', '<span class="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 particular style. (Not to mention the size and complexity of the query being passed from PHP to the database or the potential size of the result being passed from the database to PHP since it now could have lots of formatting text embedded in it.) Andrew
