summing values in a row

[mayreeh]

how do i get total values in a row by grouping into diffrent categories.

here is my code bt its getting oll totals in a row for now..

<?php require_once('connect.php'); ?>

<?php

$query="Select Amountspent from Moneyout";

$result=mysql_query($query);

$total=0;
while($row=mysql_fetch_array($result))
{
$total=$total + $row['Amountspent'];
}

echo "Total is:" . $total;



?>

the colums are, Amountspent,Category,Financialyear and Financialmonth...i want it to group by the Amountspent,Category,Financialyear and Financialmonth..

please help

[chrishea]

For the future, please use code tags when you include code.

This code is trying to calculate the total value in a column (not a row).

If you want to group the results (and provide sub-totals) you will need to use an Order clause in the Select and your While loop will need to check for the end of a grouping (by keeping the prev value for each of the fields) and print a sub total line when a group ends.

[mayreeh]

thanx, bt pliz advise me using an example code especially using while in fetching next row in providing sub totals please.

[Biiim]

<?php
require_once('connect.php');

$query="Select `Amountspent`,`Category`,CONCAT(`Financialyear`,`Financialmonth`) as `jointdate` from Moneyout";
$result=mysql_query($query);
//CONCAT joins 2 fields together, a row with financialyear = 08 and month 10 becomes '0810'. basically is grouping the data by month

$data = Array();//array to hold all the data
$data['total'] = 0;// set total to 0
while($row=mysql_fetch_assoc($result)){
	if(!$data[$row['Category']]){//check if category has already been set, if not start it at 0
		$data[$row['Category']] = 0;
	}
	if(!$data[$row['jointdate']]){//check if date has already been set, if not start it at 0
		$data[$row['jointdate']] = 0;
	}
	$data[$row['Category']] = $data[$row['Category']] + $row['Amountspent'];//add amount spent to this category 
	$data[$row['jointdate']] = $data[$row['jointdate']] + $row['Amountspent']; //add amount spent to this date
	$data['total'] = $data['total'] + $row['Amountspent'];//add amount spent to total
}

//can access the total like before like this
echo "Total is:" . $data['total']."<br/><br/>\r\n";
//can access a certain value if you know the category/joint date name
echo "Category Test came to:" . $data['Test']."<br/><br/>\r\n";

echo '<table>';
foreach($data as $key=>$value){//loop through all categories/dates and the total(all values in data)
	echo "<tr><td>{$key}</td><td>{$value}</td></tr>
";//new line so the html stays readable!
}
echo '</table>';
?>

[ddymacek]

you can simply try to change your query

$query="Select sum(Amountspent) from Moneyout";
//or if by cateegory
$query="Select sum(Amountspent) from Moneyout where category='x'";

[mayreeh]

thenx biilim, thou am trying to get rid of a certain error in my code invain, using yur code am getting this error, help

Notice: Undefined index: Petty Cash in D:\wamp\www\insurance\Moneyoutreport.php on line 22

Notice: Undefined index: 2010January in D:\wamp\www\insurance\Moneyoutreport.php on line 28

Notice: Undefined index: Credit Expenses in D:\wamp\www\insurance\Moneyoutreport.php on line 22
Total is:2938

Category Test came to:2938

total 2938
Petty Cash 2538
2010January 2938
Credit Expenses 400

[mayreeh]

ddy,...ave woked with that to no avail...

[Biiim]

Hi mayreeh,

The code is running and showing you the proper values, that is just a notice/soft error i'd take a guess it is coming from these 2 lines

if(!$data[$row['Category']]){//check if category has already been set, if not start it at 0
		$data[$row['Category']] = 0;
	}
	if(!$data[$row['jointdate']]){//check if date has already been set, if not start it at 0
		$data[$row['jointdate']] = 0;
	}

you could try adding in a @ to the if statement to tell it to suppress messages, it is saying that the variable doesn't exist (which is what we want to check and make it exist!)

Try either of these 2, i'm not sure if the first one will work

#1

if(@!$data[$row['Category']]){//check if category has already been set, if not start it at 0
		$data[$row['Category']] = 0;
	}
	if(@!$data[$row['jointdate']]){//check if date has already been set, if not start it at 0
		$data[$row['jointdate']] = 0;
	}

#2

if(@isset($data[$row['Category']]) === false){//check if category has already been set, if not start it at 0
		$data[$row['Category']] = 0;
	}
	if(@isset($data[$row['jointdate']]) === false){//check if date has already been set, if not start it at 0
		$data[$row['jointdate']] = 0;
	}

[mayreeh]

waooh....
Biliim,

Thenx alot...yu such a genius...its working now......

thanks alot..