sum / array_sum

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

sum / array_sum

Post by diseman »

Hi All,

Having problems adding the total of one column. Being a noob, I think I'm not fully understanding something and that's why it's not working for me. My query results in two returns.

They are:

10 $200
10 $150

I want to add the dollar amounts (referral_actual_fee). I read somewhere that I would need to use array_sum, but I can't get it to work. I finally learned how to print the array, so I could see it, but that didn't do me any good either. Here is the array for you in case it matters:

Array ( [0] => 03/17/10 [referral_pay_date] => 03/17/10 [1] => 200 [referral_actual_fee] => 200 )
Array ( [0] => 09/10/10 [referral_pay_date] => 09/10/10 [1] => 150 [referral_actual_fee] => 150 )

Finally, I left out the part where I was trying to use array_sum 'cause nothing is working. If anyone can plug it in for me, so I can see how to do this I would be grateful.

Here's my code:

Code: Select all

$requestor = 'Squashers';

$query = "SELECT referral_pay_date, referral_actual_fee FROM users WHERE affiliate = '".$requestor."' " ;

$result = mysql_query($query);

while($row = mysql_fetch_array($result))

	{

    if ( substr(''.$row['referral_pay_date'].'', -2 ) == 10 )	{	 	
		
		echo substr(''.$row['referral_pay_date'].'', -2)  ;
		echo "$".$row['referral_actual_fee']. "<br> \n"; // THIS IS THE COLUMN I'M TRYING TO ADD (SUM)
	} 	
}

User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: sum / array_sum

Post by Jonah Bron »

This ought to work.

Code: Select all

$result = mysql_query('SELECT SUM(referral_actual_fee) as fee_sum FROM users');
$result = mysql_fetch_assoc($result);
echo $result['fee_sum'];
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: sum / array_sum

Post by diseman »

Thank you for the reply/help. I'm a little confused by your code.

It appears to sum the total column. In my original code, I had WHERE in the query to only pull certain records. Also, I have an IF statement in my code that has to be maintained. So, how do I keep my code and only use yours to sum the one column that was returned to me based on my query? The result I'm looking for based on my code should have only returned $350; not $750 like yours did.

Looking forward to your reply.... and thanks again.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: sum / array_sum

Post by Jonah Bron »

Oops, missed that part. I just read your question, I didn't look at the code.

Code: Select all

$result = mysql_query('SELECT SUM(referral_actual_fee) as fee_sum FROM users WHERE affiliate = "' . mysql_real_escape_string($requestor) . '"');
$result = mysql_fetch_assoc($result);
echo $result['fee_sum'];
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: sum / array_sum

Post by diseman »

Still not going to work properly because your code is not accounting for the IF statement and returns the sum of all $requestor even though I only want the sum from dollar amounts IF the year is = to 10; that's what the IF statement is looking for.

I'm really hoping you got an answer for that one : ) feel like I"m close with your help and then I'm done.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: sum / array_sum

Post by John Cartwright »

So you only want to query rows in 2010?

Code: Select all

$result = mysql_query("SELECT SUM(referral_actual_fee) as fee_sum FROM users WHERE affiliate = '" . mysql_real_escape_string($requestor) . "' AND YEAR(referral_pay_date) = '2010'";
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: sum / array_sum

Post by diseman »

unfortunately, my date is not in the db as '2010.' It's saved as 09/10/10. So, you can see why I was doing the substr with IF statement; to pull the last to digits from the date, which equals '10' in the test. If $requestor and and date is equal to 10 then I want to sum those results.

The code I submitted from the start is fine. I simply can't figure out how to add/sum the total of the actual_referral_fee that is returned based on my WHERE and IF statements.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: sum / array_sum

Post by John Cartwright »

Code: Select all

*snip* ... AND referral_pay_date LIKE '%10'
But to answer your question, you would need to loop the result set, and iteratively add the array elements.

I.e.,

Code: Select all

$total = 0;
foreach ( ... ) {
   $total += $your_total;
}

echo 'Sum is '. $total; 
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: sum / array_sum

Post by diseman »

I'm gonna look at your code more closely when finished posting this, but

Code: Select all

AND referral_pay_date LIKE '%10'   


will not work because what if the date is 09/10/09? Then your LIKE statement will take it even though the '10' was the day and not the year. That's why I'm using substr to remove everything from the date except the last two characters, which is the year.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: sum / array_sum

Post by John Cartwright »

diseman wrote:I'm gonna look at your code more closely when finished posting this, but

Code: Select all

AND referral_pay_date LIKE '%10'   


will not work because what if the date is 09/10/09? Then your LIKE statement will take it even though the '10' was the day and not the year. That's why I'm using substr to remove everything from the date except the last two characters, which is the year.
No it won't. Notice the placement of the wildcard.

You generally want to perform as much of the logic in SQL btw, which is why were pretty adament on getting rid of that nasty if() statement.
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: sum / array_sum

Post by diseman »

Ok, that's a good answer. I didn't realize that about the %10. That's actually what I was trying to do just now; get the IF statement out and get it all in the query.

I am however getting a little confused and this is going over my head a bit. I'm also getting a error with how I'm putting it together.

Any chance you can put it all together for me, so I can see it as one ?

I'm a little burned out on this today after 8 hours on this one problem...

thanks for any help...
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: sum / array_sum

Post by diseman »

OK, I got it to work. Thank you to everyone.

However, let me save my REAL thank you's until after I'm out of the woods. I finally got the result I"m looking for, but now I have to tweak some things and I have a feeling I'm going to be asking for help.

I'll post back soon...
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: sum / array_sum

Post by diseman »

OK, I was able to get it all working based on your comments. Thanks very much to both of you for helping. Not sure I would have gotten this one any time soon. I feel like I can finally go to bed now. Thank you VERY MUCH guys. :)
Post Reply