Page 3 of 3

Re: Get total from database column based on recordID

Posted: Mon Apr 29, 2013 2:51 pm
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>

Re: Get total from database column based on recordID

Posted: Tue Apr 30, 2013 9:48 am
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

Re: Get total from database column based on recordID

Posted: Wed May 01, 2013 4:49 am
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];
		  ?>

Re: Get total from database column based on recordID

Posted: Wed May 01, 2013 8:40 am
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

Re: Get total from database column based on recordID

Posted: Thu May 02, 2013 6:59 am
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;
		  ?>

Re: Get total from database column based on recordID

Posted: Thu May 02, 2013 11:59 am
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)'];
}

Re: Get total from database column based on recordID

Posted: Thu May 02, 2013 2:34 pm
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

Re: Get total from database column based on recordID

Posted: Thu May 02, 2013 2:50 pm
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.

Re: Get total from database column based on recordID

Posted: Thu May 02, 2013 3:05 pm
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

Re: Get total from database column based on recordID

Posted: Thu May 02, 2013 3:24 pm
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.

Re: Get total from database column based on recordID

Posted: Thu May 02, 2013 4:00 pm
by jonnyfortis
ok i will read what i need to know in more depth. thank you both for you advise

Re: Get total from database column based on recordID

Posted: Wed May 08, 2013 6:46 am
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;?>