Get total from database column based on recordID

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post 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?
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

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

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post 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
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

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

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post 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;
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Get total from database column based on recordID

Post 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..
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

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

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Get total from database column based on recordID

Post by requinix »

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

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Get total from database column based on recordID

Post by mikosiko »

show the exact and complete code that you have now.
Post Reply