We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Answered search query with no accents showing results

    • 42560
    • 49 Posts
    jacielluve Reply #1, 9 years ago
    Hi,

    Can someone help me how to do a search independent of accents?

    im trying to do a search query and display records depending of the search text

    i want to search the words (in pagetitle column) "cancer" or "cáncer" and get the same result. The word cáncer is located in DB pagetitle and is displaying all kind of result, but when i search for cancer(no accents), no results at all.

    This query is working well, but not for cáncer-cancer or any other variation (hepático-hepatico)

    SELECT msc.id, isfolder, uri, class_key,tmplvarid, value, msc.pagetitle 
           FROM `modx_site_content` AS msc 
		   INNER JOIN `modx_site_tmplvar_contentvalues` AS mstc 
		       ON msc.id = mstc.contentid 
		   WHERE(LOWER(pagetitle) like '".$search."%' ) 
		   AND tmplvarid IN(4,28)
		   AND isfolder=0 AND mstc.value = 'si' AND template IN(4,8,184,117,136,134,125,33,34,90,144,145,146,147) 
		   LIMIT 100"


    Thanks for your time

    This question has been answered by BobRay. See the first response.

    • discuss.answer
      • 3749
      • 24,544 Posts
      BobRay Reply #2, 9 years ago
      https://stackoverflow.com/questions/8647080/accent-insensitive-search-query-in-mysql
        Did I help you? Buy me a beer
        Get my Book: MODX:The Official Guide
        MODX info for everyone: http://bobsguides.com/modx.html
        My MODX Extras
        Bob's Guides is now hosted at A2 MODX Hosting
        • 42560
        • 49 Posts
        jacielluve Reply #3, 9 years ago
        yes, its all about collation. I did the query in new modx install(utf8_unicode_ci collation), search for accented/not accented word and found the same result.

        I think my production table is in another collation(not privileges to acces DB @:( ) and thats the problem qhen i try to do the search.
          • 3749
          • 24,544 Posts
          BobRay Reply #4, 9 years ago
          Maybe you could ask someone with access to the DB to export it to a SQL file for you. It's just a text file, so that would let you see the collations.

          There's a solution here for having mixed character sets and/or collations, but without access to the DB, you won't be able to implement it.

            Did I help you? Buy me a beer
            Get my Book: MODX:The Official Guide
            MODX info for everyone: http://bobsguides.com/modx.html
            My MODX Extras
            Bob's Guides is now hosted at A2 MODX Hosting
            • 42560
            • 49 Posts
            jacielluve Reply #5, 8 years, 11 months ago
            i think i solved it, my db collation was different in both, DB and html, so i had to use replace() function in query in order to replace accents. couldnt change DB settings, no admin XD

            something like REPLACE(pagetitle,"á","a")...

            nested REPLACE and solved, thanks for all