[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

MYSQL and Collation is sucking the floor

Posted on 2013-11-24
15
Medium Priority
?
273 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
  • 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to count occurrences of each item in an array.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Suggested Courses

611 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