Solved

MYSQL and Collation is sucking the floor

Posted on 2013-11-24
15
253 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
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…

706 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

15 Experts available now in Live!

Get 1:1 Help Now