Page 2 of 3
Re: Get total from database column based on recordID
Posted: Tue Apr 23, 2013 12:40 pm
by requinix
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?
Re: Get total from database column based on recordID
Posted: Wed Apr 24, 2013 8:14 am
by jonnyfortis
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?
i think i am displaying all the parent and/or child rows
Re: Get total from database column based on recordID
Posted: Wed Apr 24, 2013 12:41 pm
by requinix
Then I don't think you want to GROUP BY or SUM() anything.
Re: Get total from database column based on recordID
Posted: Thu Apr 25, 2013 4:40 am
by jonnyfortis
Then I don't think you want to GROUP BY or SUM() anything.
so what should my statement look like would you advise?
Re: Get total from database column based on recordID
Posted: Thu Apr 25, 2013 1:01 pm
by requinix
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
Re: Get total from database column based on recordID
Posted: Fri Apr 26, 2013 4:22 am
by jonnyfortis
I 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
tried that and echoed out
Code: Select all
echo $row_rsSum['utilityTotal'].'<br />';
but nothing was displayed
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'];
the echo is
and it is still giving me just the value of the first utility of that property.
Re: Get total from database column based on recordID
Posted: Fri Apr 26, 2013 12:39 pm
by requinix
That could very well be because you only actually get the first row.
Re: Get total from database column based on recordID
Posted: Fri Apr 26, 2013 12:47 pm
by jonnyfortis
That could very well be because you only actually get the first row.
yes that is only the first row but need all the rows based on the recordID
Re: Get total from database column based on recordID
Posted: Fri Apr 26, 2013 1:08 pm
by requinix
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.
Code: Select all
$row_rsSum = mysql_fetch_assoc($rsSum);
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
$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;
Re: Get total from database column based on recordID
Posted: Mon Apr 29, 2013 4:04 am
by jonnyfortis
i change my SQL to your suggestion
// and display the contents of the row
// }
// then eventually
does this mean something should go before the curly bracket because with the code you showed the echo show nothing..
Re: Get total from database column based on recordID
Posted: Mon Apr 29, 2013 4:50 am
by requinix
You need to substitute code for the comments I put in.
Re: Get total from database column based on recordID
Posted: Mon Apr 29, 2013 5:06 am
by jonnyfortis
You need to substitute code for the comments I put in.
what // and display the contents of the row
// }
needs to echo out?
Re: Get total from database column based on recordID
Posted: Mon Apr 29, 2013 1:35 pm
by requinix
Whatever 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
Posted: Mon Apr 29, 2013 2:11 pm
by jonnyfortis
Whatever you want it to output. You never showed that part so I assumed you already had it written.
the only thing i want show is the $grandTotal
Re: Get total from database column based on recordID
Posted: Mon Apr 29, 2013 2:36 pm
by mikosiko
show the exact and complete code that you have now.