Get total from database column based on recordID

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

jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Get total from database column based on recordID

Post by jonnyfortis »

I need to get the total of a column in my PHP MySQL based on the recordID

i can get a total of the full column but that's not what i need

this is my query

Code: Select all

$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 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);
this is what i am echoing out

Code: Select all

		  <?php
		  echo $row_rsSum['SUM(utilityAmount)'];
		  ?>
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

So add a WHERE condition.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post by jonnyfortis »

So add a WHERE condition.
yes i know i need to add a WHERE host_editpropUtil.prop_id = host_editprop.prop_id

but dont know where it should go
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

Uh, not quite? The only table in the query is host_editpropUtil. There's no host_editprop. The condition should be--

You know what? Let me backtrack. What is this total that you're getting? How is it used?
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post by jonnyfortis »

Uh, not quite? The only table in the query is host_editpropUtil. There's no host_editprop.
yes i know i need to add host_editprop to the statement aswell.
What is this total that you're getting? How is it used?
i have modified the statements as suggested

Code: Select all

$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 is the working showing all totals
$query_rsSum = sprintf("SELECT SUM(utilityAmount) AS utilityTotal 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);
and echo out

<?php
echo $row_rsSum['utilityTotal'];
?>

and am getting the TOTAL of all the values of the column
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post by jonnyfortis »

i have tried this

Code: Select all

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 am getting the following errors

Code: Select all

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
so i take it i have done this wrong
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post by jonnyfortis »

i have added the GROUP BY BUT AM GETTING UNUSUAL RESULTS..

Code: Select all

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"));

GROUP BY host_editprop.prop_id show the total for the first property no matter what property is selected
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

And where in that query is the part that you restrict the results to only "what property is selected"? You're running it against everything.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post by jonnyfortis »

And where in that query is the part that you restrict the results to only "what property is selected"? You're running it against everything.
$colname_rsProperty = "-1";
if (isset($_GET['recordID'])) {
$colname_rsProperty = $_GET['recordID'];
}
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

But you're not actually using it.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post by jonnyfortis »

But you're not actually using it.
im using it where i have commented are i?

Code: Select all

mysql_select_db($database_hostprop, $hostprop);
$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"));///using it here arent I ?
$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

You're trying to, but your query

Code: Select all

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
doesn't reference it.

Remember how your other query earlier has

Code: Select all

WHERE host_editprop.prop_id = %s
You need something like that, with the %s, in this query too.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post by jonnyfortis »

using

Code: Select all

$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 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 AND host_editprop.prop_id = %s", GetSQLValueString($colname_rsProperty, "text"));///using it here arent I ?
$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);
i am still getting error

Code: Select all

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

Like the message says, you can't do a SUM() on one field and then regularly select other fields unless you do a GROUP BY in the query.

I would say "so add one" but I don't think the SUM is entirely appropriate anyways. You'd have to group by host_editprop and host_editpropUtil, but that's all the tables in the query and your SUM() will only sum one row at a time. So either what you're trying to do in the query is wrong or the SUM doesn't belong.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post by jonnyfortis »

Like the message says, you can't do a SUM() on one field and then regularly select other fields unless you do a GROUP BY in the query.

I would say "so add one" but I don't think the SUM is entirely appropriate anyways. You'd have to group by host_editprop and host_editpropUtil, but that's all the tables in the query and your SUM() will only sum one row at a time. So either what you're trying to do in the query is wrong or the SUM doesn't belong.

so would you suggest removing the sum?
Post Reply