Page 1 of 1
sum / array_sum
Posted: Sun Oct 03, 2010 6:23 pm
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)
}
}
Re: sum / array_sum
Posted: Sun Oct 03, 2010 8:11 pm
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'];
Re: sum / array_sum
Posted: Sun Oct 03, 2010 8:47 pm
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.
Re: sum / array_sum
Posted: Sun Oct 03, 2010 8:52 pm
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'];
Re: sum / array_sum
Posted: Sun Oct 03, 2010 9:03 pm
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.
Re: sum / array_sum
Posted: Sun Oct 03, 2010 9:19 pm
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'";
Re: sum / array_sum
Posted: Sun Oct 03, 2010 9:28 pm
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.
Re: sum / array_sum
Posted: Sun Oct 03, 2010 9:40 pm
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;
Re: sum / array_sum
Posted: Sun Oct 03, 2010 9:43 pm
by diseman
I'm gonna look at your code more closely when finished posting this, but
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.
Re: sum / array_sum
Posted: Sun Oct 03, 2010 9:45 pm
by John Cartwright
diseman wrote:I'm gonna look at your code more closely when finished posting this, but
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.
Re: sum / array_sum
Posted: Sun Oct 03, 2010 10:01 pm
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...
Re: sum / array_sum
Posted: Sun Oct 03, 2010 10:21 pm
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...
Re: sum / array_sum
Posted: Sun Oct 03, 2010 11:34 pm
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.
