Help for complex query?

[dekrelo]

Hello,

I make site for sport statistic with Dreamweaver. I have problem only with head-to-head section and need ideas and help.
I have two InnoDB tables:
1. player - info for every player (player_id, player, dob, country....) and
2. matches - results from matches between players (matches_id, player1, score, player2, score2, season.......)
I made two dynamic List Menues and Go buton linked in detailed page with matches between selected player. First I tried to pass with URL parameter and with Form Variable, but no result. Now I'm thinking to make relations between two tables, but it is so complicated. In table 'players' I have name of every player in table 'matches' I have two columns with names of players? I need ideas and help how to make this complex for me query, please?

[Atli]

Hey.

We need to see the code to be able to help. (Not all of it, but the relevant parts.)

In general, you just need a <form> with two <select> boxes - one for each player - populated by fetching the player details from MySQL. Then, once two players are selected from the <select> boxes, the IDs of those players are sent to the action page, which reads them and fetches the record from the database.

[dekrelo]

That is good idea. Thank you!
Is that mean, that I must have two foreign keys in table 'matches' with players_id from table 'players'? Or I can use players names for foreign keys?

[Atli]

In a situation like that the best method would be to use a many-to-many (N:M) relationship.

One of the basic rules of database design is to never put more than one column into a table to hold the same type of value. - For example, in your table you would have to put two columns for players. Those two columns would be identical, except for the name - Now imagine if, in the future, you needed to have more than two players for a match... what would you do then? You would have to modify the table to add more player columns.

That is what a N:M relationship is meant to prevent. You basically pull the player columns out of the match table and create a intermediary table that holds a reference to a single player and a single match. That way you can add as many players as you need to any one match.

This is what a typical N:M relationship would look like:

+---------+     +--------------------+     +--------------+
| players |     | player_match       |     | matches      |
+---------+     +--------------------+     +--------------+
| id (PK) |>--->| player_id (PK, FK) |    <| id (PK)      |
| name    |     | match_id (PK, FK)  |<-/  | description  |
| etc..   |     | player_score       |     | or_something |
+---------+     +--------------------+     +--------------+

To use this effectively, you would need to use joins. (Lots of tutorials on that available via Google.) I won't go into to much detail on that, but here is an example of what those look like. This query would fetch all the players for a given match from the above tables:

SELECT
    m.id,
    m.description,
    p.name AS 'player_name',
    pm.score
FROM matches AS m
LEFT JOIN player_match AS pm
    ON pm.match_id = m.id
LEFT JOIN players AS p
    ON p.id = pm.player_id
WHERE m.id = 1;

This basically "joins" the tables so that we can query them as a single table. Makes it possible to filter and return data from multiple tables in a single query.

[dekrelo]

Perfect! I will try that! Thank you very much!

[dekrelo]

Hi,

Finaly I make query! Your help was great. Now I have other problem. I'm trying to pass 2 parameters from 2 drob down menus (player names) and don't know how to connect thouse 2 parameters with query in detailed page? I have two variables: $pl1 and $pl2 from drob down menus from master page. In detailed page I have folowing SELECT:

SELECT m.name,m.name2,score,score2
FROM matches_db m JOIN players_db a ON m.name=a.name JOIN players_db b ON m.name2=b.name
WHERE m.name IN ($pr1,$pr2) AND m.name2 IN ($pr1,$pr2)

may be somwhere I must have POST or GET to get thouse two variables from master page? Help, please!

[Atli]

Hey.

So the problem is getting the variables from the <select> boxes into the query on the detail page?

This is how something like that usually goes:

<!-- In master page -->
<form action="details.php" method="post">
    <select name="player1">
        <option value="1">First</option>
        <option value="2">Second</option>
    </select>
    <select name="player2">
        <option value="1">First</option>
        <option value="2">Second</option>
    </select>
</form>

<?php
// In details page.
if(isset($_POST['player1'], $_POST['player2']))
{
    $player1 = mysql_real_escape_string($_POST['player1']);
    $player2 = mysql_real_escape_string($_POST['player2']);
 
    $sql = "SELECT  stuff 
            FROM    table
            WHERE   player1='{$player1}'
            AND     player2='{$player2}'";
 
    $result = mysql_query($sql) or trigger_error(mysql_error(), E_USER_ERROR);
 
    // Then display the results.
}
?>

Always remember the mysql_real_escape_string function when using input data in a MySQL query! Arguably one of the - if not "the" - most important thing we learn about PHP. (See SQL Injection for details on that.)