PHP/MySQL problem... I need to add up multiple rows!

[Guest TheGameTB]

I got this wrestling database I'm developing. It's not as straight and narrow as most sport databases are, but this has got me stumped a bit. I am trying to implement their stats in to their specific profiles, but instead of having them naturally split, I need them to be accumulated (sp?) in to win one sole row of data. I'm going to give some rough examples below:profiles (db)=========$bio_id (auto_increment)$bio_id=====Wrestler 1 ($bio_id =1)Wrestler 2 ($bio_id=10)Wrestler 3 ($bio_id=24)Wrestler 4 ($bio_id=47)shows (db)========$segment_id (auto_increment)$record01 ($record01=$bio_id)$bio_id vs. $bio_id = $record01=======================1 v 10 = 124 v 1 = 2410 v 47 v 1 = 1So we have three matches inserted. I can get those showing up fine and dandy and even in the correct profiles. The trick is to have it give me a total of Matches, Wins, and Losses. With the "while" loop, I can get each separate match records displayed (only three would logically show here). When I display the data without the "while" loop, I only get 1 row (the other 2 rows are ignored). What I want are totals like the examples below:Wrestler 1 (1)M / W - L==========3 / 2 - 1Wrestler 2 (10)M / W - L===========2 / 0 - 2Wrestler 3 (24)M / W - L===========1 / 1 - 0Wrestler 4 (47)M / W - L===========1 / 0 - 1The unique (to each profile) match results weren't too bad, because I am using the "while" loop statement to display them, but having all 3 rows (matches) of data be accumulated (sp?) for the stats part is a thinker for me. I tried doing this two ways, what you see here and I also attempted using separated records (record01, record02, record03...) where I designated "1" as win and designated "0" as loss.This problem could be a database structuring problem in MySQL and/or possibly a coding problem in PHP. So note that I can get the data to show in each profile, but either I display using the "while" loop and get 3 rows displaying or I get only the newest row (or whatever row I GROUP BY) displaying only the top most row.It's late here, so let me know if I confused you in anyway or if you need more information as I would be happy to supply it. Thanks in advance for any help and/or advice on this issue.TonyAdding this may help somewhat:

$shows_query = "SELECT (record01 = '$bios[bio_id]') AS win, (record01 != '$bios[bio_id]') AS lossFROM showsWHERE wrestler01 = '$bios[bio_id]' OR wrestler02 = '$bios[bio_id]'OR wrestler03 = '$bios[bio_id]' OR wrestler04 = '$bios[bio_id]'GROUP BY match_id DESC";$shows_query_result = mysql_query($shows_query);$shows = mysql_fetch_array ($shows_query_result);$win = $shows['win'];$loss = $shows['loss'];$total = $win + $loss;	echo "<br />$total - $win - $loss<br /><br />";

[justsomeguy]

I'm a little confused by the query, but this is how I would do it. If you have a table for results with 4 wrestlers, then I would structure it with these fields:id int autonumberwrestler1 intwrestler2 intwreslter3 intwrestler4 intwinner intThe winner field would be the ID of the wrestler that won, which I think you are already doing. So getting the wins and losses would go like this:

$result = mysql_query("SELECT * FROM shows WHERE (wrestler1 = $id OR wrestler2 = $id OR wrestler3 = $id OR wrestler4 = $id) AND winner = $id");$wins = 0;while ($row = mysql_fetch_assoc($result)){  //display each winning match  $wins++;}$result = mysql_query("SELECT * FROM shows WHERE (wrestler1 = $id OR wrestler2 = $id OR wrestler3 = $id OR wrestler4 = $id) AND winner != $id");$losses = 0;while ($row = mysql_fetch_assoc($result)){  //display each winning match  $losses++;}

That would let you display all of the matches, and total up the wins and losses. I'm not sure if that answers your question or not, if not then post some of the code you are having problems with.