[RESOLVED] attribute donations to correct members retroactively.

schwim

Hi there everyone!

The situation is as follows. For years now, people have donated to a particular site and the owner of the site has adjusted their rank when necessary(1-25 gets one rank, 26-100 another, etc.). There was no db table with donations and he just kept record of the donations in a spreadsheet. In my attempt to make the system more of an automated affair, I have created a table with all of the past donations and when someone donates today, it gets dropped into a table in the db. I have also entered all past donations into this table and attributed them to the corresponding user_id.

What I have now is thousands of entries, with two important cells, user_id and donation amount.

I have another row with important user info. One of the cells I'd like to have is donation_total. For this to be populated however, I need to create a php script that grabs that donations table, sorts via each user_id, finds every donation that can be attributed by them and puts the resulting total into their table.

I know how to do this for a particular single user and have already written a function for that. What I need to do now though is create one that handles that complete table and handles every user_id that is found in it in a batch process. That's where I need help.

I would greatly appreciate it if someone could help me create a loop that will:

1) handle each user_id found in the donations table
2) total the amount of donations found in the table for their user_id and place it in the totals cell

Any ideas are welcome and if you need any more information concerning the system, please let me know.

thanks,
json

dalecosp

schwim;10971192 wrote:
Hi there everyone!

The situation is as follows. For years now, people have donated to a particular site and the owner of the site has adjusted their rank when necessary(1-25 gets one rank, 26-100 another, etc.). There was no db table with donations and he just kept record of the donations in a spreadsheet. In my attempt to make the system more of an automated affair, I have created a table with all of the past donations and when someone donates today, it gets dropped into a table in the db. I have also entered all past donations into this table and attributed them to the corresponding user_id.

What I have now is thousands of entries, with two important cells, user_id and donation amount.

I have another row with important user info. One of the cells I'd like to have is donation_total. For this to be populated however, I need to create a php script that grabs that donations table, sorts via each user_id, finds every donation that can be attributed by them and puts the resulting total into their table.

I know how to do this for a particular single user and have already written a function for that. What I need to do now though is create one that handles that complete table and handles every user_id that is found in it in a batch process. That's where I need help.

I would greatly appreciate it if someone could help me create a loop that will:

1) handle each user_id found in the donations table
2) total the amount of donations found in the table for their user_id and place it in the totals cell

Any ideas are welcome and if you need any more information concerning the system, please let me know.

thanks,
json

How many user_id 's are we talking about? The logical thing from a "unlimited resources" POV is to grab all the user_id 's into an array, (mysql_fetch_array(), for example, if that's the D😎, make sure each name appears only once [array_unique()], then do a calculation loop for each name in that array. Can you show us a start on that?

EDIT: I should also add that if the list of users is really large, this might not work too well because PHP might exhaust its allowable memory resources to create a very large array. Hence my original question....

bradgrafelman

Er.. unless I'm missing something, it would be far simpler/more optimized to just let SQL take care of all of that in a single query:

UPDATE users
SET total_donations = (
  SELECT SUM(amount) FROM donations WHERE user_id=users.id
)

schwim

Thank you both very much for taking the time to reply. I'm very inexperienced in arrays, but have tried to draft a function. There may be ridiculously newbie mistakes in it, but I hope it explains my intent as well as gives you the necessary db info. Might I need a distinct in the initial query?

/*function to completely rebuild total donated table.  If the total for the user_id is right, leave it alone, if it's incorrect or missing, update the total.*/

$query1="SELECT user_id, mc_gross FROM donation_paypal  ORDER BY (user_id) ASC";;
	$result1=mysql_query($query1) or die (mysql_error());
	$numr=mysql_num_rows($result1);
	if($numr>0){
		while($rows=mysql_fetch_array($result1)){
			$donator_id=$rows['user_id'];
			$total_donated=$rows['mc_gross'];

		// I need to check for the existence of the user_id in the totals table.  If it exists, check/replace and if it's not there, insert new entry.
		 $query2="SELECT user_id, total_donated FROM donation_totals WHERE user_id=$donator_id";
			$result2=mysql_query($query2) or die (mysql_error());
			$numr=mysql_num_rows($result2);
			if($numr>0){
				while($row=mysql_fetch_array($result2)){
					$user_id=$row['user_id'];
					$old_total_donated=$row['total_donated'];

					//totals do not match, so overwrite new total into user_id					
					if($old_total_donated != $total_donated){
						mysql_query("UPDATE donation_totals SET total_donated = '$total_donated' WHERE user_id = $user_id") or die("Cannot query the database.<br><br>" . mysql_error());
					}
				}
			}else{
				// Insert new row into table for previously missing user_id
				$query="INSERT INTO donation_totals (user_id,total_donated) VALUES ('$user_id','$total_donated')";
				$results=mysql_query($query) or die(mysql_error());
			}
	}
}

Thanks again very much for your help!
json

schwim

Well, trying to run this function, I see it's incredible flaw, that being that it's not adding the various donations up but trying to simply replace the last donation with the current.

Any help on getting this function to add the donations for each unique user_id and insert a total for them into the new table would be greatly appreciated.

thanks,
json

schwim

Any help at all, pretty please?

schwim

Okee dokee then.

cretaceous

your logic seems ok though I'd use the same structure on updates as you do on inserts:

$query3="UPDATE donation_totals SET total_donated = '$total_donated' WHERE user_id = $user_id"
$result3=mysql_query($query3) or die (mysql_error());

but clearly you are not getting the correct sum value - is mc_gross entered correctly?
do you get the error on both updates and inserts??

echo some of the results to the page to figure out what is wrong

you are setting this value twice:
$numr=mysql_num_rows($result1);
and
$numr=mysql_num_rows($result2);
though I guess it is ok as you have it

or do what brad g suggested

schwim

Thanks so much for your help.

I'd love to use Brad's solution, but I'm having problems setting it up to insert if the user_id isn't found in the totals table:

[EDIT] replacing code with full function with the hope that it shows the pertinent info[/EDIT]

/*function to completely rebuild total donated table.  If the total for the user_id is right, leave it alone, if it's incorrect or missing, update the total.*/

$query1="SELECT user_id, mc_gross FROM donation_paypal  ORDER BY (user_id) ASC";;
	$result1=mysql_query($query1) or die (mysql_error());
	$numr=mysql_num_rows($result1);
	if($numr>0){
		while($rows=mysql_fetch_array($result1)){
			$donator_id=$rows['user_id'];
			$total_donated=$rows['mc_gross'];
			echo("<b>. </b>");
			// I need to check for the existence of the user_id in the totals table.  If it exists, check/replace and if it's not there, insert new entry.
			 $query1="SELECT user_id, total_donated FROM donation_totals WHERE user_id=$donator_id";
			$result1=mysql_query($query1) or die (mysql_error());
			$numr=mysql_num_rows($result1);
			if($numr>0){
				// We are updating! Yay!
				UPDATE donation_totals
				SET total_donated = (
					SELECT SUM(amount) FROM donation_paypal WHERE user_id=donator_id
				)
			}else{
				// We are creating new record, so still yay!
				INSERT donation_totals
				SET total_donated = (
					SELECT SUM(amount) FROM donation_paypal WHERE user_id=$donator_id
				)
			}
		}
	}

You can see where my failure at adaptation is at the bottom. I haven't tested it because that part causes it to error out. If I can fix that, I hope to be able to see where else it's broken.

thanks,
json

cretaceous

first if you are runing queries inside other queries make sure each query and result and susbsequent row set has a different name !
e.g. $query1 then $query2 - not both $query1

check your sub selects are working
you missed a"$" character from the donator id in the sql update

$query1="SELECT user_id, mc_gross FROM donation_paypal  ORDER BY user_id ";
// no need for user id in brackets and no need for ASC as that is the default 
	$result1=mysql_query($query1) or die (mysql_error());
	$numr=mysql_num_rows($result1);
	if($numr>0){
		while($row1=mysql_fetch_array($result1)){
			$donator_id=$row1['user_id'];
			$total_donated=$row1['mc_gross'];
			echo "<b>. </b>";
// no need to put echo value in brackets
// why not make it useful and do:
echo "<br>".$donator_id." : ".$total_donated;


		// I need to check for the existence of the user_id in the totals table.  If it exists, check/replace and if it's not there, insert new entry.
		 $query2="SELECT user_id, total_donated FROM donation_totals WHERE user_id=$donator_id";
		$result2=mysql_query($query2) or die (mysql_error());
		$numr=mysql_num_rows($result2);
		if($numr>0){
			// We are updating! Yay!
			$query3="UPDATE donation_totals
			SET total_donated = (
				SELECT SUM(amount) FROM donation_paypal WHERE user_id=$donator_id)";
		}else{
			// We are creating new record, so still yay!
			$query3="INSERT donation_totals
			SET total_donated = (
				SELECT SUM(amount) FROM donation_paypal WHERE user_id=$donator_id)";
		}
//run the sql insert or update
if($query3) $result3=mysql_query($query3) or die (mysql_error());
		}
	}

but it might be better to just stick to one method now and not add the sub select - try and get it to work on a single line first

schwim

Thanks so much for your continued help!

I swapped out my function for your repaired, making only one change: SUM(amount) got changed to sum(mc_gross) since I'm adding all mc_gross values together per user_id from donation_paypal to insert the sum into donation_totals.

While running, I got this error:

1 : 25.00.
1 : 25.00Duplicate entry '0' for key 1

When looking in the database, it has inserted a single entry with a sum into the table for user_id of 0. I should mention that there is no user_id of 0, but it starts at 1.

I'm sure that the problem is being caused my manipulation of of the variables, but I haven't been able to find the problem area yet. If you happen to see anything, I'd very much appreciate a nudge in the right direction 🙂

thanks,
json

cretaceous

wrap the entire third set of querying in a conditional "if"

so : if ($donator_id){...

that way if $donator_id is zero (or not defined) then you won't get anything added or updated

schwim

I searched in the table and there are no donation records with undefined or 0 for the user_id(which is the value donator_id is supposed to be picking up), so it seems my problem is not getting rid of the 0(or undefined), but instead donator_id not getting a proper value.... is this line of thought correct?

cretaceous

no I don't think that line of thought is correct!

if your first query is doing this:

$query1="SELECT user_id, mc_gross FROM donation_paypal  ORDER BY user_id ";

$result1=mysql_query($query1) or die (mysql_error());
$numr=mysql_num_rows($result1);
if($numr>0){
    while($row1=mysql_fetch_array($result1)){
        $donator_id=$row1['user_id'];
etc...

then $donator_id MUST be a user_id from the donation_paypal table

so now I have looked again at your INSERT:

 $query3="INSERT donation_totals
                SET total_donated = (
                    SELECT SUM(amount) FROM donation_paypal WHERE user_id=$donator_id)";
            }

BUT you are not inserting the $donator_id!!
(I'd get into habit of writing INSERT INTO instead of plain INSERT)

It should be

 $query3="INSERT INTO donation_totals
                SET user_id= '$donator_id', total_donated = (
                    SELECT SUM(amount) FROM donation_paypal WHERE user_id=$donator_id)";
            }

schwim

Hi there again,

I think we've made progress, but the problem with the function has changed completely:

Where before the page errored out because it tried to insert user_id of 0, the function now processes without any error but the totals table remains empty.

I only changed the INSERT command that you referenced and didn't try to change anything else.

/*function to completely rebuild total donated table.  If the total for the user_id is right, leave it alone, if it's incorrect or missing, update the total.*/

$query1="SELECT user_id, mc_gross FROM donation_paypal  ORDER BY user_id "; 
// no need for user id in brackets and no need for ASC as that is the default 
	$result1=mysql_query($query1) or die (mysql_error()); 
	$numr=mysql_num_rows($result1); 
	if($numr>0){ 
		while($row1=mysql_fetch_array($result1)){ 
			$donator_id=$row1['user_id']; 
			$total_donated=$row1['mc_gross']; 
			echo "<b>. </b>"; 
// no need to put echo value in brackets 
// why not make it useful and do: 
echo "<br>".$donator_id." : ".$total_donated; 


		// I need to check for the existence of the user_id in the totals table.  If it exists, check/replace and if it's not there, insert new entry. 
		 $query2="SELECT user_id, total_donated FROM donation_totals WHERE user_id=$donator_id"; 
		$result2=mysql_query($query2) or die (mysql_error()); 
		$numr=mysql_num_rows($result2); 
		if($numr>0){ 
			// We are updating! Yay! 
			$query3="UPDATE donation_totals 
			SET user_id= '$donator_id', total_donated = ( 
				SELECT SUM(mc_gross) FROM donation_paypal WHERE user_id=$donator_id)"; 
		}else{ 
			// We are creating new record, so still yay! 
			$$query3="INSERT INTO donation_totals
SET user_id= '$donator_id', total_donated = ( 
				SELECT SUM(mc_gross) FROM donation_paypal WHERE user_id=$donator_id)"; 
		} 
//run the sql insert or update 
if($query3) $result3=mysql_query($query3) or die (mysql_error()); 
		} 
	}

I'm not sure where I went wrong, but I can't see any reason why it wouldn't insert the record.

cretaceous

I would debug it step by step - insert a number instead of the sum amount

does your install version of mySql allow the nested subselect?
if not you would need to pull this out as a separte process:

SELECT SUM(mc_gross) FROM donation_paypal WHERE user_id=$donator_id

schwim

Hi there again,

I found my error, I had improperly set query3. Upon running the corrected code, it ran properly for about 20 records, then errored out:

1 : 25.00.
1 : 20.00.
1 : 25.00.
1 : 25.00.
1 : 25.00.
1 : 5.70.
1 : 25.00.
1 : 50.00.
1 : 15.00.
1014 : 135.00.
102 : 5.00.
1029 : 45.00.
1032 : 70.00.
<abridged>
1545 : 120.00.
1565 : 20.00.
1589 : 10.00.
159 : 25.00.
159 : 20.00Duplicate entry '159' for key 1

The code didn't have problems with multiple user_id of 1, but errored when it got a second user_id of 159. I tried removing the index key from user_id, but after running it again, I got a whole new problem(I know, it seems that's just what I do).

I now have 278 records in donation_totals with the same value for both user_id and total_donated.

user_id | total_donated
981 | 260 <~~~ This shows up in the totals table 278 times.

The very last records that the script calculated was user_id of 981, so it seems that it overwrote all previous user_id and totals with the last user_id that it hit.

8522 : 70.00.
8522 : 50.00.
8577 : 30.00.
8584 : 20.00.
86 : 80.00.
93 : 25.00.
957 : 10.00.
968 : 120.00.
969 : 15.00.
981 : 210.00.
981 : 25.00.
981 : 25.00

You can see that user 981 donated 260.

Can you see a reason why it would overwrite all previous data with the last one calculated?

/*function to completely rebuild total donated table.  If the total for the user_id is right, leave it alone, if it's incorrect or missing, update the total.*/

$query1="SELECT user_id, mc_gross FROM donation_paypal  ORDER BY user_id "; 
$result1=mysql_query($query1) or die (mysql_error()); 
$numr=mysql_num_rows($result1); 
if($numr>0){ 
	while($row1=mysql_fetch_array($result1)){ 
		$donator_id=$row1['user_id']; 
		$total_donated=$row1['mc_gross']; 
		echo "<b>. </b>"; 
		echo "<br>".$donator_id." : ".$total_donated; 
		// I need to check for the existence of the user_id in the totals table.  If it exists, check/replace and if it's not there, insert new entry. 
		$query2="SELECT user_id, total_donated FROM donation_totals WHERE user_id=$donator_id"; 
		$result2=mysql_query($query2) or die (mysql_error()); 
		$numr=mysql_num_rows($result2); 
		if($numr>0){ 
			// We are updating! Yay! 
			$query3="UPDATE donation_totals 
			SET user_id= '$donator_id', total_donated = ( 
			SELECT SUM(mc_gross) FROM donation_paypal WHERE user_id=$donator_id)"; 
		}else{ 
			// We are creating new record, so still yay! 
			$query3="INSERT INTO donation_totals
			SET user_id= '$donator_id', total_donated = ( 
			SELECT SUM(mc_gross) FROM donation_paypal WHERE user_id=$donator_id)"; 
		} 
		//run the sql insert or update 
		if($query3) $result3=mysql_query($query3) or die (mysql_error()); 
	} 
}

cretaceous

got it I think
you're update is not specifying a "WHERE" on the update part, but only on the "SUM part
below is kind of how it should be, but I am not expert on nested queries so that might not be correct syntax

$query3="UPDATE donation_totals 
			SET user_id= '$donator_id', total_donated = ( 
			SELECT SUM(mc_gross) FROM donation_paypal WHERE user_id='$donator_id') WHERE user_id='$donator_id'";

schwim

That seems to have worked fantastically, and I can't thank you enough for your help.

I have one other question(I think just one)...

After totaling the users up, I need to assign a rank to them (if $total_donated=1-25, it gets one rank, 26-100 gets another, etc...), so for instance.

if($total_donated >= 1 AND =< 25){
 	$query4="UPDATE user_prefs 
	SET user_rank = 'rank_1' WHERE user_id=$donator_id";
	$result4=mysql_query($query4) or die (mysql_error());
}

My fear is that I will place it somewhere that causes it to assign the rank each time it finds an entry in the donations table, when it only needs to assign the rank after it's totaled everything for that user(it seems less wasteful on resources this way). Where would I place it if I only want it to handle the user's rank after totaling all entries found in the donations table?

Thanks again very much for your time,
json

cretaceous

I should have noticed it sooner I think!

I think you will need to do the rank update at the very end
- maybe BradG or Lars berg could write the sql to do it at the same time as the 'total donated' entries !

to be honest the rank seems a bit redundant as you can calculate it later whenever you get data about the users.. so it doesn't really need to be entered as another column to my mind

I'd do a select on the entire donation_totals table
and chunk through it with a 'while' and then set the rank for each entry as it fits your criteria - make sense?