Page 1 of 3

Get total from database column based on recordID

Posted: Thu Apr 18, 2013 4:53 am
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)'];
		  ?>

Re: Get total from database column based on recordID

Posted: Thu Apr 18, 2013 12:28 pm
by requinix
So add a WHERE condition.

Re: Get total from database column based on recordID

Posted: Thu Apr 18, 2013 2:12 pm
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

Re: Get total from database column based on recordID

Posted: Thu Apr 18, 2013 3:08 pm
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?

Re: Get total from database column based on recordID

Posted: Fri Apr 19, 2013 4:26 am
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

Re: Get total from database column based on recordID

Posted: Fri Apr 19, 2013 5:14 am
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

Re: Get total from database column based on recordID

Posted: Fri Apr 19, 2013 5:43 am
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

Re: Get total from database column based on recordID

Posted: Mon Apr 22, 2013 12:23 pm
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.

Re: Get total from database column based on recordID

Posted: Mon Apr 22, 2013 1:09 pm
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'];
}

Re: Get total from database column based on recordID

Posted: Mon Apr 22, 2013 1:52 pm
by requinix
But you're not actually using it.

Re: Get total from database column based on recordID

Posted: Mon Apr 22, 2013 2:00 pm
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);

Re: Get total from database column based on recordID

Posted: Mon Apr 22, 2013 2:26 pm
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.

Re: Get total from database column based on recordID

Posted: Tue Apr 23, 2013 3:37 am
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

Re: Get total from database column based on recordID

Posted: Tue Apr 23, 2013 3:51 am
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.

Re: Get total from database column based on recordID

Posted: Tue Apr 23, 2013 4:31 am
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?