RE: [PHP] Mysql search query ignoring dots

> -----Original Message----- > From: Barbara Picci [mailto:barbara.picci@sardi.it] > Sent: Monday, January 24, 2011 4:51 AM > To: php-general@lists.php.net > Subject: [PHP] Mysql search query ignoring dots > > Hi all, > > I have to perform a mysql query in a table with millions of records. > I've full-text indexed my search field and I'm searching with MATCH > AGAINST. > But there is a problem. In this field there are company names that contain > dots, for istance I've "PO.SE. srl" and I want to find it if the user search for: > "POSE" or "PO.SE" or "P.O.S.E." etc. > I googled in the web but I don't find any solution. I don't want to add a new > field with the cleaned version of my string because I would like to solve > with the query and I prefer that the mysql table not become too big. But if I > will not find a different solution, I will use this escamotage. > I've find a post that is similar but the solution don't seem to solve my > situation. > You can see it at the url: > http://forums.mysql.com/read.php?10,395557,395584#msg-395584 > In my case replace(email, '.', '') = replace(theSearchValue, '.', > ''); is > indifferent and don't change my results. > > My query, searching "POSE", is: > > select aziende.* from aziende where 1>0 AND > (MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR > (replace(aziende.ragione_sociale, '.', '') = replace('POSE', > '.', > '')) order by aziende.ragione_sociale limit 0, 10 > > The alternative choice could be REGEXP but I've red that it make my query > slow in a table of millions of records and I don't know how to exclude dots > in the regular expression. > > Can anyone help me? > > Thanks in advance. > Barbara > > -- > ------------------------ > Barbara Picci > Micro srl > viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240 > http://www.microsrl.com > I don't see anything relevant regarding PHP. As for ' a table of millions of records,' that sounds like questions for the DBA. Regards, Tommy