Viewing data from 6 tables in MySQL

Question

Im am trying to make a "tournament" application, and get stuck on viewing data from 6 different tables!

I have 6 tabels that I can put data in at the same time, but I can't view it together! And have tried to search on the internet for hours, but I just can't get it...

Any way. It is an 4 team tournament with wins, draws, lose and point. And I have 6 tables for this:

Tournament name - ID, name
Teams - ID, team1, team2, team3, team4
Wins - ID, team1w, team2w, team3w, team4w
Draws ID, team1d, team2d, team3d, team4d
Looses - ID, team1l, team2l, team3l, team4l
Points - ID, team1p, team2p, team3p, team4p

Thats whats in the Tables...

This is my input, it works fine..

<?php
                if(isset($_POST['submit'])) {
                    $cupname = $_POST['cupname'];
                    $team1 = $_POST['team1'];
                    $team2 = $_POST['team2'];
                    $team3 = $_POST['team3'];
                    $team4 = $_POST['team4'];

                    $zero = "0";

                    $result = mysql_query("INSERT INTO cupname (name) VALUES ('$cupname')");
                    $result = mysql_query("INSERT INTO teams (team1, team2, team3, team4) VALUES ('$team1', '$team2', '$team3', '$team4')");
                    $result = mysql_query("INSERT INTO wins (team1w, team2w, team3w, team4w) VALUES ($zero, $zero, $zero, $zero)");
                    $result = mysql_query("INSERT INTO draws (team1d, team2d, team3d, team4d) VALUES ($zero, $zero, $zero, $zero)");
                    $result = mysql_query("INSERT INTO looses (team1l, team2l, team3l, team4l) VALUES ($zero, $zero, $zero, $zero)");
                    $result = mysql_query("INSERT INTO points (team1p, team2p, team3p, team4p) VALUES ($zero, $zero, $zero, $zero)");
                echo "<h1>Turnering og lag lagt til!</h1>";
                }

                ?>

That's the start out of making a "league/tournament". And all the data get's in the tables.

And I got this code from an tutorial I used to make a simple on-site editing for my webpage. And though mabey I could use the same output code, but on that site it's only news from a single table.

I have read some about UNION, LEFT JOIN, FULL JOIN etc.... But I didn't get it.

Here is my "output" code:

<?php
                $result = mysql_query("SELECT * FROM cupname ORDER BY id DESC");
                $result = mysql_query("SELECT * FROM teams ORDER BY id DESC");
                $result = mysql_query("SELECT * FROM wins ORDER BY id DESC");
                $result = mysql_query("SELECT * FROM draws ORDER BY id DESC");
                $result = mysql_query("SELECT * FROM looses ORDER BY id DESC");
                $result = mysql_query("SELECT * FROM points ORDER BY id DESC");
                    while($row = mysql_fetch_array($result)) {
                        $i=$i + 1;
                        echo "<table>";
                        echo "<tr>";
                        echo $row['name'];
                        echo "</tr>";
                        echo "<tr><td><b>Lag</b></td>
                        <td><b>Seiere</b></td>
                        <td><b>Uavgjort</b></td>
                        <td><b>Tap</b></td>
                        <td><b>Poeng</b></td>
                        </tr><tr><td>";
                        echo $row['team1'];
                        echo "</td><td>";
                        echo $row['team1w']; 
                        echo "</td><td>"; 
                        echo  $row['team1d']; 
                        echo "</td><td>"; 
                        echo  $row['team1l']; 
                        echo "</td><td>"; 
                        echo  $row['team1p']; 
                        echo "</td></tr><tr><td>"; 
                        echo  $row['team2']; 
                        echo  "</td><td>"; 
                        echo  $row['team2w']; 
                        echo "</td><td>"; 
                        echo  $row['team2d']; 
                        echo "</td><td>"; 
                        echo  $row['team2l']; 
                        echo "</td><td>"; 
                        echo  $row['team2p']; 
                        echo "</td></tr><tr><td>"; 
                        echo  $row['team3']; 
                        echo  "</td><td>"; 
                        echo  $row['team3w']; 
                        echo "</td><td>"; 
                        echo  $row['team3d']; 
                        echo "</td><td>"; 
                        echo  $row['team3l']; 
                        echo "</td><td>";
                        echo  $row['team3p']; 
                        echo "</td></tr><tr><td>"; 
                        echo  $row['team4']; 
                        echo  "</td><td>"; 
                        echo  $row['team4w']; 
                        echo "</td><td>"; 
                        echo  $row['team4d']; 
                        echo "</td><td>"; 
                        echo  $row['team4l']; 
                        echo "</td><td>"; 
                        echo  $row['team1p']; 
                        echo "</td></tr></table><br /><hr /><br />";
                    }
                ?>

Anyone have any idea of what I shall do, or not do ?

Thanks for any answers!

EDIT: I just get an output of empty tables with this! Only the points is outputted, the 0's.

You really should look into learning how to join tables in a single query. I wrote a rather lengthy [question and answer](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) to help this exact sort of question - I think you would really benefit from reading it. — Fluffeh, Sep 28 '12 at 11:53
That's more than *awesome* @Fluffeh! I'll fave it right now to have the link to it, and give it further to others "in need". — Havelock, Sep 28 '12 at 11:56
And besides that redesign the database from scratch. The design is flawed. As example see http://en.wikipedia.org/wiki/Database_normalization. What do you do if you ant to expand your tournament to 8 players, what it there are 10 Million players? I know won't happen but it's to make a point. — beginner_, Sep 28 '12 at 11:58
And just to add, the OP might need to redesign its DB model ;-) — Havelock, Sep 28 '12 at 11:58
@Havelock Yeah, that was the whole point of writing it - I came across way too many *what's wrong with this join query* type questions that I was providing a few lines of query to, but wanted to give an in-depth explanation as well as providing some tips and tricks that I have picked up along the way. I have popped one bounty on it already to garnish more answers, and think I will do so again in the future :) — Fluffeh, Sep 28 '12 at 11:59
I see your point @beginner_ but if I expand to 8 players, I just add some rows to the tables. It's only the output that stops me here — kongebra, Sep 28 '12 at 11:59
Have been reading the Q&A pages now, but I can't put it together to get the output I need :S — kongebra, Sep 28 '12 at 12:16
Once again, that's great @Fluffeh! But sometimes I feel most of these question emerge, because of poor DB design :-( And a Q&A here on SO wouldn't be enough to explain it all :-/ — Havelock, Sep 28 '12 at 12:44
I think you have messed up the database. Redo the database part — FirmView, Sep 28 '12 at 13:19
Hmm, I just though of it, think I can make one big actually :P — kongebra, Sep 28 '12 at 13:33
Havelock is right. Mostly if you have to do very weird and complicated joins it tells you your design is flawed. Your lucky, you can change it. In most cases you would have to live with it. — beginner_, Sep 29 '12 at 10:53

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

You need to join the tables. From your question, it seems that you already have the relationship section of the tables sorted (though I am not totally sure that I follow it all that well), so it is really a question of what srot of joins to use:

Inner Joins - This will only return records where the data matches in all tables:

select
    tourn.id,
    tourn.name
from
    tournament
        join teams team
            on tourn.id=team.id
        join wins win
            on tourn.id=win.id

and so on (I am assuming that tournament.ID is the column that the others relate to)

Now, you can change this to an outer join, which will return rows from your first table whether or not there are matching rows in the others:

select
    tourn.id,
    tourn.name
from
    tournament
        right outer join teams team
            on tourn.id=team.id
        right outer join wins win
            on tourn.id=win.id

I am not totally sure that I can explain much here in this answer better in any way that I explained it in the Q&A that I wrote before though. Does this answer make sense?

edited May 23 '17 at 11:48

Community

1
1

answered Sep 28 '12 at 12:27

Fluffeh

33,228
16
67
80

Ehh, I'm gonna try it. But I can try to explain better! All ID's is matching one tournament. Like if ID is 1 inn all it will be like this; Champions League (cupname), FC Barcelona (0Win0Draw0Lose0Points), Man U (0W0D0L0P), Man C (0W0D0L0P), Rosenborg BK (0W0D0L0P)... So all the names is ID=1, and all 0's are ID=1, so after I can update wins, draws, looses and points on the ID=1. I need too output all that data in one .. And view it. Did that make any sence?
– kongebra Sep 28 '12 at 12:42
@Fluffeh do you think the structure of the database is in right format? – FirmView Sep 28 '12 at 13:20
I putted everything in one table now.. So I going to try it now! but thanks for the help everyone :D – kongebra Sep 29 '12 at 00:42

Viewing data from 6 tables in MySQL

1 Answers1