Pulling my hair out over this one!

[newttster]

I've tried every which way that I can think of to get this to work and still nothing. Basically if a player has item "622" they will earn 4% interest, if they don't they will earn 1%. Any ideas?

 

 

$users=$db->query("SELECT `cybermoneyba` FROM `users` WHERE userid > 0');
$ICStBA=mysql_query("SELECT `inv_qty` FROM `inventory` WHERE `inv_itemid`='622' AND `inv_userid` > 0");
WHILE ($r=$db->fetch_row($ICStBA))
{
if (($r['inv_qty']>=1) AND ($users['cybermoneyba']<10000000))
{
$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*4) WHERE ($r['inv_userid'])= userid");
}
else
{
$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*1) WHERE ((cybermoneyba<10000000) AND (userid= ($ir['userid'])))");
}
}

[sniko]

Simple,

Select users who have the item 622, and a cyber account

<?php
$get = $db->query("SELECT users.userid,inventory.itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE inventory.inv_itemid=622 AND inventory.inv_qty>0 AND users.cybermoney>0");
?>

 

Now that you have the users you want, let's do the interest

<?php
$fourperc = array(); #Create an array for later
while($r = $db->fetch_row($get)) {
  $db->query("UPDATE users SET cybermoney=cybermoney*0.4 WHERE userid={$r['userid']}"); #Add 4% interest
  array_push($fourperc, $r['userid']); #Add to the array of people who have 4% increase
}
?>

 

Now that's done, let's do those who don't have the required things I've edited this query (edit #1)

$get = $db->query("SELECT users.userid,inventory.itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE users.userid NOT IN({$fourperc}) AND users.cybermoney>0"); 
?>

 

Now let's add the 1%

<?php
while($r = $db->fetch_row($get)) {
  $db->query("UPDATE users SET cybermoney=cybermoney*0.1 WHERE userid={$r['userid']}"); #Add 1% interest
}
?>

 

And for the full script

<?php
$get = $db->query("SELECT users.userid,inventory.itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE inventory.inv_itemid=622 AND inventory.inv_qty>0 AND users.cybermoney>0");

$fourperc = array(); #Create an array for later
while($r = $db->fetch_row($get)) {
  $db->query("UPDATE users SET cybermoney=cybermoney*0.4 WHERE userid={$r['userid']}"); #Add 4% interest
  array_push($fourperc, $r['userid']); #Add to the array of people who have 4% increase
}


$get = $db->query("SELECT users.userid,inventory.itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE users.userid NOT IN({$fourperc}) AND users.cybermoney>0"); 

while($r = $db->fetch_row($get)) {
  $db->query("UPDATE users SET cybermoney=cybermoney*0.1 WHERE userid={$r['userid']}"); #Add 1% interest
}
?>

 

I hope that helps

~sniko

Edits

Added array_push

Edited December 31, 2011 by sniko

[sniko]

Sniko, you are really going to query the users table twice? So we use resources twice for almost the same task?

My query sucks, but it only runs though once.

True, but I find that logical code is easier to read (as well as it being 3am (yes, I am using your excuse :p)) but I do see why you are saying what you are saying, plus we all have our methods

[newttster]

I really appreciate that you have both put forth some great ideas. Unfortunately, neither one worked. I am finding this so very frustrating. *Grrrr* I'll keep playing with both ... hopefully something will work soon.

[Uridium]

None of these methods actually show 4% i did a test of this script and 100 + 4% is >> 104 yet when i ran your queries SomeRandomBastard i ended up with this value >> 756824 as the total

[sniko]

I really appreciate that you have both put forth some great ideas. Unfortunately, neither one worked. I am finding this so very frustrating. *Grrrr* I'll keep playing with both ... hopefully something will work soon.

When testing my method, did you come across any errors visible on your screen? Or did it just not update?

Try putting or die(mysql_error()); at the end of each query, just for testing purposes.

[Djkanna]

$getUsers = $db->query('SELECT `userid` FROM `users` WHERE (`cybermoneyba` > 0) ');
if ($db->num_rows($getUsers) > 0) {
   $interest = 1;
   while ($users = $db->fetch_row($getUsers)) {

       $checkItem = $db->query('SELECT `inv_id` FROM `inventory` WHERE (`inv_userid` = '.$users['userid'].' && `inv_itemid` = 622) LIMIT 1');
       if ($db->num_rows($checkItem) > 0) {
           $interest = 4;
       }
       $db->query('UPDATE `users` SET `cybermoneyba` = `cybermoneyba` + ((`cybermoneyba`/100)*'.$interest.') WHERE (`userid` = '.$users['userid'].')');
   }
}

Maybe?

[newttster]

Okay ... I have been testing everyone's code that they have posted here or sent me via pm. So far ... the only one that has shown any updating to the db is Sniko's. I made a few changes to the code that he posted. The first have of the code works fine. The second half I am getting an error in relation to the array.

<b>QUERY ERROR:</b> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'in_(Array) AND users.cybermoneyba>0' at line 1<br />

Query was SELECT users.userid,inventory.inv_itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE users.userid NOT in_(Array) AND users.cybermoneyba>0

The code that I have is:

 

$get = $db->query("SELECT users.userid,inventory.inv_itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE inventory.inv_itemid=622 AND inventory.inv_qty>0 AND users.cybermoneyba>0")or die(mysql_error());
$fourperc = array(); #Create an array for later
while($r = $db->fetch_row($get)) 
{ 
$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*4) WHERE userid={$r['userid']}")or die(mysql_error()); #Add 4% interest  
array_push($fourperc, $r['userid']); #Add to the array of people who have 4% increase
}  
$get = $db->query("SELECT users.userid,inventory.inv_itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE users.userid NOT in_({$fourperc}) AND users.cybermoneyba>0")or die(mysql_error());
while($r = $db->fetch_row($get))
{   
$db->query("UPDATE users SET cybermoneyba=cybermoneyba*0.1 WHERE userid={$r['userid']}or die(mysql_error())"); #Add 1% interest
}  

 

In the meantime ... I will be further testing out Randoms' and Djs' as well. I'm hoping that with a combination of everyones ideas we will be able to come up with a working code that only accesses the db once. *crosses fingers* I am so far into this now that I refuse to give up .... bald or not!!!

[sniko]

That would be due to the _ in in_({$fourperc})

NOT IN ({$fourperc})

I am not sure if you need to do anything with $fourperc, but try it out =]

Ah yea, thanks SRB

Sorry for the typo

[newttster]

Okay ... you guys are not going to believe this ... but I actually got it to work. I changed the code just a little and if I'm understanding it ... it is accessing the db just once. Here it is. What do you guys think???

 

 $get = $db->query("SELECT users.userid,inventory.inv_itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE inventory.inv_itemid=622 AND inventory.inv_qty>0 AND users.cybermoneyba>0");
$fourperc = array(); #Create an array for later
while($r = $db->fetch_row($get)) 
{ 
$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*4) WHERE userid={$r['userid']} AND cybermoneyba>0 AND cybermoneyba<10000000"); #Add 4% interest  
array_push($fourperc, $r['userid']); #Add to the array of people who have 4% increase
}  
if (!in_array(($r['userid']),$fourperc))
{   
$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*1) WHERE cybermoneyba >0 AND cybermoneyba<10000000"); #Add 1% interest
} 

 

Damn it!! Okay ... I lied. It works to a degree ... what it is doing is adding the 4% to ID's that have item 622 and adding 1% to ID's that do not have item 622 ... but ... then it is adding 1% compunded to the ID's that have item 622.

IE: $1,000,000 + 4% =$1,040,000 then it adds 1% to the $1,040,000 giving $1,050,400.

It is adding just the 1% to the ID's that do not have the item 622.

Edited January 2, 2012 by newttster

[sniko]

Damn it!! Okay ... I lied. It works to a degree ... what it is doing is adding the 4% to ID's that have item 622 and adding 1% to ID's that do not have item 622 ... but ... then it is adding 1% compunded to the ID's that have item 622.

IE: $1,000,000 + 4% =$1,040,000 then it adds 1% to the $1,040,000 giving $1,050,400.

It is adding just the 1% to the ID's that do not have the item 622.

If it's adding the 1% to it, make line 5

$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*3) WHERE userid={$r['userid']} AND cybermoneyba>0 AND cybermoneyba<10000000"); #Add 4% interest  

[newttster]

If it's adding the 1% to it, make line 5

$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*3) WHERE userid={$r['userid']} AND cybermoneyba>0 AND cybermoneyba<10000000"); #Add 4% interest  

I had already thought of that but that won't work because I have other cyberbanks where the interest rates are different.

[sniko]

$get = $db->query("SELECT users.userid,inventory.inv_itemid FROM users LEFT JOIN inventory ON users.userid=inventory.inv_userid WHERE inventory.inv_itemid=622 AND inventory.inv_qty>0 AND users.cybermoneyba>0");
$fourperc = array(); #Create an array for later
while($r = $db->fetch_row($get)) /* Populate the array */
{ 
array_push($fourperc, $r['userid']); #Add to the array of people who have 4% increase
}  

while($r = $db->fetch_row($get)) {
$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*4) WHERE userid IN ($fourperc) AND cybermoneyba>0 AND cybermoneyba<10000000"); #Add 4% interest  

$db->query("UPDATE users SET cybermoneyba=cybermoneyba+((cybermoneyba/100)*1) WHERE userid NOT IN ($fourperc) AND  cybermoneyba >0 AND cybermoneyba<10000000"); #Add 1% interest
}

 

Try that

[newttster]

@ Sniko ... I tried the new code ... now it doesn't update either. I added the or die(mysql_error()); and it is giving me no errors with it. Argghh ... who thought what would appear to be so simple is so damn hard to get working.