Printable View
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?
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;
}
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?
á 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.
EDIT: I forget to add the ñ to the array. You can do that yourself.PHP Code:$query = "SELECT * FROM table WHERE column = '" . noAccentString('Mas tarde iré a cenar con mi esposa!') . "'";
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.
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?
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?
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>
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...
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.
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.
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
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)).Quote:
Originally Posted by Doctor Luz
You can use regular expressions, I guess.
Just replace all underscores with the character missing.Code:SELECT *
FROM aves
WHERE LIKE 'p[áa]j[áa]ro;
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.