Problems with online scorecharts

[evilsanta]

Hi, I am currently putting up scorecharts http://nzgames.org/games/guessinggame/highscore.php and there are some problems I am having, first problem... the scores are being sorted odd, how should I be sorting them? I am getting them from a MySQL DB, heres the code...

<?php
$conf = array();
include ('conf.php');

$DBUser = $conf['DB_USER'];
$DBPass = $conf['DB_PASS'];
$DBHost = $conf['DB_HOST'];
$DBName = $conf['DB_NAME'];

Function Connect_bdd()
{
Global $DBName, $DBHost, $DBPass, $DBUser;

$Link = mysql_connect($DBHost, $DBUser, $DBPass) Or die ("can't open the database");
mysql_select_db($DBName);
}

//------------------

Function Deconnect_bdd()
{
	Global $Link;
	@mysql_close ($Link);
}

//-------------------

Function Add_player_score ($player_name, $player_score, $player_kills)
{
	$player_name = strToLower ($player_name);

Connect_bdd();

//		$Requete = "INSERT INTO highscores ";
//		$Requete .= "(id, player_name, player_score, player_kills) ";
//		$Requete .= "VALUES (5, '$player_name','$player_score', '$player_kills')";
//		$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
	$Requete = "Select * from highscores  ";
	$Requete .= "where player_name = '$player_name'";
	$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());

//If (mysql_num_rows($Result) != 0)  //REMOVED BY ME< MAY NEED TO ADD BACK -BRIAN
//{
	//$Requete = "update highscores ";
	//$Requete .= "set player_score='$player_score', player_kills='$player_kills' ";
	//$Requete .= "where player_name = '$player_name'";
	//$Resultat = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
//}
	//Else
//{
	$Requete = "INSERT INTO highscores ";
	$Requete .= "(id, player_name, player_score, player_kills) ";
	$Requete .= "VALUES (NULL, '$player_name','$player_score', '$player_kills')";
	$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
//}

Deconnect_bdd();

}

//-------------------

Function List_scores()
{

Connect_bdd();

$Requete = "Select player_name, player_score, player_kills  from highscores order by player_score desc";
$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());

Deconnect_bdd();

If (mysql_num_rows($Result) != 0) 
{
	$strCache = "";

	$Count = 0;

	While ($Ligne=mysql_fetch_array($Result))
	{
		$Count++;

		$strCache .= "$Count" . ". ";
		$strCache .= $Ligne["player_name"] . ", guesses "; 
		$strCache .= $Ligne["player_score"] . " ";
		//$strCache .= $Ligne["player_kills"] . " ";
		$strCache .= "<br>";
	}

}
Else
{
	$strCache .= "score>no scores !!!n";
}
Print ($strCache);

}

//------------------- main program

$Op = $HTTP_GET_VARS['op'];

If (isset($Op))
{
	switch($Op)
	{

	Case "Add":
		echo 'URL Read, Ready to try and add to database.' . "<BR>";
		Add_player_score ($HTTP_GET_VARS['player_name'], $HTTP_GET_VARS['player_score'], $HTTP_GET_VARS['player_kills']);
	break;

	Case "List":
		List_scores();
	break;

}
}
?>
<?php 
@define('IN_PHPBB', true); 
@$phpbb_root_path = '../../forums/'; 
global $phpbb_root_path;
include("../../HEADER.php"); ?> 
Here is a small little online score chart demo.
<br>
<a href="http://www.nzgames.org/games/guessinggame/GuessingGame.zip">Download the guessing game to try it out.</a>
<br>
<br>
Score Chart.
<br>
<?php 
List_scores();
include("../../FOOTER.php"); ?> 
?>

The problem is in the Add_player_score function... I have tried many things but I cannot get it to sort properlly... also I want it to only hold the top 10 (or low 10, in this case) scores and to have them sorted numerically by score...

Also in the Add_player_score function I have noticed that whenever I add any score through a program that I write its all lowercase, is there any ways to store if the letters are capitalized?

Thanks ahead for your responses. 🙂

[kakki]

maybe this:

// ....
Function Add_player_score ($player_name, $player_score, $player_kills)
{
	// $player_name = strToLower ($player_name);
        // ^^^^ thats 4 your question concerning "lowercase"

Connect_bdd();
// whats that for, you dont do anything with the result from that
/*	
	$Requete = "Select * from highscores  ";
	$Requete .= "where player_name = '$player_name' ORDER BY score ASC LIMIT 0,10"; // or change that ASC to DESC ...
	$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
*/
		$Requete = "INSERT INTO highscores ";
		$Requete .= "(id, player_name, player_score, player_kills) ";
		$Requete .= "VALUES (NULL, '$player_name','$player_score', '$player_kills')";// should work, if id is auto_increment
		$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());


Deconnect_bdd();

}
// ....

[kakki]

i think for "The problem is in the Add_player_score function... I have tried many things but I cannot get it to sort properlly... also I want it to only hold the top 10 (or low 10, in this case) scores and to have them sorted numerically by score..."
you have to change:

Function List_scores()
{	
	Connect_bdd();	
	$Requete = "Select player_name, player_score, player_kills  from highscores order by player_score desc";
// ......

to

Function List_scores()
{	
	Connect_bdd();	
	$Requete = "Select player_name, player_score, player_kills  from highscores order by player_score desc LIMIT 0,10"; // limit -> "only for top10"

[evilsanta]

Thanks for the reply, the words can now be capitalized... but theres still something wrong with the sorting ... http://nzgames.org/games/guessinggame/highscore.php

Ill post my code as I have a habit of not implementing fixed code properly...

<?php
$conf = array();
include ('conf.php');

$DBUser = $conf['DB_USER'];
$DBPass = $conf['DB_PASS'];
$DBHost = $conf['DB_HOST'];
$DBName = $conf['DB_NAME'];

Function Connect_bdd()
{
Global $DBName, $DBHost, $DBPass, $DBUser;

$Link = mysql_connect($DBHost, $DBUser, $DBPass) Or die ("can't open the database");
mysql_select_db($DBName);
}

//------------------

Function Deconnect_bdd()
{
	Global $Link;
	@mysql_close ($Link);
}

//-------------------

Function Add_player_score ($player_name, $player_score, $player_kills)
{
    // $player_name = strToLower ($player_name);
        // ^^^^ thats 4 your question concerning "lowercase"

Connect_bdd();

$Requete = "INSERT INTO highscores ";
$Requete .= "(id, player_name, player_score, player_kills) ";
$Requete .= "VALUES (0, '$player_name','$player_score', '$player_kills')"; // why do you add a '5' as id, if its auto_increment, just add nothing or 0
$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
//    $Requete = "Select * from highscores  ";
    $Requete = "Select * from highscores  ";
    $Requete .= "where player_name = '$player_name' ORDER BY score ASC LIMIT 0,10"; // or change that ASC to DESC ...
    $Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
        $Requete = "INSERT INTO highscores ";
        $Requete .= "(id, player_name, player_score, player_kills) ";
        $Requete .= "VALUES (NULL, '$player_name','$player_score', '$player_kills')";
        $Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());


Deconnect_bdd();

} 

//-------------------

Function List_scores()
{

Connect_bdd();

    $Requete = "Select player_name, player_score, player_kills  from highscores order by player_score desc LIMIT 0,10"; // limit -> "only for top10" 
$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());

Deconnect_bdd();

If (mysql_num_rows($Result) != 0) 
{
	$strCache = "";

	$Count = 0;

	While ($Ligne=mysql_fetch_array($Result))
	{
		$Count++;

		$strCache .= "$Count" . ". ";
		$strCache .= $Ligne["player_name"] . ", guesses "; 
		$strCache .= $Ligne["player_score"] . " ";
		//$strCache .= $Ligne["player_kills"] . " ";
		$strCache .= "<br>";
	}

}
Else
{
	$strCache .= "score>no scores !!!n";
}
Print ($strCache);

}

//------------------- main program

$Op = $HTTP_GET_VARS['op'];

If (isset($Op))
{
	switch($Op)
	{

	Case "Add":
		echo 'URL Read, Ready to try and add to database.' . "<BR>";
		Add_player_score ($HTTP_GET_VARS['player_name'], $HTTP_GET_VARS['player_score'], $HTTP_GET_VARS['player_kills']);
	break;

	Case "List":
		List_scores();
	break;

}
}
?>
<?php 
@define('IN_PHPBB', true); 
@$phpbb_root_path = '../../forums/'; 
global $phpbb_root_path;
include("../../HEADER.php"); ?> 
Here is a small little online score chart demo.
<br>
<a href="http://www.nzgames.org/games/guessinggame/GuessingGame.zip">Download the guessing game to try it out.</a>
<br>
<br>
Score Chart.
<br>
<?php 
List_scores();
include("../../FOOTER.php"); ?> 
?>

Anything I did wrong? B/c I tried several combinations of things, sadly nothing worked.

EDIT: I see you made a change to your code, well I added that in as well, same result. 🙁

[kakki]

i think you use a "varchar"/text-field for the score, so orderby isnt workin properly.

if you change that column "player_score" to INT
and change List_scores() to:

Function List_scores()
{	
Connect_bdd();	
$Requete = "Select player_name, player_score, player_kills  from highscores order by player_score asc LIMIT 0,10"; // limit -> "only for top10" 
$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
If (mysql_num_rows($Result) != 0) 
{
$strCache = "";		
$Count = 0;		
While ($Ligne=mysql_fetch_array($Result))
{
$Count++;						
$strCache .= "$Count" . ". ";
$strCache .= $Ligne["player_name"] . ", guesses "; 
$strCache .= $Ligne["player_score"] . " ";
//$strCache .= $Ligne["player_kills"] . " ";
$strCache .= "<br>";
}		
}
Else
{
$strCache .= "score>no scores !!!n";
}
Print ($strCache);
Deconnect_bdd();

}

[kakki]

... and you may change that "Function Add_player_score" to

Function Add_player_score ($player_name, $player_score, $player_kills)
{
Connect_bdd();    

$Requete = "INSERT INTO highscores ";
$Requete .= "(id, player_name, player_score, player_kills) ";
$Requete .= "VALUES (0, '$player_name','$player_score', '$player_kills')"; 
$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
Deconnect_bdd();
}

ALL WILL WORK ( on first glance )

[evilsanta]

kakki wrote:

... and you may change that "Function Add_player_score" to

Function Add_player_score ($player_name, $player_score, $player_kills)
{
Connect_bdd();    

$Requete = "INSERT INTO highscores ";
$Requete .= "(id, player_name, player_score, player_kills) ";
$Requete .= "VALUES (0, '$player_name','$player_score', '$player_kills')"; 
$Result = mysql_query($Requete)  Or die ($Requete . "<br>" . mysql_error());
Deconnect_bdd();
}

ALL WILL WORK ( on first glance )

Didnt even have to do that, worked by doing the above, thank you very much for your help. 🙂

(BTW that above code isnt needed if it works right? B/c I just dont want it to fix some unseen error then I dont add it and it causes problems. 🙂 )

[kakki]

idont know, you add the player value twice ...
and you do a select - qeury in there ( for no use )
.... but

you're welcome ... i was glad i could help

[evilsanta]

Also just a little side question, but its not really needed...

But lets say you have 10 people who have a score of 0, then a new guy gets a score of 0, in the above code it wouldnt add him to the list, is it possible to put all new ones at the top of people who have the same score? (sorry, this is somewhat hard to explane).

[evilsanta]

Ok I was thinking about it for a bit and I am guessing that it has to do with the following line...

$Requete = "Select player_name, player_score, player_kills  from highscores order by player_score asc LIMIT 0,10";

Is there anyway that I can sort it by ascending player_score and descending IDs? Because I am guessing that that is how it would be done, I just dont know how to do both at once...

EDIT: Nvrmind, all I needed to do was change it to this

$Requete = "Select player_name, player_score, player_kills  from highscores order by player_score asc, id desc LIMIT 0,10";