Jump to content

searching mysql database with accented characters

Sharkadder

By Sharkadder
in PHP

Recommended Posts

Sharkadder Member

Sharkadder

Posted
Posted

Hi there,In a website i am creating i am having some problems with accented characters and comparing them with results from a MySQL database. Basically the accented word is searched but the database just says that no such entry is found. The database is for a website about football players and the problem i am having is with Turkish accented characters. A sample of what i have tried is below.teams.php (relevant code which calls squad.php)================================

print '<a href="/sql_testing/squad.php?squad='Gençlerbirliği'">Gençlerbirliği</a><br />';

ok so the above code will print what i put out and when you click on "Gençlerbirliği" it will load squad.php with the squad variable set as "Gençlerbirliği".Now on squad.php i do this:==================

<?phpheader('Content-type: text/html;charset=utf-8');?><html><head></head><body><meta http-equiv="content-type" content="text/html; charset=utf-8" /> <?php if(isset($_GET['squad'])){	$squads = explode("=",$_SERVER['REQUEST_URI']);	$squad = str_replace("%20&%20"," & ",$squads[1]);	$squad = str_replace("%20"," ",$squad);	$squad = trim(rawurldecode($squad));}else{	echo "No valid squad selected";}$con = mysql_connect('**********','*********','********');@mysql_select_db("*******",$con) or die(mysql_error());$query="SELECT * FROM FootballPlayers WHERE TeamName LIKE '%$squad%' ORDER BY CASE WHEN Positions LIKE 'GK%' THEN 1 WHEN Positions LIKE 'RB%' THEN 2 WHEN Positions LIKE 'LB%' THEN 3 WHEN Positions LIKE 'SW%' THEN 4 WHEN Positions LIKE 'CD%' THEN 5 WHEN Positions = 'D' THEN 6 WHEN Positions LIKE 'RW%' THEN 7 WHEN Positions LIKE 'LW%' THEN 8 WHEN Positions LIKE 'DM%' THEN 9 WHEN Positions LIKE 'CM%' THEN 10 WHEN Positions LIKE 'AM%' THEN 11 WHEN Positions = 'M' THEN 12 WHEN Positions LIKE 'FW%' THEN 13 WHEN Positions LIKE 'ST%' THEN 14 WHEN Positions = 'A' THEN 15 ELSE 16 END";$result=mysql_query($query) or exit(mysql_error());$num=mysql_numrows($result);mysql_close();

ok just to explain the above code, basically i am wanting to select a squad from the the table "footballplayers" where the team name is equal to the squad variable. I know i have not shown my print out code there but when i do go to print the row out where the squad equals to "Gençlerbirliği" it says no results found even though i have that team name in the database. The stuff after the order by statement is just ordering players by their position so that part is irrelevant.My question is, why will it not search the database properly with this name which has accented characters? The mysql database is using UTF-8 encoding and stores the name "Gençlerbirliği" as "Gençlerbirliği" when i look in phpmyadmin, so something must be wrong with the search variable's value. Sorry about not showing all of the php file, if you wish for it all shown then i can post the whole thing.p.s. no mysql error is generated from the die functions so i assume that it commits.Thanks,Mark

Link to comment
Share on other sites
Sharkadder Member

Sharkadder

Posted
  • Author
Posted

Hi, sorry for not being around but i've been working on some other projects in my spare time and have now came back to this issue.Ok i have tried to print out the whole query in normal php and it comes up as follows once printed:

SELECT * FROM FootballPlayers WHERE TeamName LIKE '%Gençlerbirliği%' ORDER BY CASE WHEN Positions LIKE 'GK%' THEN 1 WHEN Positions LIKE 'RB%' THEN 2 WHEN Positions LIKE 'LB%' THEN 3 WHEN Positions LIKE 'SW%' THEN 4 WHEN Positions LIKE 'CD%' THEN 5 WHEN Positions = 'D' THEN 6 WHEN Positions LIKE 'RW%' THEN 7 WHEN Positions LIKE 'LW%' THEN 8 WHEN Positions LIKE 'DM%' THEN 9 WHEN Positions LIKE 'CM%' THEN 10 WHEN Positions LIKE 'AM%' THEN 11 WHEN Positions = 'M' THEN 12 WHEN Positions LIKE 'FW%' THEN 13 WHEN Positions LIKE 'ST%' THEN 14 WHEN Positions = 'A' THEN 15 ELSE 16 END

As you can see from above, the name is as it should be, so obviously it's going through the query ok.I then print out the result:

$result=mysql_query($query) or exit(mysql_error());print $result;

The outcome "Resource id #3" is then printed, not too sure what this means but it is printed.finally i try printing out the $num variable, i.e. the number of rows

$num=mysql_numrows($result);print $num;

Nothing is printed out when i try printing out $numI then tried the following in phpmyadmin:

squad = "Gençlerbirliği";$query="SELECT * FROM FootballPlayers WHERE TeamName LIKE '%$squad%' ORDER BY CASE WHEN Positions LIKE 'GK%' THEN 1 WHEN Positions LIKE 'RB%' THEN 2 WHEN Positions LIKE 'LB%' THEN 3 WHEN Positions LIKE 'SW%' THEN 4 WHEN Positions LIKE 'CD%' THEN 5 WHEN Positions = 'D' THEN 6 WHEN Positions LIKE 'RW%' THEN 7 WHEN Positions LIKE 'LW%' THEN 8 WHEN Positions LIKE 'DM%' THEN 9 WHEN Positions LIKE 'CM%' THEN 10 WHEN Positions LIKE 'AM%' THEN 11 WHEN Positions = 'M' THEN 12 WHEN Positions LIKE 'FW%' THEN 13 WHEN Positions LIKE 'ST%' THEN 14 WHEN Positions = 'A' THEN 15 ELSE 16 END";$result=mysql_query($query) or exit(mysql_error());print $result;

it brought out this result below:

ErrorSQL query:squad = "Gençlerbirliği";MySQL said: Documentation#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'squad = "GençlerbirliÄŸi"' at line 1
Ok as you can see, the name it tries to find in phpmyadmin is different, it says syntax error, i am not sure why as i have called the same sql database in this way before using php and it works fine for names without accents such as English names. The coding on the database is utf-8 general ci as it shows in phpmyadmin.Thanks and more help is apprechiated.
Link to comment
Share on other sites
Sharkadder Member

Sharkadder

Posted
  • Author
Posted

ok i've done that and phpmyadmin finds no results, here is the results from the query:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0048 sec)SQL query:SELECT *FROM FootballPlayersWHERE TeamName LIKE 'Gençlerbirliği'ORDER BY CASE WHEN Positions LIKE 'GK%'THEN 1WHEN Positions LIKE 'RB%'THEN 2WHEN Positions LIKE 'LB%'THEN 3WHEN Positions LIKE 'SW%'THEN 4WHEN Positions LIKE 'CD%'THEN 5WHEN Positions = 'D'THEN 6WHEN Positions LIKE 'RW%'THEN 7WHEN Positions LIKE 'LW%'THEN 8WHEN Positions LIKE 'DM%'THEN 9WHEN Positions LIKE 'CM%'THEN 10WHEN Positions LIKE 'AM%'THEN 11WHEN Positions = 'M'THEN 12WHEN Positions LIKE 'FW%'THEN 13WHEN Positions LIKE 'ST%'THEN 14WHEN Positions = 'A'THEN 15ELSE 16ENDLIMIT 0 , 30

As i say, the query works if i do names without accents. The name in the query above is about the worst accented example i have. I check phpmyadmin again and some accented names are saving as follows within the database:GençlerbirliğiWith me converting them in php i don't know if it converts them right. If i search for the name with the character number instead of the accent in phpmyadmin it still shows zero results. The same goes for this code below in phpmyadmin:

SELECT *  FROM `FootballPlayers` WHERE `TeamName` LIKE 'Gençlerbirliği'

Thanks

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...