Keeps adding

[newttster]

Here is the scenario.

On the users table I have the "maxpoints" set too 7200 for "dlevel" 1, "maxpoints" set too 9600 for "dlevel" 2, "maxpoints" set too 12000 for "dlevel" 3. The problem I am having is that it keeps adding to the "accpoints" and does not stop at the "maxpoints" amount. Here is my code. I've tried a number of ways to get it to stop adding the points. Really stumped on this. Any help would be greatly appreciated.

 

$q2=$db->query("SELECT dlevel FROM users WHERE userid > 0");
WHILE ($r=$db->fetch_row($q2))
{
IF (($r['accpoints'])<= ($r['maxpoints']))
{
$db->query("UPDATE users SET accpoints=accpoints+300 WHERE dlevel=1");
$db->query("UPDATE users SET accpoints=accpoints+400 WHERE dlevel=2");
$db->query("UPDATE users SET accpoints=accpoints+500 WHERE dlevel=3");
}
ELSE
{
$db->query("UPDATE users SET accpoints=accpoints+0");
}
} 

 

Okay ... I figured that part out by adjusting the code to WHERE dlevel=1 and accpoints<=maxpoints")

The next issue I am having is if there are only 99 points or 53 or 210 points to equal the maxpoints ... how would I get it to add that number of points so that it does not exceed the maxpoints. The way the code is now ... it will add the full 300 points if they are a dlevel1. Meaning that if they have 7110 points it will bring the accpoints to 7410 and then stop accumulating points. I want it to just add 90 points so that the accpoints does not exceed the maxpoints of 7200.

Edited December 29, 2011 by newttster
Fixed first part

[secret_life]

Try this :)

 

$q2=$db->query("SELECT `dlevel`,`accpoints`,`maxpoints` FROM `users` WHERE `userid` > 0");
WHILE ($r=$db->fetch_row($q2))
{
   IF (($r['accpoints'])<= ($r['maxpoints']))
   {

if ($r['dlevel'] == 1)
{
$accpointsgain = $r['accpoints']+300;

if ($accpointsgain <= $r['maxpoints'])
{
$db->query("UPDATE users SET accpoints=$accpointsgain WHERE {$r['userid']}");
}

else
{
$db->query("UPDATE users SET accpoints={$r['maxpoints']} WHERE {$r['userid']}");
}
}

if ($r['dlevel'] == 2)
{

$accpointsgain = $r['accpoints']+400;

if ($accpointsgain <= $r['maxpoints'])
{
$db->query("UPDATE users SET accpoints=$accpointsgain WHERE {$r['userid']}");
}

else
{
$db->query("UPDATE users SET accpoints={$r['maxpoints']} WHERE {$r['userid']}");
}
}

if ($r['dlevel'] == 3)
{

$accpointsgain = $r['accpoints']+500;

if ($accpointsgain <= $r['maxpoints'])
{
$db->query("UPDATE users SET accpoints=$accpointsgain WHERE {$r['userid']}");

   }

else

{

$db->query("UPDATE users SET accpoints=$r['maxpoints'] WHERE $r['userid']");

}
  }
 }
}

Edited December 29, 2011 by secret_life

[bluegman991]

You're not selecting accpoints or maxpoints in your query. So therefore they are both null and null is equal to null.

So change your query to

$q2=$db->query("SELECT `dlevel`,`accpoints`,`maxpoints` FROM `users` WHERE `userid` > 0");

[newttster]

Thanks everyone for your help. Got it to work finally with your suggestions and some fiddling. It may not be pretty, but it works.

 

$q2=$db->query("SELECT `dlevel`,`accpoints`,`maxpoints` FROM `users` WHERE `userid` > 0");
WHILE ($r=$db->fetch_row($q2))
{    
IF (($r['accpoints'])<= ($r['maxpoints']))    
{ 
	if ($r['dlevel'] == 1) 
	{
	$accpointsgain = ($r['accpoints']+300);
		if ($accpointsgain <= $r['maxpoints']) 
		{   
		$db->query("UPDATE users SET accpoints=$accpointsgain WHERE dlevel=1 AND accpoints<=maxpoints AND userid > 0"); 
		} 
		else
		{
		$db->query("UPDATE users SET accpoints={$r['maxpoints']} WHERE dlevel=1 AND accpoints<=maxpoints AND userid > 0"); 
		}
	} 
 		if ($r['dlevel'] == 2) 
	{
	$accpointsgain2 = ($r['accpoints']+400);
		if ($accpointsgain2 <= $r['maxpoints']) 
		{   
		$db->query("UPDATE users SET accpoints=$accpointsgain2 WHERE dlevel=2 AND accpoints<=maxpoints AND userid > 0"); 
		} 
		else
		{
		$db->query("UPDATE users SET accpoints={$r['maxpoints']} WHERE dlevel=2 AND accpoints<=maxpoints AND userid > 0"); 
		}
	} 

	if ($r['dlevel'] == 3) 
	{
	$accpointsgain3 = ($r['accpoints']+500);
		if ($accpointsgain3 <= $r['maxpoints']) 
		{   
		$db->query("UPDATE users SET accpoints=$accpointsgain3 WHERE dlevel=3 AND accpoints<=maxpoints AND userid > 0"); 
		} 
		else
		{
		$db->query("UPDATE users SET accpoints={$r['maxpoints']} WHERE dlevel=3 AND accpoints<=maxpoints AND userid > 0"); 
		}
	} 
}
} 

[bineye]

does "dlevel" only go up to 3?

[newttster]

does "dlevel" only go up to 3?

Yes. dlevels are 1,2 and 3. That's it.