CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. March 13th, 2008, 02:28 PM #1
    Doctor Luz's Avatar
    Doctor Luz
    Doctor Luz is offline Senior Member
    Join Date
    Nov 2001
    Location
    Beyond Juslibol
    Posts
    1,688

    MySQL Collation issue

    I have a mysql database with one table 'data' and one field in that table 'news'. This field 'news' contains a record 'pájaro'

    The database collation is utf8_spanish_ci
    The field collation is utf8_spanish_ci

    I do a query from php.

    Why this simple query, SELECT * FROM data WHERE news='pajaro', is not working and however SELECT * FROM data WHERE news='pájaro' is working?

    The collation should force to obtain the same result with both queries.

    What's wrong with this?
    Reply With Quote Reply With Quote
  2. March 13th, 2008, 03:03 PM #2
    PeejAvery's Avatar
    PeejAvery
    PeejAvery is offline Elite Member Power Poster
    Join Date
    May 2002
    Posts
    10,943

    Re: MySQL Collation issue

    They are different characters, hence they will return different. I would suggest a string replacement. When inserting into the database, convert each letter using PHP's htmlentities() function.

    If you must, can you also replace the characters before searching.

    PHP Code:
    function noAccentString($temp) {
          $replace = array('á' => 'a', 'é' => 'e', '*' => 'i', 'ó' => 'o', 'ú' => 'u', 'ü' => 'u');

      foreach (    $replace as $char) {
            $temp = str_replace(key($replace), $char, $temp);
            next($replace);
      }

      return     $temp;
    } 
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.
    Reply With Quote Reply With Quote
  3. March 13th, 2008, 04:04 PM #3
    Doctor Luz's Avatar
    Doctor Luz
    Doctor Luz is offline Senior Member
    Join Date
    Nov 2001
    Location
    Beyond Juslibol
    Posts
    1,688

    Re: MySQL Collation issue

    Thank you.

    I can not do that. I have to keep the words in database with its special characters.

    The problem is in spanish collation 'á' is equivalente to 'a' but MySQL is ignoring it. Why?
    Reply With Quote Reply With Quote
  4. March 13th, 2008, 04:34 PM #4
    PeejAvery's Avatar
    PeejAvery
    PeejAvery is offline Elite Member Power Poster
    Join Date
    May 2002
    Posts
    10,943

    Re: MySQL Collation issue

    á is not the same as a. It is a completely different character. That is why it will not match. But, you can use the function I already gave you in my previous post.

    PHP Code:
    $query = "SELECT * FROM table WHERE column = '" . noAccentString('Mas tarde iré a cenar con mi esposa!') . "'"; 
    EDIT: I forget to add the ñ to the array. You can do that yourself.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.
    Reply With Quote Reply With Quote
  6. March 13th, 2008, 05:01 PM #5
    Doctor Luz's Avatar
    Doctor Luz
    Doctor Luz is offline Senior Member
    Join Date
    Nov 2001
    Location
    Beyond Juslibol
    Posts
    1,688

    Re: MySQL Collation issue

    do you mean that collation is only useful for sorting and not works for comparisons?

    If that's true the only solution I see is to duplicate the entries in DB since I must have the correct (gramatically) text.
    Reply With Quote Reply With Quote
  7. March 13th, 2008, 05:08 PM #6
    Homogenn
    Homogenn is offline Member
    Join Date
    Dec 2006
    Posts
    203

    Re: MySQL Collation issue

    Running MySQL 5.1.11 pajaro and pájaro return "pájaro". This is with both latin1_spanish_ci and latin1_danish_ci collation. As a result I can't have both pajaro and pájaro in my database, since it would duplice a row.

    Are you running an older version which might be the issue?
    Sincerely,

    Martin Svendsen
    Reply With Quote Reply With Quote
  8. March 13th, 2008, 05:23 PM #7
    Doctor Luz's Avatar
    Doctor Luz
    Doctor Luz is offline Senior Member
    Join Date
    Nov 2001
    Location
    Beyond Juslibol
    Posts
    1,688

    Re: MySQL Collation issue

    I have 5.0.54

    I attacha an image of the simplest query I've done: SELECT 'pajaro'='pájaro'

    Doing the query using phpMyAdmin 2.9.0.2 returns TRUE. (collation working)

    But when doing with php returns FALSE. (collation not working)

    Where's the problem? where's the collation lost?
    Attached Images Attached Images
    Reply With Quote Reply With Quote
  9. March 13th, 2008, 09:10 PM #8
    PeejAvery's Avatar
    PeejAvery
    PeejAvery is offline Elite Member Power Poster
    Join Date
    May 2002
    Posts
    10,943

    Re: MySQL Collation issue

    Okay. I am clear now.

    So I did some testing. My first thoughts were the file type and I think I am correct. Even saving the file in UTF-8 does not truly save the accent marks. For example, try the following. See if the á shows correctly in the textbox even before submitting. When the Spanish characters are not statically typed into a file, they return correctly from the query.

    PHP Code:
    <?php
    if (isset($_POST['query'])) {
          $connection = mysql_connect('localhost', 'root', 'root');
          $db = mysql_select_db('test');

          $query = mysql_query($_POST['query']);
          $row = mysql_fetch_object($query);
      echo     'Query #1: ' . $row->nombre . '<br /><br />';

          $query = mysql_query("SELECT * FROM aves WHERE nombre = 'pájaro'");
          $row = mysql_fetch_object($query);
      echo     'Query #2: ' . $row->nombre . '<br /><br />';

          mysql_close($connection);
    }
    ?>
    <html>
    <body>

    <form action="" method="post">
    <input type="text" name="query" value="SELECT * FROM aves WHERE nombre = 'pájaro'" size="80" />
    </form>

    </body>
    </html>
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.
    Reply With Quote Reply With Quote
  11. March 14th, 2008, 04:41 AM #9
    Doctor Luz's Avatar
    Doctor Luz
    Doctor Luz is offline Senior Member
    Join Date
    Nov 2001
    Location
    Beyond Juslibol
    Posts
    1,688

    Re: MySQL Collation issue

    Tried you suggestion.

    The á character is seen on the form.

    The query for 'pájaro' is successful and gives the same result with both $_POST and hardcoded text.

    The query for 'pajaro' doesn't gives any result for both methods.

    The server is running Apache and I told the owners to add an AddDefaultCharset UTF-8 to the .htaccess file. This way the server is sending the information as UTF-8. The file is also saved as UTF-8 in the server side. I don't think file type is a problem.

    I see that phpMyAdmin shows "MySQL connection collation utf8_general_ci" I'm thinking that connection collation might be the problem. Has connection collation higher priority than database and field collations?. I thought field collation had the highest priority. I don't understand...
    Reply With Quote Reply With Quote
  12. March 14th, 2008, 07:51 AM #10
    PeejAvery's Avatar
    PeejAvery
    PeejAvery is offline Elite Member Power Poster
    Join Date
    May 2002
    Posts
    10,943

    Re: MySQL Collation issue

    The problem is the file type, which is what I began to expect last night. I opened up a generic text editor and saved it with Western (Windows Latin 1) encoding. Both queries returned perfectly.
    Attached Files Attached Files
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.
    Reply With Quote Reply With Quote
  13. March 14th, 2008, 08:03 AM #11
    Doctor Luz's Avatar
    Doctor Luz
    Doctor Luz is offline Senior Member
    Join Date
    Nov 2001
    Location
    Beyond Juslibol
    Posts
    1,688

    Re: MySQL Collation issue

    For me too, (saving them as utf-8 in my case)

    The problem is doing the same with 'pajaro' instead of 'pájaro' I don't obtain any result.
    Reply With Quote Reply With Quote
  14. March 14th, 2008, 09:12 AM #12
    PeejAvery's Avatar
    PeejAvery
    PeejAvery is offline Elite Member Power Poster
    Join Date
    May 2002
    Posts
    10,943

    Re: MySQL Collation issue

    I'm confused. Are you still having the problem? I'm not. If I search for pájaro OR pajaro...they both return pájaro.

    Connection: utf8_unicode_ci
    Database: utf8_spanish_ci
    Table: utf8_spanish_ci
    Column: utf8_spanish_ci
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.
    Reply With Quote Reply With Quote
  15. March 14th, 2008, 10:49 AM #13
    Doctor Luz's Avatar
    Doctor Luz
    Doctor Luz is offline Senior Member
    Join Date
    Nov 2001
    Location
    Beyond Juslibol
    Posts
    1,688

    Re: MySQL Collation issue

    Yes, still having problems.

    The problem must be on the data itself.
    Reply With Quote Reply With Quote
  16. March 14th, 2008, 12:20 PM #14
    Homogenn
    Homogenn is offline Member
    Join Date
    Dec 2006
    Posts
    203

    Re: MySQL Collation issue

    Quote Originally Posted by Doctor Luz
    Yes, still having problems.

    The problem must be on the data itself.
    First of all, I'd like to appologize for missing a key (I broke my keyboard half an hour ago, and won't be able to fix it for another week (I'm sure you can see what key is missing)).

    You can use regular expressions, I guess.

    Code:
    SELECT *
FROM aves
WHERE LIKE 'p[áa]j[áa]ro;
    Just replace all underscores with the character missing.
    Last edited by PeejAvery; March 14th, 2008 at 12:29 PM. Reason: Replaced all the _
    Sincerely,

    Martin Svendsen
    Reply With Quote Reply With Quote
  17. March 14th, 2008, 01:42 PM #15
    Homogenn
    Homogenn is offline Member
    Join Date
    Dec 2006
    Posts
    203

    Re: MySQL Collation issue

    Thanks PeejAvery.
    I feel sort of stupid now, though. I just realized, I could simply copy a r from somewhere and use find and replace in notepad, to replace the underscores myself.
    Sincerely,

    Martin Svendsen
    Reply With Quote Reply With Quote
Page 1 of 2 12 LastLast
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules




Click Here to Expand Forum to Full Width

Featured