how to get the total of a column in php

Report · Apr 17, 2013

i have a php MySQL database that stores individual bills paid, i need to show the full amount of the column that stores the individual payments

what i have so far

$colname_rsProperty = "-1";

if (isset($_GET['recordID'])) {

$colname_rsProperty = $_GET['recordID'];

}

mysql_select_db($database_hostprop, $hostprop);

$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));

$query_limit_rsProperty = sprintf("%s LIMIT %d, %d", $query_rsProperty, $startRow_rsProperty, $maxRows_rsProperty);

$rsProperty = mysql_query($query_limit_rsProperty, $hostprop) or die(mysql_error());

$total = 0;

$total += $row_rsProperty['utilityAmount'];

the column in host_editpropUtil is called utilityAmount

and the individual amounts are echoed out

<?php echo DoFormatCurrency($row_rsProperty['utilityAmount'], 2, ',', '.', '£ '); ?>

and i want to place the total at the end of these

and just need an echo of the total

<?php echo $total ?>

it is just showing the first value in the table

thanks in advance

Report · Apr 17, 2013

>it is just showing the first value in the table

The code you posted shows you only assigning the first value. Are you adding values to the variable within your loop?

Report · Apr 17, 2013

>> Are you adding values to the variable within your loop?

no the above is all i am doing

i did try adding another recordset

$colname_rsSum = "-1";
if (isset($_GET['utilityID'])) {
$colname_rsSum = $_GET['utilityID'];
}
mysql_select_db($database_hostprop, $hostprop);
$query_rsSum = sprintf("SELECT SUM(utilityAmount) FROM hostLFePropUtil WHERE utilityID = %s", GetSQLValueString($colname_rsSum, "int"));
$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);

but the total shows 0.00

$total = 0;
$total += $row_rsSum['utilityAmount'];

Report · Apr 17, 2013

>> Are you adding values to the variable within your loop?

>no the above is all i am doing

Cmon. You can't expect a variable to contain the sum of all rows when you are only assigning it the value from a single row.

i did try adding another recordset

Well, you don't really need another recordset. You just need to sum all of the values in the first. But, what is the rowcount of this new recordset?

Report · Apr 17, 2013

>>Cmon. You can't expect a variable to contain the sum of all rows when you are only assigning it the value from a single row.

im sorry if im being dim but how am i assigning to a single row

$row_rsSum['utilityAmount'];

if i am doing this wrong how should i be doing it?

>> You just need to sum all of the values in the first. how?

>>But, what is the rowcount of this new recordset? again the above code is all i have done. never done this before.

Report · Apr 18, 2013

ok i now have managed to get a TOTAL of all the column but this show everything. I need it based on the recordID i made another recordset and user the recordID from the first recordset

$colname_rsProperty = "-1";

if (isset($_GET['recordID'])) {

$colname_rsProperty = $_GET['recordID'];

}

mysql_select_db($database_hostprop, $hostprop);

$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));

$rsProperty = mysql_query($query_rsProperty, $hostprop) or die(mysql_error());

$row_rsProperty = mysql_fetch_assoc($rsProperty);

$totalRows_rsProperty = mysql_num_rows($rsProperty);

/*

$colname_rsSum = "-1";

if (isset($_GET['recordID'])) {

$colname_rsSum = $_GET['recordID'];

}

*/

mysql_select_db($database_hostprop, $hostprop);

$query_rsSum = sprintf("SELECT SUM(utilityAmount) FROM host_editpropUtil", GetSQLValueString($colname_rsProperty, "text"));

$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());

$row_rsSum = mysql_fetch_assoc($rsSum);

$totalRows_rsSum = mysql_num_rows($rsSum);

but this is still showing the full total of the column

		<?php
		echo $row_rsSum['SUM(utilityAmount)'];
		?>

Report · Apr 18, 2013

i think the problem is there is no filter on the new recordset rsSUM

it needs to be equal to both tables prop_id so i tried the following

mysql_select_db($database_hostprop, $hostprop);

$query_rsSum = sprintf("SELECT * FROM host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id = (SELECT SUM(utilityAmount), FROM host_editpropUtil)", GetSQLValueString($colname_rsProperty, "text"));

$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());

$row_rsSum = mysql_fetch_assoc($rsSum);

$totalRows_rsSum = mysql_num_rows($rsSum);

but i am getting errors on this one

Report · Apr 19, 2013

>im sorry if im being dim but how am i assigning to a single row

OK, you really need to understand this if you are going to work with any programming language and SQL queries.

Let's start here:

How many rows does your query return?

How many rows do you think are represented in the following code?

$row_rsSum['utilityAmount'];

Now do you now see why the code you posted is only getting the value from the first row in the recordset?

if i am doing this wrong how should i be doing it?

You stated that you are outputing the individual amounts. That means you have a loop somewhere that iterates through the recordset. During each iteration, the row values change. You need to add the current value to your variable within this loop.

$total += $row_rsProperty['utilityAmount'];

Does this make sense?

You could also create another recordset to obtain the sum - as you have tried in a later post - but there is no need. You already have all of the information available.

Report · Apr 21, 2013

i have tried so many variation of what i need to acheive and thought i had tried

$total += $row_rsProperty['utilityAmount'];

earlier in the post.

this is what i have tried now

$colname_rsProperty = "-1";

if (isset($_GET['recordID'])) {

$colname_rsProperty = $_GET['recordID'];

}

mysql_select_db($database_hostprop, $hostprop);

$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));

$rsProperty = mysql_query($query_rsProperty, $hostprop) or die(mysql_error());

$row_rsProperty = mysql_fetch_assoc($rsProperty);

$totalRows_rsProperty = mysql_num_rows($rsProperty);

//

mysql_select_db($database_hostprop, $hostprop);//this query is showing all totals from the column host_editpropUtil.utilityAmount

$query_rsSum = sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id", GetSQLValueString($colname_rsProperty, "text"));

$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());

$row_rsSum = mysql_fetch_assoc($rsSum);

$totalRows_rsSum = mysql_num_rows($rsSum);

but show errror

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

but when i add the group

GROUP BY host_editprop.prop_id

it just shows the 1st value in the column.

Should i re add the $total Variable again,

Report · Apr 21, 2013

>have tried so many variation of what i need to acheive and thought i had tried

>$total += $row_rsProperty['utilityAmount'];

>earlier in the post.

Nope. Do you understand what a loop is and how they work?

>but show errror

Yes, that SQL is not legal. And you don't need it. Use the first recordset and total the amount within the loop.

Report · Apr 22, 2013

>>Nope. Do you understand what a loop is and how they work?

not really no. I know i need to understand this i know

>>Yes, that SQL is not legal. And you don't need it. Use the first recordset and total the amount within the loop.

thats what i tried

$colname_rsProperty = "-1";

if (isset($_GET['recordID'])) {

$colname_rsProperty = $_GET['recordID'];

}

mysql_select_db($database_hostprop, $hostprop);

$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));

$rsProperty = mysql_query($query_rsProperty, $hostprop) or die(mysql_error());

$row_rsProperty = mysql_fetch_assoc($rsProperty);

$totalRows_rsProperty = mysql_num_rows($rsProperty);

$total = 0;

$total += $row_rsProperty['utilityAmount'];

<?php

// echo $row_rsSum['SUM(host_editpropUtil.utilityAmount)'];

//while ( $row_rsSum = mysql_fetch_assoc($rsSum) )

// echo $row_rsSum['utilityTotal'].'<br />';

echo $total

?>

this is showing the first record for the property.

Report · Apr 22, 2013

Jonathan Fortis wrote:
i have a php MySQL database that stores individual bills paid, i need to show the full amount of the column that stores the individual payments
what i have so far

Not read all the thread but you want the total of all the individual bills that are stored in a column in your database?

If so just creat a simple query like below replacing colName with the name of your column for the individual bills information and tableName with the table that the information is stored in.

$query = "SELECT SUM(colName) FROM tableName";

$result = mysql_query($query) or die(mysql_error());

Then just echo it out: (replace colName with the name you used in the query above)

<?php

while($row = mysql_fetch_array($result)){

echo $row['SUM(colName)'];

}

?>

Report · Apr 22, 2013

>>Not read all the thread but you want the total of all the individual bills that are stored in a column in your database?

yes but it need to be the total of the individual utility cost and the selected property, not the total of the whole column but the total of the column where the recordID = the selected property

Report · Apr 22, 2013

Is recordID your autoincremented column?

If so, then you are only going to return totals for one record.

I've tried to read and make sense of the thread, so here's my two penny's worth.

If you're trying to pull in a variable, you will need a first recrdset to grab the following:

$yourVariable = $row_your1stRecordset['requiredColumn'];

Then in your second recordset you can apply the variable like so:

"SELECT * FROM table WHERE column = '".$yourVariable."'";

and I would then embed this mysql query within the repeating region of the actual page. I've just done this to enable me to give a total count on the amount of replies to posts within a forum I have built. So, as you look at the list of all threads, you can quickly see the totals for each before actually going into the detail page of the thread. You would probably need to use a GROUP BY and/or SUM on a column that relates to individual customers, but not on your autoincrementing column.

Hopefully I have understood what you are trying to achieve, but if not, then ignore me!

Report · Apr 22, 2013

can i tell you what is what as i didnt think it would have been this complex.

i have a page (property-list-full.php) that has a list of all properties, each property has a prop_id, these are NOT auto increment,

this is the recordset for property-list-full.php

mysql_select_db($database_hostprop, $hostprop);

$query_Recordset1 = "SELECT prop_id, prop_type, prop_add1, town, `full` FROM host_editprop WHERE `full` = 'yes'";

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

and the link

<a href="property-utility-list.php?recordID=<?php echo $row_Recordset1['prop_id']; ?>">Utility Bill List</a>

they are then taken to the

property-utility-list.php, this page shows the property information and utility payments that have been made to that property based on the prop_id

it currently shows all the individual payment made using recordset

$colname_rsProperty = "-1";

if (isset($_GET['recordID'])) {

$colname_rsProperty = $_GET['recordID'];

}

mysql_select_db($database_hostprop, $hostprop);

$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));

$rsProperty = mysql_query($query_rsProperty, $hostprop) or die(mysql_error());

$row_rsProperty = mysql_fetch_assoc($rsProperty);

$totalRows_rsProperty = mysql_num_rows($rsProperty);

i will show the the table below

<tr>

<td bgcolor="#B5EC2B" class="table-text">Utility</td>

<td bgcolor="#B5EC2B" class="table-text">Start Date</td>

<td bgcolor="#B5EC2B" class="table-text">Amount</td>

<td bgcolor="#B5EC2B" class="table-text">Tenants</td>

<td bgcolor="#B5EC2B"><span class="table-text">Delete Utility</span></td>

</tr>

<?php do { ?>

<tr>

<td class="table-text"><a href="delete-utility.php?utilityID=<?php echo $row_rsProperty['utilityID']; ?>">Delete </a></td>

</tr>

<?php } while ($row_rsProperty = mysql_fetch_assoc($rsProperty)); ?>

</table>

what i need to also show is the total of $row_rsProperty['utilityAmount'] for that property

i have tried making a new recordset to handle this but all i can get this to do is either show the total from the whole column "utilityAmount"

or the first amount from the chosen property,

mysql_select_db($database_hostprop, $hostprop);//this query is showing all totals from the column host_editpropUtil.utilityAmount

$query_rsSum = sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id GROUP BY host_editprop.prop_id", GetSQLValueString($colname_rsProperty, "text"));

$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());

$row_rsSum = mysql_fetch_assoc($rsSum);

$totalRows_rsSum = mysql_num_rows($rsSum);

i have tried

adding

$total = 0;

$total += $row_rsProperty['utilityAmount'];

then echo out the total

<?php

echo $total

?>

then this show the amount from the selected property

Report · Apr 23, 2013

What would this give you?

$recordID = $_GET['recordID'];

$query = "SELECT host_editpropUtil.SUM(utilityAmount), host_editprop.prop_id WHERE prop_id = $recordID";

$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))

{

echo $row['SUM(utilityAmount)'];

}

Report · Apr 23, 2013

>>What would this give you?

>>$recordID = $_GET['recordID'];

>>$query = "SELECT host_editpropUtil.SUM(utilityAmount), host_editprop.prop_id WHERE prop_id = $recordID";

>>$result = mysql_query($query);

>>while($row = mysql_fetch_assoc($result))

>> {

>> echo $row['SUM(utilityAmount)'];

>> }

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /usr/users1/domain/public_html/host/admin/property-utility-list.php on line 48

Report · Apr 23, 2013

Change the query to this:

$query = "SELECT SUM(host_editpropUtil.utilityAmount), host_editprop.prop_id WHERE prop_id = $recordID";

and the echo to this:

echo $row['SUM(host_editpropUtil.utilityAmount)'];

What happens now?

Report · Apr 23, 2013

		<?php

$query = "SELECT SUM(host_editpropUtil.utilityAmount), host_editprop.prop_id WHERE prop_id = $recordID";

echo $row['SUM(host_editpropUtil.utilityAmount)'];

//echo $row_rsSum['SUM(host_editpropUtil.utilityAmount)'];

//while ( $row_rsSum = mysql_fetch_assoc($rsSum) )

//echo $row_rsSum['utilityTotal'].'<br />';

//echo $row_rsSum['SUM(utilityAmount)'];

//echo $total

?>

show no value..it is blank

Report · Apr 23, 2013

Do you have this as the while loop?

while($row_rsSum = mysql_fetch_assoc($result))

Report · Apr 23, 2013

>>Do you have this as the while loop?

>>while($row_rsSum = mysql_fetch_assoc($result))

i have this commented out at the moment

Report · May 02, 2013

got it to work

$colname_rsProperty = "-1";

if (isset($_GET['recordID'])) {

$colname_rsProperty = $_GET['recordID'];

}

mysql_select_db($database_hostprop, $hostprop);

$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));

$rsProperty = mysql_query($query_rsProperty, $hostprop) or die(mysql_error());

$row_rsProperty = mysql_fetch_assoc($rsProperty);

$totalRows_rsProperty = mysql_num_rows($rsProperty);

mysql_select_db($database_hostprop, $hostprop);

$query_rsSum = sprintf("SELECT host_editpropUtil.utilityAmount, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id AND host_editprop.prop_id = %s", GetSQLValueString($colname_rsProperty, "text"));

$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());

$totalRows_rsSum = mysql_num_rows($rsSum);

$grandTotal = 0;

// use a loop here

{ while ($row_rsSum = mysql_fetch_assoc($rsSum))

$grandTotal += $row_rsSum['utilityAmount'];

$row['utilityAmount'];// and display the contents of the row

//$row['SUM(utilityAmount)'];// and display the contents of the row

}

how to get the total of a column in php

1 Correct answer