Updating only increments, rather than setting a value
Okay, here's the deal. I have a few tables in a single database, and the particular functions I'll be talking about use three of those tables: "members", "donations", and "ranks".
A quick rundown of the fields being used in each table (at least for the purpose of our discussion):
members:
o user_id: int(4)
o user_name: varchar(50)
o post_count: int(6)
o intro_date: date
o rank: int(2)
o banned: enum('FALSE','TRUE')
ranks:
o rank_id: int(2)
o post_req: int(6)
o date_req: int(6)
o donate_req: int(6)
o admin_req: enum('TRUE','FALSE')
donations:
o donate_id: int(4)
o user_id: varchar(50)
o amount: varchar(50)
o date: date
o confirmed: enum('TRUE','FALSE')
o type: enum('GOLD','ITEM')
Okay, with these tables and fields in mind, I have a set of functions that determines "rank" in the "members" table, posted below:
function Check_Ranks()
{
/*
* This function will check all members, determine their rank
* via "GetRank()", and set their new rank.
*/
$check_sql = "SELECT * FROM members";
$check_result = mysql_query($check_sql) or die ("Blarg");
while ($member = mysql_fetch_array($check_result, MYSQL_ASSOC))
{
extract($member);
$new_rank = GetRank($user_id);
$sql = "UPDATE members SET rank=".$new_rank." WHERE user_id=".$user_id;
$result = mysql_query($sql) or die ("Couldn't execute query. New Rank");
}
}
GetRank() returns the new rank number as $new_rank, and (although it's been returning the correct value) I will include it's code below. But first, the problem...
Members ranks are only updated one rank at a time. When I call Check_Ranks(), if the rank -should- be 3, but it is currently 0, it only rises to 1. On the next call, it rises to 2, then 3 on the call after that. However, the $sql variable for the example given (when a user should be at rank 3) contains "UPDATE members SET rank=3 WHERE user_id=15" (I have a test statement that returns that output to me when the $user_id == 15). When I look at the table after this has been called once, though, the "rank" field for the member has only risen by 1 (from 0 to 1, 1 to 2, 2 to 3, etc.).
The MySQL query should set the "rank" field to 3, though, right? Not merely increment it by one.
Below I will include the GetRank() function, although I can't see whay it would be causing the problem, as it's been testinf and proved to provide the correct results.
function GetRank($user_id)
{
/*
* For the given user ID, returns an updated value of what the
* Rank of that member should be. If the rank given is set by
* the Captains, just returns that rank.
*/
$member_sql = "SELECT * FROM members WHERE user_id='$user_id'";
$member_result = mysql_query($member_sql) or die ("Couldn't execute query. MEMBERS");
$member_row = mysql_fetch_array($member_result, MYSQL_ASSOC);
extract($member_row);
$rank_sql = "SELECT * FROM ranks WHERE rank_id='".$rank."'";
$rank_result = mysql_query($rank_sql) or die ("Couldn't execute query. RANKS");
$rank_row = mysql_fetch_array($rank_result, MYSQL_ASSOC);
extract($rank_row);
if ($admin_req == "TRUE") {return $rank;}
$donation_amount = 0;
$donate_sql = "SELECT * from DONATIONS WHERE user_id='$user_id'";
$donate_result = mysql_query($donate_sql) or die ("Couldn't execute query. DONATIONS");
$current_donate_id = 0;
$all_donations = false;
while ($donations = mysql_fetch_array($donate_result))
{
extract($donations);
$all_donations[$current_donate_id] = $donate_id;
$current_donate_id++;
}
if ($all_donations)
{
foreach ($all_donations as $key => $current_id)
{
$sql = "SELECT * from DONATIONS WHERE donate_id='" . $current_id . "'";
$result = mysql_query($sql) or die ("Couldn't execute query. DONATE INFO");
$row = mysql_fetch_array($result, MYSQL_ASSOC);
extract($row);
if ($confirmed == "TRUE")
{
if ($type == "GOLD") {$donation_amount += $amount;}
}
}
}
// Get the number of days in the Guild
/* "RCDate()" is merely a "date()" func that returns
* a null string when there is no date, rather than
* a bunch of zeros.
*/
if (RCdate("d-m-Y",$intro_date) != "")
{
$now = time("d-m-Y");
$TimeSpan = ($now - strtotime($intro_date))/86400;
}
else {$TimeSpan = 0;}
$new_rank = $rank;
$ranktest_sql = "SELECT * FROM ranks";
$ranktest_result = mysql_query($ranktest_sql);
while ($ranktest = mysql_fetch_array($ranktest_result, MYSQL_ASSOC))
{
extract($ranktest);
if (($admin_req == "FALSE") and ($rank_id > $rank))
{
if (($post_count > ($post_req-1)) and
($TimeSpan > $date_req) and
($donation_amount > ($donate_req-1)) ) {$new_rank = $rank_id;}
}
}
return $new_rank;
}