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
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?