MySQL Forums
Forum List  »  PHP

Updating only increments, rather than setting a value
Posted by: Jesse Albanese
Date: August 19, 2005 04:31PM

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;
}

Options: ReplyQuote


Subject
Written By
Posted
Updating only increments, rather than setting a value
August 19, 2005 04:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.