Forum Moderators: coopster
What im trying to do is my building condo calculation expensive of how many each apartment has to pay for this i have a table named gastos, this table will be filtered by month and it has to calculate the total amount in the column costo that total will be divided by the amount of apartments in the building to have what each has to pay every month
this is the test code theres a lot of coments of all the test i made from what i have readed on the web, no matter what i have try i have not been able to display the total or i get wrong numbers with the total:
The database table is
id_g int 100 primary
nombre varchar 255
descripcion text
costo decimal 30,2
mes int 4
fecha int 4
<?php
$x_costo = NULL;
$ox_costo = NULL;
$z_costo = NULL;
$ar_x_costo = NULL;
$ari_x_costo = NULL;
$nStartRec = 0;
$nStopRec = 0;
$totalRows_prueba = 0;
$nRecCount = 0;
$nRecActual = 0;
$sKeyMaster = "";
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc()? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string")? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue!= "")? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue!= "")? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue!= "")? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue!= "")? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue!= "")? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$colname_prueba = "-1";
if (isset($_GET['mes'])) {
$colname_prueba = $_GET['mes'];
}
mysql_select_db($database_condominio, $condominio);
$query_prueba = sprintf("SELECT * FROM gastos WHERE mes = %s ORDER BY id_g ASC", GetSQLValueString($colname_prueba, "int"));
$prueba = mysql_query($query_prueba, $condominio) or die(mysql_error());
$row_prueba = mysql_fetch_assoc($prueba);
$totalRows_prueba = mysql_num_rows($prueba);
<?php //require('includes/common/KT_functions.inc.php');?>
<?php
/**********/
//$total = 0; // Declare total variable
//$sql="SELECT costo FROM gastos"; // SQL statement to run
//$result=mysql_query($sql); // Query database
// Loops through query result and adds each value to $total
//while ($row = mysql_fetch_array($result)) {
//$total = $total + $row[costo];
//}
//echo $total; // Prints total to screen
//$resultado = mysql_query($query_probando);
//while($row_probando['costo'] = mysql_fetch_assoc($resultado))
//{
// la suma de las columnas/registros vengan las que vengan
//$suma = array_sum($row_probando['costo']);
//}
/*$row_prueba['costo']->MoveFirst();
$total = 0;
while ($prueba->EOF){
$total = $total + $row_prueba['costo'];
$prueba->MoveNext();
}*/
//while ($prueba->EOF){
//$tot_x_costo = 0; // Initialise total to zero for aggregation
//$x_costo = $row_probando['costo']
//$tot_x_costo += $row_prueba['costo'];
//$prueba->MoveNext();
// Move to the first record
// Set the last record to display
if ($nDisplayRecs <= 0) { // Display all records
$nDisplayRecs = $nTotalRecs;
}
$nStartRec = 1;
SetUpStartRec(); // Set up start record position
//$nStopRec = $nStartRec + $nDisplayRecs - 1;
$nRecCount = $nStartRec - 1;
if (@mysql_num_rows($prueba) > 0) {
@mysql_data_seek($prueba, $nStartRec -1);
}
$tot_x_costo = 0; // Initialise total to zero for aggregation
$nRecActual = 0;
while (($row = @mysql_fetch_array($prueba)) && ($nRecCount < $nStopRec)) {
$nRecCount = $nRecCount + 1;
if ($nRecCount >= $nStartRec) {
$nRecActual++;
$x_costo = $row_prueba['costo'];
$tot_x_costo += $x_costo; // Accumulate Total
}
}
?>
<? $xx = $row_prueba['fecha'];?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<table width="800" border="1">
<tr>
<td> </td>
</tr>
<tr>
<td><?php do {?>
<table width="800" border="1">
<tr>
<td width="200"><?php echo $row_prueba['nombre'];?></td>
<td width="200"><?php echo $row_prueba['descripcion'];?></td>
<td width="108"><?php echo $row_prueba['mes'];?></td>
<td width="101"><?php echo $row_prueba['fecha'];?></td>
<td width="149"><?php echo $row_prueba['costo'];?></td>
<td width="7"> </td>
<td width="9"> </td>
</tr>
</table>
<?php } while ($row_prueba = mysql_fetch_assoc($prueba));?></td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td>total: <?php
$x_costo = $tot_x_costo;
if ($totalRows_prueba > 0) {?>
<? echo $x_costo;?>
<?php }?>
</td>
</tr>
<tr>
<td><? echo $xx;?> </td>
</tr>
</table>
<p> </p>
</body>
</html>
<?php
mysql_free_result($prueba);
?>
any idea on how i can solve this?
$query = "SELECT mes, SUM(costo) AS MonthlyCost FROM gastos GROUP BY mes";
That will directly return costs by month.
$abx_abc = "-1";
if (isset($_GET['mes'])) {
$abx_abc = $_GET['mes'];
}
mysql_select_db($database_condominio, $condominio);
$query_abc = sprintf("SELECT SUM(costo)AS MonthlyCost, mes FROM gastos WHERE gastos.mes = %s GROUP BY mes", GetSQLValueString($abx_abc, "int"));
$abc = mysql_query($query_abc, $condominio) or die(mysql_error());
$row_abc = mysql_fetch_assoc($abc);
$totalRows_abc = mysql_num_rows($abc);
