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 (while loop), I need them to be accumulated (sp?) in to win one sole row of data. So in essence, I want all of the possible values added as one complete value. 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 = 1
24 v 1 = 24
10 v 47 v 1 = 1
So 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 - 1
Wrestler 2 (10)
M / W - L
===========
2 / 0 - 2
Wrestler 3 (24)
M / W - L
===========
1 / 1 - 0
Wrestler 4 (47)
M / W - L
===========
1 / 0 - 1
The 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.
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.
Adding this may help somewhat:
[PHP]$shows_query = "SELECT (record01 = '$bios[bio_id]') AS win, (record01 != '$bios[bio_id]') AS loss
FROM shows
WHERE 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 />";[/PHP]