Ranking Script - Everyone same rank =/

[MasterACE14]

Evening Everyone,

 

I have a script which is suppose to rank every person on my website by the average of their strike, defence and covert columns in MySQL. But it is ranking everyone the amount of people that are registered. So basically, everyone currently registered adds up to 28 people, and everyone is ranked 28, instead of 1,2,3 etc.

 

here's my script:

<?php
/*** Work out player ranks ***/

$other_new_sql = mysql_query("UPDATE `cf_users` SET `score` = strikeaction+defenceaction+covertaction/3");

// First, we query the database...
$sql = mysql_query("SELECT * FROM `cf_users` order by `score` DESC");

$other_sql = mysql_query("SELECT * FROM `cf_users` order by `score` DESC");

// start off with the rank as 1 (this will be changed in the loop)
$i = 1;

// start the loop
while($row = mysql_fetch_assoc($sql)){


// I'm not very good with original var names :S, but this will update the rank (so, the highest score will become rank 1.
$new_sql = mysql_query("UPDATE `cf_users` SET `rank` = '$i'");

// Then, add one to the counter, so the second becomes rank 2, third rank 3, etc. etc.
$i++;

// close the loop
}
?>

 

Regards ACE

[monkeybidz]

$sql & $other_sql are calling the same thing. I would use $i=0

[MasterACE14]

setting $i = 0 just makes everyones rank "total number of players -1" , so everyone is rakn 27.  :-\

 

any other ideas?

[monkeybidz]

I think I have a fix, but I need to see what VARS you are using after $i++ to identify a user.

[MasterACE14]

<?php
$all_player_username = all_table("username");

 

all_table function:

<?php
// Select fields from all the users tables
function all_table($select){
    $sql = "SELECT `" . $select . "` FROM `" . what_table($select) . "`";
    $rs = mysql_query($sql) or die('Query:<br />' . $sql . '<br /><br />Error:<br />' . mysql_error());
    while($row = mysql_fetch_assoc($rs)){
        if(isset($row[$select])){return $row[$select];}
    }
};

 

by the way, this while loop to rank everyone runs on a hourly cronjob.

[monkeybidz]

$all_player_username =  mysql_result($rs,$i,"username");

[MasterACE14]

that doesnt work, $all_player_username is called before the while loop.

[monkeybidz]

You show this:

$all_player_username = all_table("username");

 

Is all_table valid?

 

Maybe?

 

$all_player_username = $all_table["username"];

 

 

 

[MasterACE14]

yeah, it works fine, I use it on quite a few pages, like displaying all people on my site, and it works fine echo'ing them into a table.

[monkeybidz]

Are you using includes just like other pages? I think you are just ordering by DESC to many times like I mentioned earlier where you query twice:

// First, we query the database...
$sql = mysql_query("SELECT * FROM `cf_users` order by `score` DESC");

$other_sql = mysql_query("SELECT * FROM `cf_users` order by `score` DESC");

You are also trying to update $other_new_sql before the other query can be executed.

[MasterACE14]

just removed $other and still no success, I'm off for the night, so I'll post my whole cronjob code. See if maybe something is out of place...

 

<?php // cron job
// error_reporting(E_ALL);

/** includes **/
include_once '/home/ace/public_html/conflictingforces/functions.php';
include_once '/home/ace/public_html/conflictingforces/weapons.php';
include_once '/home/ace/public_html/conflictingforces/armors.php';
include_once '/home/ace/public_html/conflictingforces/vehicles.php';

$link = mysql_connect("localhost", "username", "password");
    mysql_select_db("database", $link);

// connect to the database
$rs = mysql_connect( "localhost", "username", "password" );
$rs = mysql_select_db( "database" );	

// --- variables needed for turn change --- //

/**********************/
// Player Select Variables //
/**********************/

// User Information
$all_player_accountid = all_table("id");
$all_player_username = all_table("username");
$all_player_email = all_table("email");

// Military Effectiveness
$all_player_strikeaction = all_table("strikeaction");
$all_player_defenceaction = all_table("defenceaction");
$all_player_covertaction = all_table("covertaction");

// General Statistics
$all_player_ammo = all_table("ammo");
$all_player_money = all_table("money");
$all_player_level = all_table("level");
$all_player_currentexp = all_table("currentexp");
$all_player_neededexp = all_table("neededexp");
$all_player_skilllevel = all_table("skilllevel");
$all_player_skillpoints = all_table("skillpoints");
$all_player_strength = all_table("strength");
$all_player_agility = all_table("agility");
$all_player_intelligence = all_table("intelligence");

// Talent Statistics
$all_player_talentid = all_table("talentid");
$all_player_talentname = all_table("talentname");
$all_player_talentlevel = all_table("talentlevel");
$all_player_talentpoints = all_table("talentpoints");

// Alliance Information
$all_player_allianceid = all_table("allianceid");
$all_player_alliancename = all_table("alliancename");
$all_player_allianceposition = all_table("allianceposition");

// Inventory
$all_player_weaponid = all_table("weaponid");
$all_player_armorid = all_table("armorid");
$all_player_vehicleid = all_table("vehicleid");

// Equipped Items

// Names
$equippedweaponname = select_array($weapons,$all_player_weaponid,"weapon");
$equippedarmorname = select_array($armors,$all_player_armorid,"armor");
$equippedvehiclename = select_array($vehicles,$all_player_vehicleid,"vehicle");

// ID
$equippedweaponid = select_array($weapons,$all_player_weaponid,"id");
$equippedarmorid = select_array($armors,$all_player_armorid,"id");
$equippedvehicleid = select_array($vehicles,$all_player_vehicleid,"id");

// Damage, Defence and Power
$equippedweapondamage = select_array($weapons,$all_player_weaponid,"damage");
$equippedarmordefence = select_array($armors,$all_player_armorid,"defence");
$equippedvehiclepower = select_array($vehicles,$all_player_vehicleid,"power");


/*********************** MATHS ***********************/

// Players Actions Total
$total_action = $all_player_strikeaction + $all_player_defenceaction + $all_player_covertaction;

// Workout the Players Income //
$players_income = ((($all_player_strikeaction * $all_player_defenceaction) * $all_player_covertaction) / 100000000);

$new_ammo = $all_player_ammo + 3;

/****** Give Players their Income ******/

//**** Then do the Query ****//

// SQL query to update the players account into the database
//$sql = "UPDATE `cf_users` SET money=money+{$players_income}";
$sql = "UPDATE `cf_users` SET money=money+strikeaction+defenceaction+covertaction/10";

// execute the query
$rs = mysql_query( $sql ) or die('Query:<br />' . $sql . '<br /><br />Error:<br />' . mysql_error());


/****** Give Players their Ammo ******/ 

// SQL query to update the players account into the database
$sqll = "UPDATE `cf_users` SET ammo=ammo+3";

// execute the query
$rss = mysql_query( $sqll ) or die('Query:<br />' . $sqll . '<br /><br />Error:<br />' . mysql_error());


/*** Work out player ranks ***/

$other_new_sql = mysql_query("UPDATE `cf_users` SET `score` = strikeaction+defenceaction+covertaction/3");

// First, we query the database...
$sql = mysql_query("SELECT * FROM `cf_users` order by `score` DESC");

// start off with the rank as 1 (this will be changed in the loop)
$i = 1;

// start the loop
while($row = mysql_fetch_assoc($sql)){


// I'm not very good with original var names :S, but this will update the rank (so, the highest score will become rank 1.
$new_sql = mysql_query("UPDATE `cf_users` SET `rank` = '$i'");

// Then, add one to the counter, so the second becomes rank 2, third rank 3, etc. etc.
$i++;

// close the loop
}

mysql_close( $link );

?>

 

Regards ACE - back tommorow afternoon...

[MasterACE14]

bump

[mr_mind]

You were using a lot of unnescessary mysql actions which would slow down the server substantially on a high traffic server

 

This should be the contents of rate_user.php

 

What i do here is set up the rating entry and make sure the user exists

Then i submit the data to the database there the column "userid" (the username) is equal to the submitted username

In this i am assuming that the column for the usernames is userid. you can change this to whatever the real value is

I am also assuming that you are checking the username of the user submitting with $_SESSION['userid'] which can be whatever you want

i do not know what "strikeaction+defenceaction+covertaction" is to you so i left it out of the form. you can add that later

 

<?php
function user_exists($user) {
	$user_exists_query = mysql_query("SELECT * FROM cf_users WHERE userid='$user'");
	if(mysql_num_rows($user_exists_query)>0) {
		return TRUE;
	}
	else {
		return FALSE;
	}
}
if($_POST['rating']) {
	if($_POST['userid']) {
		if($_SESSION['userid']!=$_POST['userid']) {
			$rating_user = $_POST['userid'];
			if(user_exists($rating_user)) {
				$rating = (strikeaction+defenceaction+covertaction)/3
				if(mysql_query("UPDATE cf_users SET score='$rating' WHERE userid='$rating_user'")) {
					$message = 'User rated';
				}
				else {
					$error = 'Failed connecting to server. Please refresh.';
				}
			}
			else {
				$error = 'User does not exist';
			}
		}
		else {
			$error = 'You can not rate yourself';
	}
	else {
		$error = 'Userid must be supplied';
	}
}
$title = 'Rate a user';
?>
<html>
<head>
	<title>
		<?php print $title ?>
	</title>
</head>
<body>
	<?php
		if($_SESSION['userid']) {
			if(!$_POST['rating']) {
				print '<form action=rate_user.php method=post>';
				print 'User ID:<br /><input type=text name=userid /><br />';
				print '</form>';
			}
			else {
				if(isset($message)) {
					print $message;
				}
				else {
					if(isset($error)) {
						print $error;
						print '<form action=rate_user.php method=post>';
						print 'User ID:<br /><input type=text name=userid /><br />';
						print '</form>';
					}
				}
			}
		}
		else {
			print 'You must be logged in to view this page';
		}
	?>
</body>
</html>

 

After we have done this we need to make the page in which it will show the user rank.

Doing it this way allows for easy changing of rating techniques

The following code should be in user_ratings.php

 

<html>
<head>
	<title>
		User Ratings
	</title>
</head>
<body>
	<?php
		if($_SESSION['userid']) {
			$ratings_query = mysql_query("SELECT * FROM cf_users ORDER BY score DESC");
			print '<table>';
			print '<tr><td> User ID </td><td> Rating </td></tr>';
			while($ratings_array = mysql_fetch_array($ratings_query)) {
				print '<tr><td> ' . $ratings_array['userid'] . ' </td><td> ' . $ratings_array['score'] . '</td></tr>';
			}
			print '</table>';
		}
		else {
			print 'You must be logged in to view this page';
		}
	?>
</body>
</html>

[MasterACE14]

I've already got a page which displays all users and their rank, and my file is a cronjob, runs once a hour. And is made to be automatic, no need for forms.

[MasterACE14]

bump