Help for complex query? – PHP

  php

Q(Question):

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?

A(Answer):

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.

A(Answer):

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?

A(Answer):

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.

A(Answer):

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

A(Answer):

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!

A(Answer):

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.)

LEAVE A COMMENT