467,911 Members | 1,380 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,911 developers. It's quick & easy.

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

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]
Mar 18 '07 #1
  • viewed: 1389
Share:

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

23 posts views Thread by phpfrizzle | last post: by
7 posts views Thread by Gary | last post: by
reply views Thread by Lenz Grimmer | last post: by
reply views Thread by Mike Chirico | last post: by
9 posts views Thread by elyob | last post: by
9 posts views Thread by Dejan | last post: by
1 post views Thread by mpar612 | last post: by
Atli
6 posts views Thread by Atli | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.