Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MYSQL and Collation is sucking the floor

Posted on 2013-11-24
15
Medium Priority
?
268 Views
Last Modified: 2013-11-30
Hello once Again....

ÆØÅ. . . . Or 50% of the time spent on webdeveloping.


I TRIED ALL:
1. Defined UTF-8 at top of ALL my files.
2. Converted to UTF-8
3. Picked "utf8-general-ci" in MYSQL.

But still. This Picture is shown in one table ("latin1-general-ci"), while in another table row stastistic shows "utf8-general-ci".

Why?
Why UTF8 vs. Latin1???????
0
Comment
Question by:Mike Kristensen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 

Author Comment

by:Mike Kristensen
ID: 39673245
Oki i actually managed to make Row statistics "utf8-general-ci".

But the ÆØÅ letters is wrong.... If i upload to the table, its wrong. If i write to table directly from MYSQL, it is wrong in another way. . .

Its like hell. It will burn forever.
0
 
LVL 58

Expert Comment

by:Gary
ID: 39673259
Can you attach a sample php page.
Even if you declare UTF-8 in the page doesn't mean the page is UTF-8
0
 

Author Comment

by:Mike Kristensen
ID: 39673270
<?php
session_start();

include_once('bruger.php');

if(isset($_POST['logind'])){
	$navn = $_POST['navn'];
	$kode = $_POST['kode'];
	
	$object = new bruger();
	$object->Logind($navn, $kode);	
}


?>



<html>
<head>
<LINK href="design.css" rel="stylesheet" type="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
</head>
<body>
<div class="wrapper">
<div class="tavle">
<h1>Admin</h1>

<?php
if (isset($_SESSION['navn'])){



$con = mysql_connect("","");
if (!$con){
die("Kan ikke forbinde til: " . mysql_error());
}
mysql_select_db("",$con);


if(isset($_POST['opdater'])){
	if($_POST[nyScore] == ''){
		
	}
	else{
		$x=$_POST[score];
		$y=$_POST[nyScore];
		$z=(($x + $y) / 2);

		$UpdateQuery = "UPDATE tavlen SET navn='$_POST[navn]', alias='$_POST[alias]', score='$z' WHERE ID='$_POST[ID]'";
		mysql_query($UpdateQuery, $con);
	};
};

if(isset($_POST['slet'])){
$DeleteQuery = "DELETE FROM tavlen WHERE ID='$_POST[ID]'";
mysql_query($DeleteQuery, $con);
};

if(isset($_POST['tilføj'])){
$AddQuery = "INSERT INTO tavlen (navn, alias, score) VALUES ('$_POST[nyNavn]','$_POST[nyAlias]','40')";
mysql_query($AddQuery, $con);
};


if(isset($_POST['finale_tavle_opdater'])){
$UpdateQuery = "UPDATE finale_tavle SET finale_deltager='$_POST[f_deltager]', finale_kval_res='$_POST[f_kval_res]', finale_dag_navn='$_POST[f_dag_navn]' WHERE finale_dage='$_POST[f_dage]'";
mysql_query($UpdateQuery, $con);
};

if(isset($_POST['ryd_finaletavle'])){
$UpdateQuery = "UPDATE finale_tavle SET finale_deltager='', finale_kval_res='' WHERE finale_dage='onsdag_1' OR finale_dage= 'onsdag_2' OR finale_dage= 'torsdag_1' 
OR finale_dage= 'torsdag_2' OR finale_dage= 'fredag_1' OR finale_dage= 'fredag_2' OR finale_dage= 'lordag_1' OR finale_dage= 'lordag_2' OR finale_dage= 'sondag_1' 
OR finale_dage= 'sondag_2' OR finale_dage= 'mandag_1' OR finale_dage= 'mandag_2' OR finale_dage= 'tirsdag_1' OR finale_dage= 'tirsdag_2' OR finale_dage= 'nr_1' OR finale_dage= 'nr_2' OR finale_dage= 'nr_3'";
mysql_query($UpdateQuery, $con);
};




/*-------------------------------------------------------------------------GULDTAVLE-------------------------------------------------------------*/

$sql = "SELECT * FROM tavlen ORDER BY navn"; 
$myData = mysql_query($sql,$con) or die(mysql_error());


echo "<br><br><b>Opret ny Guldtavle deltager:</b><br><br><table border=0>
<tr>
<th>Navn</th>
<th>Alias</th>

</tr>";
echo "<form action=admin.php method=post>";
echo "<tr>";
echo "<td><input type=text name=nyNavn></td>";
echo "<td><input type=text name=nyAlias></td>";
echo "<td>" . "<input type=submit name=tilføj value=Opret" . " </td>";

echo "</form>";
echo "</table><br><br><br><br>";



echo "<table border=0>
<tr>
<th></th>
<th>Navn</th>
<th>Alias</th>
<th>Score</th>
<th>Sidste resultat</th>
</tr>";
while($record = mysql_fetch_array($myData)){
echo "<form action=admin.php method=post>";
echo "<tr>";
echo "<td>" . "<input type=hidden name=ID value=" . $record['ID'] . "> </td>";
echo "<td>" . "<input type=text name=navn value='" . $record['navn'] . "'> </td>";
echo "<td>" . "<input type=text name=alias value='" . $record['alias'] . "'> </td>";
echo "<td>" . "<input type=text name=score value=" . $record['score'] . "> </td>";
echo "<td>" . "<input type=text name=nyScore value=''> </td>";
echo "<td>" . "<input type=submit name=opdater value=Opdater" . "> </td>";
echo "<td>" . "---------------------- <input type=submit name=slet value='Slet' style='color:#c00;'" . "> </td>";
echo "</tr>";
echo "</form>";
}

echo "</table>";

/*--------------------------------------------------------------------------/GULDTAVLE---------------------------------------------------------------------------*/
echo "-------------------------------------------------------------/GULDTAVLE-------------FINALTAVLE---------------------------------------------------";
/*--------------------------------------------------------------------------FINALETAVLE---------------------------------------------------------------------------*/

$sql = "SELECT * FROM finale_tavle ORDER BY ID"; 
$myData = mysql_query($sql,$con) or die(mysql_error());

echo "<br><br><b>Finaletavle</b><br><br>

<table border=0>
<tr>
<th>Dag</th>
<th>Navn</th>
<th>Resultat</th>
</tr>";
while($record = mysql_fetch_array($myData)){
echo "<form action=admin.php method=post>";
echo "<tr>";
echo "<td>" . "<input type=text name=f_dag_navn value='" . $record['finale_dag_navn'] . "'> </td>";
echo "<td>" . "<input type=text name=f_deltager value='" . $record['finale_deltager'] . "'> </td>";
echo "<td>" . "<input type=text name=f_kval_res value='" . $record['finale_kval_res'] . "'> </td>";
echo "<td>" . "<input type=submit name=finale_tavle_opdater value=Opdater" . "> </td>";
echo "<td>" . "<input type=hidden name=f_dage value='" . $record['finale_dage'] . "'> </td>";
echo "</tr>";
echo "</form>";
}
echo "<form action=admin.php method=post>";
echo "<td>" . "<input type=submit name=ryd_finaletavle value='Ryd hele Finaletavlen' style='color:#c00;'" . "> </td>";
echo "</form>";
/*--------------------------------------------------------------------------/FINALETAVLE---------------------------------------------------------------*/











mysql_close($con);









}
else{

echo '	
	<form method="post" action="admin.php">
		Brugernavn: <input type="text" name="navn"/><br>Kodeord: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="text" name="kode"/>
		<input type="submit" name="logind" value="Logind">
	</form>';

}

?>




</div>




<div class="logud">
<a href="logud.php"><img src="logud.png" border="0" alt="logud"></a> 
</div>

<div class="sLogo">
Engineered by: <a href="http://www.stevenm.dk" target="_blank"><img src="sLogo.png" border="0" alt="Stevenm.dk"></a> 
</div>
</div>


</body>
</html>

Open in new window



What exactly do i have to do with these files that pretend to be UTF8 but isent?
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 58

Expert Comment

by:Gary
ID: 39673273
No, you need to attach the actual file- not post the code.
0
 

Author Comment

by:Mike Kristensen
ID: 39673296
File is here.
admin---Kopi.php
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39673301
Please see:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html

If you still have questions, post back and I'll be glad to update the article.  Thanks, ~Ray
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39673308
One other thing I noticed...

$DeleteQuery = "DELETE FROM tavlen WHERE ID='$_POST[ID]'";

Please see antipractice #18.  You better fix this right now!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html

Once you've got it changed to actually filter the external data, come back to this question, but not until you've made a Google search for PHP MySQL Security.
0
 

Author Comment

by:Mike Kristensen
ID: 39674835
After reading the article i tried to change some more.... I tried to make a new copy of the admin.php file that writes to MSQL database, and converted it to UTF-8.

For now, the admin.php uploads Ø as ø¸    
Or the database reads it wrong. I dont know....
But when i call the information from MYSQL, it is shown correctly.

So everything looks right, expect the database.


According to this $DeleteQuery = "DELETE FROM tavlen WHERE ID='$_POST[ID]'"; i will look into it right away. But nothing is "LIVE" yet, so np.
ttgtg.png
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39674908
The image shown here is the signature of UTF-8 data that is being displayed when the browser has been told that it's got ISO-8859-1 or CP-1252.  The underlying data is almost certainly correct UTF-8; it's just the display that is wrong.  Try reading one of those rows and displaying it in a web page that is marked with these HTML5 tags:

<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />

Open in new window

0
 

Author Comment

by:Mike Kristensen
ID: 39674996
Rows are shown correctly in a browser using:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>

So i could accept it as it is. But the database display is wrong, which is sad.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39675014
What program are you using to display this image?  If it's phpMyAdmin, I am pretty sure you can get it to report in UTF-8.

Look for the "variables" tab (Server variables and settings) and search for "character"
0
 

Author Comment

by:Mike Kristensen
ID: 39675333
Is there a way to change the charset directly in phpmyadmin?

I see the variables and it is mixed with latin1 and utf8.

variables.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 total points
ID: 39675363
I'd just be guessing at that.  I think a trip through the phpMyAdmin documentation looking for "charset" and "collation" might help you find a definitive answer.
0
 

Author Closing Comment

by:Mike Kristensen
ID: 39686918
Yes its there.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39686942
@Mikethk: Please see the grading guidelines for Expert Exchange here:
http://support.experts-exchange.com/customer/portal/articles/481419

I think we are entitled to your explanation of why you gave the worst possible grade anyone can give to a colleague at EE?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to count occurrences of each item in an array.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question