Add values for total in each table.

phplost

Hi all, and thanks for any help and/or suggestions you can offer.

I have been hacking this script for months now as a bit of an ongoing school project and to learn how it works. To date I had never tried to create more than one table from my results. I will admit to finding it difficult dealing with multiple tables.

The only thing I cant figure out is where to put $total +=$value;
It seems that regardess of where I put it I get a continual total of $value instead of the $total just being related to each table in question.

I have marked with /*=== where I thought something should go, but.....

Any help appreciated.

<?php

}
$build_table = "SELECT sports.sport AS sport, athlete.name AS name,portfolio.aid AS this_aid,portfolio.quantity AS quantity
FROM sports,athlete,portfolio
WHERE portfolio.aid=athlete.aid AND athlete.sid=sports.sportid AND portfolio.mid=$thismid ORDER BY this_aid";

$build_result = mysql_query($build_table) or die(mysql_error());

$hollow='Total';
$sport = '';
$name_title='Athlete';
$quantity_title='Quantity';
$avg_title='Points';
$value_title='Value';
$total=0;
$value=0;

for ($i = 1; $build_row=mysql_fetch_array($build_result); ++$i)
{
if($sport != $build_row['sport'])
{
if($i > 1)
{
echo "<tr>\n";
echo "<td colspan=3 align=right>". $hollow ."</td>\n";
echo "<td>" .$total. "</td>\n";
echo "</tr>\n\n";
echo "</table>\n\n";
}
echo "<p>";
echo "<table border=1 WIDTH=350>\n";
echo "<tr>\n";
echo "<td colspan=4 align=center bgcolor=#00FFFF>". $build_row['sport']."</td>";
echo "<tr>\n";
echo "<td>" . $name_title. "</td>\n";
echo "<td>" . $quantity_title . "</td>\n";
echo "<td>" . $avg_title . "</td>\n";
echo "<td>" . $value_title . "</td>\n";
echo "</tr>\n\n";
echo "</tr>\n\n";
}
echo "<tr>\n";
echo "<td>" . $build_row['name'] . "</td>\n";
$quantity=$build_row['quantity'];
echo "<td>" . $quantity . "</td>\n";
$this_aid=$build_row['this_aid'];
$base_points="SELECT MAX(points) AS base_points FROM base WHERE aid=$this_aid";

$base_result=mysql_query($base_points,$link);

if(!$base_result)
{
echo "you screwed up base_result";
}
else
{
WHILE($base_row = mysql_fetch_array($base_result))
{
$base_points=$base_row['base_points'];

$praise="SELECT MIN(points) AS praise_points FROM praise WHERE aid=$this_aid";
$praise_result=mysql_query($praise,$link);
if(!$praise_result)
{
echo "you screwed up praise_result";
}
else
{
WHILE($praise_row = mysql_fetch_array($praise_result))
{

$praise_points=$praise_row['praise_points'];

$sub=$praise_points-$base_points;
$half_sub=$sub/2;
$avg_points=$half_sub+$base_points;
$value=$avg_points$quantity;
/=============== I believe I need something here ($total +=$value😉 that doesnt work, gives running total for all tables ====================*/
}
echo "<td>" . $avg_points . "</td>\n";
echo "<td>" . $value . "</td>\n";

}

}
}
echo "</tr>\n\n";

$sport =$build_row['sport'];

}

echo "<tr>\n";
echo "<td colspan=3 align=right>". $hollow ."</td>\n";
echo "<td>" .$total. "</td>\n";
echo "</tr>\n\n";

echo "</table>\n\n";
}

Roger_Ramjet

If you post a long script, please use the php tags to make it more legible: [ p h p ] (remove spaces) at the beginning and [ / p h p ] (remove spaces) at the end. Also, and if you are at school they should have taught you this, try using comments in your code to document it.

<?php 

} 
$build_table = "SELECT sports.sport AS sport, athlete.name AS name,portfolio.aid AS this_aid,portfolio.quantity AS quantity 
FROM sports,athlete,portfolio 
WHERE portfolio.aid=athlete.aid AND athlete.sid=sports.sportid AND portfolio.mid=$thismid ORDER BY this_aid"; 

$build_result = mysql_query($build_table) or die(mysql_error()); 

$hollow='Total'; 
$sport = ''; 
$name_title='Athlete'; 
$quantity_title='Quantity'; 
$avg_title='Points'; 
$value_title='Value'; 
$total=0; 
$value=0; 

for ($i = 1; $build_row=mysql_fetch_array($build_result); ++$i) 
{ 
if($sport != $build_row['sport']) 
{ 
if($i > 1) 
{ 
echo "<tr>\n"; 
echo "<td colspan=3 align=right>". $hollow ."</td>\n"; 
echo "<td>" .$total. "</td>\n"; 
echo "</tr>\n\n"; 
echo "</table>\n\n"; 
} 
echo "<p>"; 
echo "<table border=1 WIDTH=350>\n"; 
echo "<tr>\n"; 
echo "<td colspan=4 align=center bgcolor=#00FFFF>". $build_row['sport']."</td>"; 
echo "<tr>\n"; 
echo "<td>" . $name_title. "</td>\n"; 
echo "<td>" . $quantity_title . "</td>\n"; 
echo "<td>" . $avg_title . "</td>\n"; 
echo "<td>" . $value_title . "</td>\n"; 
echo "</tr>\n\n"; 
echo "</tr>\n\n"; 
} 
echo "<tr>\n"; 
echo "<td>" . $build_row['name'] . "</td>\n"; 
$quantity=$build_row['quantity']; 
echo "<td>" . $quantity . "</td>\n"; 
$this_aid=$build_row['this_aid']; 
$base_points="SELECT MAX(points) AS base_points FROM base WHERE aid=$this_aid"; 

$base_result=mysql_query($base_points,$link); 

if(!$base_result) 
{ 
echo "you screwed up base_result"; 
} 
else 
{ 
WHILE($base_row = mysql_fetch_array($base_result)) 
{ 
$base_points=$base_row['base_points']; 

$praise="SELECT MIN(points) AS praise_points FROM praise WHERE aid=$this_aid"; 
$praise_result=mysql_query($praise,$link); 
if(!$praise_result) 
{ 
echo "you screwed up praise_result"; 
} 
else 
{ 
WHILE($praise_row = mysql_fetch_array($praise_result)) 
{ 

$praise_points=$praise_row['praise_points']; 

$sub=$praise_points-$base_points; 
$half_sub=$sub/2; 
$avg_points=$half_sub+$base_points; 
$value=$avg_points*$quantity; 
/*=============== I believe I need something here ($total +=$value that doesnt work, gives running total for all tables ====================*/ 
} 
echo "<td>" . $avg_points . "</td>\n"; 
echo "<td>" . $value . "</td>\n"; 

} 

} 
} 
echo "</tr>\n\n"; 

$sport =$build_row['sport']; 

} 

echo "<tr>\n"; 
echo "<td colspan=3 align=right>". $hollow ."</td>\n"; 
echo "<td>" .$total. "</td>\n"; 
echo "</tr>\n\n"; 

echo "</table>\n\n"; 
} 

?>

chyan

to get your total result

$total=$total+$value;

but in the beginning declare first
$total=0;

phplost

Mr Ramjet, I'm truly sorry I overlooked the php tags. I'm also sorry if my post was misleading. I am not at school, in fact I am just a few months off of 50. I am helping a couple of young boys (my son and his friend) with a project we put together after they started showing an interest in Photoshop, Fireworks, Dreamweaver etc, they learn none of this at school at this point. Their project may seem stupid, but for their age I personally think it a great idea and it may very well help them get the classes they want next year when they move on to High School, but we only have a little under 4 weeks to finish and my side of the project is now holding them up.

If you could offer anything constructive beyond php tags I would appreciate it as these boys are now sitting around on one of their very few weekends they have left, waiting for me.

I'm sorry if the script looks a little strange. It is a converted script from a learning project that I was working on called "Bricks R Us". I was originally working on this project with a kind gentleman called "Vincent". Sadly he never got beyond the db design and I tried to finish it myself.

I tried this:

$total=$total+$value;

but in the beginning declare first
$total=0;

$total=0; was always at the top of the script.
The result is that I am just getting the last value (row) from each table as the total.

I have never worked with multiple tables before so I'm not claiming that any of my script is correct.

phplost

Thankyou chyan !

Turns out that I needed $total=0; in 2 places in my script for it to work.

Sincerely appreciate your answer and time.