case sensitive help

[Justafriend]

ok heres my little problem right now  i  have databases and php and coding  the coding is set to get data from the database  from multiple lines now what my problem is is  that UBG_mymasson and ubg_mymasson are recofnizing as 2 different  nicks so  the total points are  not added for the player  but instead have 2 different entries in points list what id like help with is how to get the code to pull the first time the name is listed and then add all the points together amd so the names will only show once if they are the same name but different case letters here is my code

 

<style type="text/css">
<!--
Table {border-collapse:collapse; border-color:#000000; border-width:2px; cellpadding:3px;}
Table THEAD TD{background-color:#00FFFF; color:#FF0000; font-weight:bold; font-size:larger;; padding:3px; border:1px solid black}
Table TR TD {padding:3px;border: 1px solid black}
-->
</style>
<?
//print_r($_SERVER);
	$sn = $_SERVER['PHP_SELF'];
	mysql_connect(null, "havensd1_tourn", "z(:wI,VL91SL"); //Put login and password for mysql here. Replace null with mysql server address if provided by host.

	mysql_select_db("havensd1_tournaments") or die(mysql_error());//Put databasename here
	mysql_query("create table if not exists tourne_report (Host TEXT, Date_Time DATETIME, PlayersCount INT, Winner TEXT, WinnerEmail TEXT, SecondPlace TEXT, SecondEmail TEXT, ThirdPlace TEXT, ThirdEmail TEXT, ThirdPlace2 TEXT, ThirdEmail2 TEXT)");
	mysql_query("create table if not exists emails (PlayerName char(30) key not null, email TEXT)");
	//mysql_query("create table if not exists points (PlayerName char(30) key not null, Points INT, Date_Time DateTime)");
	mysql_query("create table if not exists points_at (PlayerName char(30) not null, Points INT, Date_Time DateTime)");
	mysql_query("create table if not exists IP_unique (IP char(20) key not null, Date_Time INT)");

if (isset ($_GET["act"]))
{
	if ($_GET["act"] == "distinctpoints")
	{
		?>
		<form method="GET">
		<table>
		<tr>
		<td>Since:</td><td><input type="text" name="since" value="1900-01-01" /></td>
		</tr>
		<tr>
		<td>To:</td><td><input type="text" name="till" value="3000-01-01" /></td>
		</tr>
		</table>
		<input type="hidden" name="act" value="distinctpoints" />

		<input type="submit" name="datespan" value="Show that period"/>

		</form>
		<table>
		<thead>

		<td>PlayerName</td>
		<td>Points</td>
		<td>Date</td>
		</thead>
		<?
			$q = "";
			if (isset($_GET["datespan"]))
			{
				$q = " WHERE Date_Time >= '${_GET["since"]}' AND Date_Time <= '${_GET["till"]}' ";
			}
			//$i = 0;
			//$d = date("Y-m-");
			//$d .= "01 00:00:00";
			//$d = preg_replace("/-(\d+) /", "-01 ", $d);
			//echo("Extracting data since $d<br/>");

			$r = mysql_query("select * from points_at ".$q."order by Date_Time DESC, PlayerName ASC");

			while ($v = mysql_fetch_assoc($r))
			{

				echo("<tr><td>${v["PlayerName"]}</td><td>${v["Points"]}</td><td>${v["Date_Time"]}</td></tr>\n");
			}
		?>
		</table>
		<?//<a href="<?echo ($sn);?><?//">Back</a>?>
		<?
		exit();

	}

	if ($_GET["act"] == "points")
	{
		?>
		<table>
		<thead>
		<td>Place</td>
		<td>PlayerName</td>
		<td>Points</td>
		</thead>
		<?

			$i = 0;
			$d = date("Y-m-");
			$d .= "01 00:00:00";

			$d = "2009-08-01 00:00:01"; //Uncomment and change data here. This is SINCE date //Change data here
			$till = "2009-08-31 23:59:59"; //Place actual date here. This is TILL date //Change data here
			//$d = preg_replace("/-(\d+) /", "-01 ", $d);
			echo("Extracting data since $d<br/>");

			$r = mysql_query("select PlayerName, Points from points_at where Date_Time > '$d' AND Date_Time < '$till'");
			$totals = array();
			while ($v = mysql_fetch_assoc($r))
			{
				if (!isset($totals ["${v["PlayerName"]}"])) $totals ["${v["PlayerName"]}"] = $v["Points"];
				else $totals ["${v["PlayerName"]}"] += $v["Points"];
			}
			if (count($totals) > 0)
			{
				asort($totals);
				$totals = array_reverse($totals, true);
				$newtotals = array();

				foreach ($totals as $k => $v)
				{
					$newtotals[] = array("PlayerName" => $k,"Points" => $v);
				}
				foreach ($newtotals as $v)
				{
					++$i;
					echo("<tr><td>$i</td><td>${v["PlayerName"]}</td><td>${v["Points"]}</td></tr>\n");
				}
			}


		?>
		</table>
		<?//<a href="<?echo ($sn);?><?//">Back</a>?>
		<?
		exit();

	}
	if ($_GET["act"] == "emails")
	{
		?>
		<table>
		<thead>
		<td>PlayerName</td>
		<td>EMail</td>
		</thead>
		<?

			$r = mysql_query("select * from emails order by PlayerName ASC");
			while ($v = mysql_fetch_assoc($r))
			{
				if ($v["PlayerName"] != "")
					echo("<tr><td>${v["PlayerName"]}</td><td>${v["email"]}</td></tr>\n");
			}

		?>
		</table>
		<?//<a href="<?echo ($sn);?><?//">Back</a>?>
		<?
		exit();

	}
	if ($_GET["act"] == "toc")
	{
			$d = date("Y-m-");
			$d .= "01 00:00:00";
			$d = "2009-08-01 00:00:01"; //Uncomment and change data here. This is SINCE date //Change data here
			$till = "2009-08-31 23:59:59"; //Place actual date here. This is TILL date //Change data here
			//$d = preg_replace("/-(\d+) /", "-01 ", $d);
			echo("Extracting data since $d<br/>");

		?>
		<h2>TOC:</h2>
		<table>
		<tr>
		<?
			$i = 0;
			$all = array();
			$r = mysql_query("select * from tourne_report where Date_Time > '$d' AND Date_Time < '$till' order by Date_Time DESC");
			while ($v = mysql_fetch_assoc($r))
			{

			if ($v["Winner"] != "")
				{
					$all[] = $v["Winner"];
				}
			if ($v["SecondPlace"] != "")
				{
					$all[] = $v["SecondPlace"];
				}						
			if ($v["ThirdPlace"] != "")
				{
					$all[] = $v["ThirdPlace"];
				}						
			if ($v["ThirdPlace2"] != "")
				{
					$all[] = $v["ThirdPlace2"];
				}						
			}
			sort($all,SORT_STRING);
			$all = array_unique($all);
			foreach ($all as $k)
			{
					echo("<td>$k</td>");
					$i++;
					if ($i == 3)
					{
						echo("</tr><tr>");
						$i = 0;
					}

			}

			while ($i != 3 && $i!=0)
			{
				echo("<td></td>");
				++$i;
			}


		?>
		</tr>
		</table>			
		<?//<a href="<?echo ($sn);?><?//">Back</a>?>
		<?
		exit();

	}
	if ($_GET["act"] == "months")
	{
		?>
		<table>
		<thead>
		<td>Host</td>
		<!--<td>Nick</td> -->
		<td>Date_Time</td>
		<td>Winner</td>
		<td>#of players</td>
		</thead>
		<?
			$d = date("Y-m-");
			$d .= "01 00:00:00";
			$d = "2009-08-01 00:00:01"; //Uncomment and change data here. This is SINCE date //Change data here
			$till = "2009-08-31 23:59:59"; //Place actual date here. This is TILL date //Change data here

			//$d = preg_replace("/-(\d+) /", "-01 ", $d);
			echo("Extracting data since $d<br/>");
			$r = mysql_query("select * from tourne_report where Date_Time>'$d' AND Date_Time <= '$till' order by Date_Time DESC");
			while ($v = mysql_fetch_assoc($r))
			{
				echo("<tr><td>${v["Host"]}</td><td>${v["Date_Time"]}</td><td>${v["Winner"]}</td><td>${v["PlayersCount"]}</td></tr>\n");
			}

		?>
		</table>
		<?//<a href="<?echo ($sn);?><?//">Back</a>?>
		<?
		exit();

	}

}
?>

[ignace]

http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

[Justafriend]

so id change the coding from

$r = mysql_query("select * from points_at ".$q."order by Date_Time DESC, PlayerName ASC");

 

to

 

$r = mysql_query("select * from points_at ".$q."order by Date_Time DESC, Binary PlayerName ASC");

 

thanks in advance

[Justafriend]

ok one question if i set the db charachters to binary will that work or not

[ignace]

http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

 

Woops sorry misunderstood your question.