Solved

MYSQL and Collation is sucking the floor

Posted on 2013-11-24
15
255 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
 
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 108

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 108

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 108

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 108

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 108

Accepted Solution

by:
Ray Paseur earned 500 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 108

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

896 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now