CANT RETRIEVE SUM USING MYSQL QUERY

[gevensen]

The mysql query is good

It returns a sum in phpmyadmin

but i cant seem to get the sum over to $expense_amount

the $expense_row=mysql_fetch_array($result3); comes up with an empty array?

what am i doing wrong?

 

$query3="SELECT SUM(`split_amount`) FROM `sc_expenses_transactions` WHERE `fund` = '$fund' AND `account` = '$account' AND YEAR(`date_of_split`) = '$year' ";
$result3=mysql_query($query3)or die("error b :".mysql_error());
if($result3){
	$expense_row=mysql_fetch_array($result3);
	$expense_amount=$expense_row[0];
	}

[Andy-H]

Try this.

$query3="SELECT SUM(`split_amount`) AS total FROM `sc_expenses_transactions` WHERE `fund` = '$fund' AND `account` = '$account' AND YEAR(`date_of_split`) = '$year' ";
   $result3=mysql_query($query3)or die("error b :".mysql_error());
   if($result3){
      $expense_row=mysql_fetch_assoc($result3);
      $expense_amount=$expense_row['total'];
      }

[gevensen]

nope gets a error message

Notice: Undefined index: total

[PFMaBiSmAd]

What does adding the following right after the line with the mysql_query() statement show -

 

echo mysql_num_rows($result3);

[gevensen]

shows 1 result i did that already

[PFMaBiSmAd]

Then that is not your whole actual relevant code. You probably have that code inside of a loop and variables are being reuse.

[gevensen]

yes it is but i have renamed them all??

 

 

$query="SELECT * FROM `sc_business_report` ";
$result=mysql_query($query)or die(mysql_error());
while($row=mysql_fetch_assoc($result))
{
$fund=$row['fund'];
$account=$row['account'];
print_r($row); echo "<br />";
echo $fund."-".$account."<br />";
$income_amount=0.00;
$expense_amount=0.00;


$query2="SELECT SUM(`amount`) FROM `sc_income_data` WHERE `fund` = '$fund' AND `account` = '$account'  AND YEAR(`date`) = '$year' ";
echo "INCOME QUERY : ".$query2."<br />";
$result2=mysql_query($query2)or die("error a :".mysql_error());
if($result2){
	$income_row=mysql_fetch_array($result2);
	$income_amount=$income_row[0];
	echo "Income Amount :".$income_amount."<br />";
}


$query3="SELECT SUM(`split_amount`) FROM `sc_expenses_transactions` WHERE `fund` = '$fund' AND `account` = '$account' AND YEAR(`date_of_split`) = '$year' ";
echo "EXPENSE QUERY : ".$query3."<br />";
$result3=mysql_query($query3)or die("error b :".mysql_error());
if($result3){
	$expense_row=mysql_fetch_array($result3);
	$expense_amount=$expense_row[0];

	}


$query4="UPDATE `sc_business_report` SET `income` = '$income_amount' , `expense` = '$expense_amount' WHERE ( `fund` =  '$fund' AND `account` = '$account' ) ";
mysql_query($query4)or die(mysql_error());


}

[gevensen]

i get a result of one return row but it doesnt pass to the array for some reason?

[PFMaBiSmAd]

What are the actual errors, symptoms? Post actual error messages, identify lines where the error is being reported...

 

You haven't exactly proven that your code is not working. The posted code looks like it should work, so best guess at this point is that the actual code being executed is not the code you are looking at or posting and asking us to look at.

[gevensen]

its the actual code.

 

i am displaying results on mys screen to troubleshoot the results

 

It is inside that loop, which must be causing the problem because if i take it outside the loop it works

 

I posted the actual loop and i dont see why it shouldnt work

 

 

 

[gevensen]

and no error messages it just passes an empty value even though when i run the query on phpmyadmin i get an amount

[gevensen]

i rewrote a smaller portion same problem

this is the whole code

in phpmyadmin the query displays a sum

but print_r($row2); shows an empty array

if i use echo $row2[0]; i get null also

 

any takers?

 

mysql_connect($servername, $dbusername, $dbpassword) or die(mysql_error());
$dbname=$_SESSION['db'];
mysql_select_db($dbname) or die("CANNOT SELECT DATABASE - ". mysql_error());
$year=$_SESSION['ytd_year'];
$query="SELECT * FROM sc_business_report ";
$result=mysql_query($query) or die(mysql_error());
if($result){
while($row=mysql_fetch_array($result)){
$fund=$row['fund'];
$account=$row['account'];
$query2="SELECT SUM( split_amount ) FROM sc_expenses_transactions WHERE  fund = '$fund' AND account = '$account'  AND YEAR ( date_of_split ) = '$year'  ";
$result2=mysql_query($query2) or die(mysql_error());
if($result2){
	$row2=mysql_fetch_row($result2)or die(mysql_error());
	print_r($row2);
} // eof result2
mysql_free_result($result2);	
}// eof while

} //eof result

[PFMaBiSmAd]

The or die(mysql_error()); on the mysql_fetch_row() line of code has no meaning because mysql_fetch_row does not set a mysql_error(). The only result you would get from the or die() would be for your code to stop execution with nothing being output by that statement. Remove the or die() -

$row2=mysql_fetch_row($result2)or die(mysql_error());

 

It would sure help if you posted the output you are getting. What does print_r($row2); actually show? What does the following show right after the line where $query2 is being set -

 

var_dump($query2);

 

What does adding the following two lines of code immediately after your first opening <?php tag on the page show -

ini_set("display_errors", "1");
error_reporting(E_ALL); 

 

And since you happen to be using $_SESSION variables (it pays to show us everything you are doing on the page as it can be relevant to problems you are having on a page), what does a phpinfo() statement show for the register_globals setting? And if register_globals are ON, do any of your $_SESSION variables have the same name as any of your program variables.

 

 

[PFMaBiSmAd]

Best guess at this point is that you have some non-printing characters (null, line-feed, tab...) that are part of the $fund, $account, or $year values and they don't match what is in the database but when you copy/paste the printed output and use it in phpmyadmin, the non-printing characters as no longer part of the values. This is actually why I asked for the var_dump() of the $query2 variable above, to see if the length of the printing characters matches the actual length of what is in the variable.

[gevensen]

Example of output for

 

$result2=mysql_query($query2) or die(mysql_error());
if($result2){
	$row2=mysql_fetch_assoc($result2);
	echo $query2."<br />";
	echo "<br />VAR DUMP<br />";
	var_dump($row2);
	echo "<br />VAR DUMP<br />";
	echo $row2['total']."<br />";
} // eof result2

 

Sample of actual output should be 7219.35

 

SELECT SUM( `split_amount` )AS `total` FROM `sc_expenses_transactions` WHERE `fund` = 'General' AND `account` = '2007 Debt ' AND YEAR ( `date_of_split` ) = '2009'

 

VAR DUMP

array(1) { ["total"]=> NULL }

VAR DUMP

[gevensen]

$row2=mysql_fetch_row($result2)or die(mysql_error());

FIXED

 

VAR DUMP

array(1) { ["total"]=> NULL }

VAR DUMP

 

No other error codes

ini_set("display_errors", "1");
error_reporting(E_ALL); 

 

I pulled out the session and changed to to '2009'

 

 

 

The or die(mysql_error()); on the mysql_fetch_row() line of code has no meaning because mysql_fetch_row does not set a mysql_error(). The only result you would get from the or die() would be for your code to stop execution with nothing being output by that statement. Remove the or die() -

$row2=mysql_fetch_row($result2)or die(mysql_error());

 

It would sure help if you posted the output you are getting. What does print_r($row2); actually show? What does the following show right after the line where $query2 is being set -

 

var_dump($query2);

 

What does adding the following two lines of code immediately after your first opening <?php tag on the page show -

ini_set("display_errors", "1");
error_reporting(E_ALL); 

 

And since you happen to be using $_SESSION variables (it pays to show us everything you are doing on the page as it can be relevant to problems you are having on a page), what does a phpinfo() statement show for the register_globals setting? And if register_globals are ON, do any of your $_SESSION variables have the same name as any of your program variables.

[gevensen]

i do appreciate the help i have written a number of reports on this database using the fund and account and it works?

But using sum it doesnt

 

I was thinking maybe a i have a localhost mysql problem but i uploaded it to my server and off wamp and i still get the same results

[gevensen]

ok i figured it out

 

i changed $account=$row['account']; to $account=trim($row['account']);

 

i guess there are newlines, it hasnt effected any other report except the SUM

 

thanks for the help

[PFMaBiSmAd]

You need to -

 

A) Use trim() when the data is inserted into the database to prevent any future problems, and

B) UPDATE the values in your account column using  trim() to remove the white space that is already present.

 

[PFMaBiSmAd]

Just an FYI to  gevensen on anyone else that happens upon this thread -

 

the $expense_row=mysql_fetch_array($result3); comes up with an empty array?

and -

but print_r($row2); shows an empty array

 

No they did not. The array was not empty. It contained an element, but the element contained a null value, which I why I asked more than once for you to provide actual information that you were seeing in front of you. When you post actual information, you can get solutions a lot faster.

[gevensen]

that why i printed my data to the screen i usually catch errors easily that way but it didnt show the additional whitespace

it just showed for example 'account' which looked normal not 'account

' which would look abnormal

but thanks for the heads up you mentioned it early on but i didnt have problems with any other reports for whatever reason

thanks again