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

Code: Select all

<?php
   echo $grandTotal;
?>
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.