Get total from database column based on recordID
Moderator: General Moderators
Re: Get total from database column based on recordID
I might. I don't know your application, what it does, how it works, or really anything about it besides the code you've posted.
Here's the question to answer: does this query need to show information from a "parent" table and include a total of the values in a "child" table column? Or are you displaying all the parent and/or child rows?
Here's the question to answer: does this query need to show information from a "parent" table and include a total of the values in a "child" table column? Or are you displaying all the parent and/or child rows?
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Get total from database column based on recordID
i think i am displaying all the parent and/or child rowsI might. I don't know your application, what it does, how it works, or really anything about it besides the code you've posted.
Here's the question to answer: does this query need to show information from a "parent" table and include a total of the values in a "child" table column? Or are you displaying all the parent and/or child rows?
Re: Get total from database column based on recordID
Then I don't think you want to GROUP BY or SUM() anything.
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Get total from database column based on recordID
so what should my statement look like would you advise?Then I don't think you want to GROUP BY or SUM() anything.
Re: Get total from database column based on recordID
I don't know. All I can do is guess, like
Code: Select all
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-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Get total from database column based on recordID
tried that and echoed outI don't know. All I can do is guess, like
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
Code: Select all
echo $row_rsSum['utilityTotal'].'<br />'; i have now tried just multiplying the echoed result of total of each utility from that property so have done the following
Code: Select all
$colname_rsProperty = "-1";
if (isset($_GET['recordID'])) {
$colname_rsProperty = $_GET['recordID'];
}
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());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);
$grandTotal = 0;
$grandTotal += $row_rsSum['utilityAmount'];Code: Select all
<?php
echo $grandTotal;
?>Re: Get total from database column based on recordID
That could very well be because you only actually get the first row.
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Get total from database column based on recordID
yes that is only the first row but need all the rows based on the recordIDThat could very well be because you only actually get the first row.
Re: Get total from database column based on recordID
Your query already takes care of the "based on the recordID". The query does return more than one row (I assume). The problem is your code only gets one of them.
That's all. If you want more than one then you'll need a loop somewhere to actually get more than one.
Code: Select all
$row_rsSum = mysql_fetch_assoc($rsSum);Code: Select all
$colname_rsProperty = "-1";
if (isset($_GET['recordID'])) {
$colname_rsProperty = $_GET['recordID'];
}
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 {
$row_rsSum = mysql_fetch_assoc($rsSum);
$grandTotal += $row_rsSum['utilityAmount'];
// and display the contents of the row
// }
// then eventually
echo $grandTotal;-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Get total from database column based on recordID
i change my SQL to your suggestion
does this mean something should go before the curly bracket because with the code you showed the echo show nothing..// and display the contents of the row
// }
// then eventually
Re: Get total from database column based on recordID
You need to substitute code for the comments I put in.
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Get total from database column based on recordID
what // and display the contents of the rowYou need to substitute code for the comments I put in.
// }
needs to echo out?
Re: Get total from database column based on recordID
Whatever you want it to output. You never showed that part so I assumed you already had it written.
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Get total from database column based on recordID
the only thing i want show is the $grandTotalWhatever you want it to output. You never showed that part so I assumed you already had it written.
Re: Get total from database column based on recordID
show the exact and complete code that you have now.