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

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 »

show the exact and complete code that you have now.

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
//}
function DoFormatCurrency($theObject,$NumDigitsAfterDecimal,$DecimalSeparator,$GroupDigits,$CurrencySymbol) { 
	$currencyFormat=$CurrencySymbol.number_format($theObject,$NumDigitsAfterDecimal,$DecimalSeparator,$GroupDigits);
	return ($currencyFormat);
}
<?
	session_start();
	if(!$_SESSION['loggedIn']) // If the user IS NOT logged in, forward them back to the login page
	{
		header("location:Login.html");
	}
?>
in the body

Code: Select all

<table width="940" border="1" align="left" cellpadding="0" cellspacing="0">
              <tr>
                <td bgcolor="#B5EC2B" class="table-text">Utility</td>
                <td bgcolor="#B5EC2B" class="table-text">Date Paid</td>
                <td bgcolor="#B5EC2B" class="table-text">Start Date</td>
                <td bgcolor="#B5EC2B" class="table-text">End Date</td>
                <td bgcolor="#B5EC2B" class="table-text">Amount</td>
                <td bgcolor="#B5EC2B" class="table-text">Tenants</td>
                <td bgcolor="#B5EC2B"><span class="table-text">Delete Utility</span></td>
              </tr>
              <?php do { ?>
                <tr>
                  <td class="table-text"><a href="utility-breakdown.php?recordID=<?php echo $row_rsProperty['utilityID']; ?>"><?php echo $row_rsProperty['utilName']; ?></a></td>
                  <td class="table-text"><?php echo date('D, d/m/Y',strtotime($row_rsProperty['utilityDatePaid'])); ?></td>
                  <td class="table-text"><?php echo date('D, d/m/Y',strtotime($row_rsProperty['utilitySD'])); ?></td>
                  <td class="table-text"><?php echo date('D, d/m/Y',strtotime($row_rsProperty['utilityED'])); ?></td>
                  <td class="table-text"><?php echo DoFormatCurrency($row_rsProperty['utilityAmount'], 2, ',', '.', '£ '); ?></td>
                  <td class="table-text"><?php echo $row_rsProperty['utilityStudAmount']; ?></td>
                  <td class="table-text"><a href="delete-utility.php?utilityID=<?php echo $row_rsProperty['utilityID']; ?>">Delete </a></td>
                </tr><?php } while ($row_rsProperty = mysql_fetch_assoc($rsProperty)); ?>
                <tr>
                  <td class="table-text">&nbsp;</td>
                  <td class="table-text">&nbsp;</td>
                  <td class="table-text">&nbsp;</td>
                  <td class="table-text">&nbsp;</td>
                  <td class="table-text"><?php
		  
		 /* $query = "SELECT host_editpropUtil.SUM(utilityAmount), host_editprop.prop_id WHERE host_editprop.prop_id = $recordID";
$result = mysql_query($query);
 
 
while($row = mysql_fetch_assoc($result))
   {
   echo $row['SUM(utilityAmount)'];
   }*/
//echo $row_rsSum['SUM(host_editpropUtil.utilityAmount)'];
//echo $row_rsSum['utilityTotal'].'<br />'; 
   //echo $total
echo $grandTotal;
		  ?></td>
                  <td class="table-text">&nbsp;</td>
                  <td class="table-text">&nbsp;</td>
                </tr>
                
            </table>
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 »

well.. your code is not showing any of the recommendations that requinix gave to you implemented... in this part of your code

Code: Select all

$grandTotal = 0;

// use a loop here {
  $row_rsSum = mysql_fetch_assoc($rsSum);
  $grandTotal += $row_rsSum['utilityAmount'];
  // and display the contents of the row
//}
which part you didn't understand?

by the end of your code seems that you tried something like that...

Code: Select all

while($row = mysql_fetch_assoc($result))
   {
   echo $row['SUM(utilityAmount)'];
   }*/
so... just look what you tried there and replicate it where requinix suggested
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 »

ok still not getting any closer i feel. I want to just use one recordset which is rsProperty I hope i have followed your instruction but no values are being displayed on the page now.

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);

$grandTotal = 0;

// use a loop here {
  $row_rsProperty = mysql_fetch_assoc($rsProperty);
  $grandTotal += $row_rsProperty['utilityAmount'];
  // and display the contents of the row
//}
the body

Code: Select all

		  <?php
		  
		 /* $query = "SELECT host_editpropUtil.SUM(utilityAmount), host_editprop.prop_id WHERE host_editprop.prop_id = $recordID";
$result = mysql_query($query);
 
 
while($row = mysql_fetch_assoc($result))
   {
   echo $row['SUM(utilityAmount)'];
   }*/
//echo $row_rsSum['SUM(host_editpropUtil.utilityAmount)'];
//echo $row_rsSum['utilityTotal'].'<br />'; 
   //echo $total
while ($row_rsProperty = mysql_fetch_assoc($rsProperty));
echo  $row[$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 »

last try.... I'm going to repeat part of my previous post first:

Code: Select all

$grandTotal = 0;

// use a loop here {
  $row_rsSum = mysql_fetch_assoc($rsSum);
  $grandTotal += $row_rsSum['utilityAmount'];
  // and display the contents of the row
//}

which part you didn't understand?...

.... why in heaven you tried to add the loop at the end of your code instead of what requinix suggested with is // USE A LOOP HERE comment?... moreover the loop that you did try is incomplete hence you should be getting a syntax error and a blank screen probably..... try again
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 »

think i have followed the instructions correctly now

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 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 
{ while ($row_rsSum = mysql_fetch_assoc($rsSum))
  $grandTotal += $row_rsSum['utilityAmount'];
  $row['SUM(utilityAmount)'];// and display the contents of the row
}

Code: Select all

		  <?php
echo $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 »

syntax 101:

Code: Select all

// use a loop here 
{ while ($row_rsSum = mysql_fetch_assoc($rsSum))
  $grandTotal += $row_rsSum['utilityAmount'];
  $row['SUM(utilityAmount)'];// and display the contents of the row
}
..... WRONG!!

Code: Select all

// use a loop here 
while ($row_rsSum = mysql_fetch_assoc($rsSum)) { // proper location of the bracket
  $grandTotal += $row_rsSum['utilityAmount'];
  /// none of your SELECT has a column named like this $row['SUM(utilityAmount)'];
}
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 »

Code: Select all

..... WRONG!!
thats what i thought but it gave me the correct results....

i then changed it to

// use a loop here
{ while ($row_rsSum = mysql_fetch_assoc($rsSum))
$grandTotal += $row_rsSum['utilityAmount'];
$row['utilityAmount'];// and display the contents of the row
}

and that showed the correct results as well
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 »

The fun part is that you both posted the same code, at least in terms of functionality. Both of them will operate the exact same way. However the {while version works only accidentally, and is confusing since using {}s merely to mark a block of code (which is what happened) is quite uncommon.
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 »

jonnyfortis wrote:

Code: Select all

..... WRONG!!
thats what i thought but it gave me the correct results....

i then changed it to

// use a loop here
{ while ($row_rsSum = mysql_fetch_assoc($rsSum))
$grandTotal += $row_rsSum['utilityAmount'];
$row['utilityAmount'];// and display the contents of the row
}

and that showed the correct results as well
but you don't understand why it is working neither what is exactly wrong do you?... good understanding of the right syntax for your sentences is something that you MUST know otherwise you will still doing the same mistakes over and over without even noticing that your code is not good... per example:... do you know what is the purpose of the { and } curly bracets and when to use them?... do you know what is the difference among this sentences?:

Code: Select all

a)  while ($row_rsSum = mysql_fetch_assoc($rsSum))   $grandTotal += $row_rsSum['utilityAmount'];    
b)  while ($row_rsSum = mysql_fetch_assoc($rsSum))   { $grandTotal += $row_rsSum['utilityAmount']; } 
c)  while ($row_rsSum = mysql_fetch_assoc($rsSum))   $grandTotal += $row_rsSum['utilityAmount']; $row['utilityAmount'];
d)  while ($row_rsSum = mysql_fetch_assoc($rsSum))  { $grandTotal += $row_rsSum['utilityAmount'] ; $row['utilityAmount']; }
e)  while ($row_rsSum = mysql_fetch_assoc($rsSum))  { $grandTotal += $row_rsSum['utilityAmount'] ; } $row['utilityAmount'];  and
f)  { while ($row_rsSum = mysql_fetch_assoc($rsSum))   $grandTotal += $row_rsSum['utilityAmount'] ;  $row['utilityAmount']; } 
and moreover... do you know what this sentence alone is doing:

Code: Select all

$row['utilityAmount'];
once you understand the differences or similitude among them then you will figure out what is wrong with your code... even if for now it is giving you the result that you are looking for... so take your time... go and read the manual looking for the proper syntax of the sentences and you will improve for sure... here is the manual for the while sentence... look for the examples:
http://php.net/manual/en/control-structures.while.php
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 »

requinix wrote:The fun part is that you both posted the same code, at least in terms of functionality. Both of them will operate the exact same way. However the {while version works only accidentally, and is confusing since using {}s merely to mark a block of code (which is what happened) is quite uncommon.
I do agree with you requinix that both code do the same in term of functionality, however my WRONG comment was pointing to good coding practice (in the context of the sentence on point) and no accidental functionality... the idea was to make the user aware of the differences and make him understand why his code is working... he doesn't know why clearly.
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 »

ok i will read what i need to know in more depth. thank you both for you advise
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 need to expand on my question , i need to get the total from a variable and not a column in the database.
the variable is made from other equations

this is what i have tried so far.

Code: Select all

<?php do { ?>
<?php 
             // host £6 equation for option 1
			 $utilTotal = $totalPayment;
			 $LessFeeCol2 = $utilTotal - $fee;
			 $securityDeposit = $row_rsTenProp['rental_price'] * 4;
			 $lessSecurityDepsoit = $LessFeeCol2 - $securityDeposit;
			 $weekCon = $row_rsTenProp['weeks'] / 7;
			 $rentLess21 = $row_rsTenProp['rental_price'] - 21;
			 $weekTerm = $rentLess21 * $weekCon;
			 $lessFees = $fee - $fourWeeksSecurityDep - $weekTerm;
			 $utilTotalLessFees = $utilTotal  - $lessFees;
			 $totalB4Divide = $lessSecurityDepsoit - $weekTerm;
			 $sixMultiDura = 6 * $weekCon;
			 $utility = $totalB4Divide - $sixMultiDura;
			?>
 <td class="table-text"><a href="customer-info.php?recordID=<?php echo $row_rsTenProp['userid']; ?>"><?php echo $row_rsTenProp['userid']; ?></a></td>
                <td class="table-text"><?php echo $row_rsTenProp['weeks'] / 7; ?> weeks</td>
                <td class="table-text"><?php echo $row_rsTenProp['payment_option']; ?></td>
                <td class="table-text"><?php echo DoFormatCurrency($row_rsTenProp['rental_price'], 2, ',', '.', '£ '); ?></td>
                <td class="table-text"><?php echo $row_rsTenProp['prop_id']; ?></td>

                <td class="table-text"><?php echo DoFormatCurrency($totalPayment, 2, ',', '.', '£ '); ?></td>
                <td class="table-text"><?php echo DoFormatCurrency($sixMultiDura, 2, ',', '.', '£ '); ?></td>
                <td class="table-text"><?php echo DoFormatCurrency($utility, 2, ',', '.', '£ ', '');  ?></td>
              </tr>
			  <?php 
			  } while ($row_rsTenProp = mysql_fetch_assoc($rsTenProp)); ?>
and i need to multiply the $totalPayment

Code: Select all

<?php 
				
				$totalPaymentTot = 0;
				while
					($row_totalPayment = mysql_fetch_row($totalPayment)){
					$totalPaymentTot += $row_totalPayment;
					$row_totalPayment;
				}
				echo $totalPaymentTot;?>
Post Reply