Help for complex query? – PHP




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?



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.


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?


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:

m.description, AS 'player_name',
FROM matches AS m
LEFT JOIN player_match AS pm
ON pm.match_id =
LEFT JOIN players AS p
ON = pm.player_id
WHERE = 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.


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



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:

FROM matches_db m JOIN players_db a ON JOIN players_db b ON
WHERE 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!



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 name="player2">
<option value="1">First</option>
<option value="2">Second</option>
// 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.)