Please Help. Another Query

[sladotron]

 

 

 

 

 

 

 

Hi all,

Hopefully someone can help me with this. I am trying to display the tournament name, Date, and corresponding rating change for a player that I specify by a PlayerID.

 

 

 

I have a table called "matches" that contains match stats between two players. It has columns:

------------------------------------------------------------------------------------------------

| Player1ID | Player2ID | Rating1Before | Rating1Change | Rating2Before | Rating2Change |MeetID |

------------------------------------------------------------------------------------------------

MeetID is the ID of the tournament that this match belongs to. I have multiple matches for one tournament

 

I also have a second table called "players" that has player names with the Player's ID and the TeamID that they belong to

---------------------------------

PLayerID | PlayerName | TeamID  |

---------------------------------

 

Third table is called "teams"

-----------------------

| TeamID | TeamName |

-----------------------

 

The last table is called "meets" which holds all of the information for each tournament/meet.

----------------------------------------------

| MeetID | HomeTeamID | AwayTeamID | Date |

----------------------------------------------

 

The table that I am trying to achieve for a player that I specify with PlayerID field is this

I am trying to list all of the tournaments/meets that player has played in and their rating change for each tournament.

-----------------------------------------------------------------------

| HomeTeamID | AwayTeamID | Date | RatingBefore | TotalRatingChange |

-----------------------------------------------------------------------

 

Here is the query that I have. I am passing a value of variable $PlayerID for this query

 

$query_Recordset5 = "SELECT matches.Rating1Before, SUM(matches.Rating1Change) AS Rating1Change, matches.Rating2Before, SUM(matches.Rating2Change) AS Rating2Change, Home.TeamName AS HomeName, Away.TeamName AS AwayName, M1.Date ";

$query_Recordset5 .="FROM matches ";

$query_Recordset5 .="INNER JOIN meets AS M1 ";

$query_Recordset5 .="ON matches.MeetID=M1.MeetID ";

$query_Recordset5 .="INNER JOIN teams as Home ";

$query_Recordset5 .="ON M1.HomeTeamID=Home.TeamID ";

$query_Recordset5 .="INNER JOIN teams as Away ";

$query_Recordset5 .="ON M1.AwayTeamID=Away.TeamID ";

$query_Recordset5 .="WHERE matches.Player1ID=$PlayerID ";

$query_Recordset5 .="GROUP BY matches.Player1ID";

 

This works for Player 1, however it does not work for Player 2.

I believe I need to change the WHERE clause to have two conditions

 

I tried this instead of WHERE and GROUP BY clause, but its not a legal statement, and I am not sure how to run an IF function or statement in the WHERE clause

 

$query_Recordset5 .="IF(matches.Player1ID=$PlayerID,'WHERE matches.Player1ID=$PlayerID GROUP BY matches.Player1ID','WHERE matches.Player2ID=$PlayerID GROUP BY matches.Player2ID');

 

Any ideas on how to accomplish this ?

[artacus]

what an ugly way to write your query (in php)...

 

Its harder for you to write the query because its not normalized. If you are not too far into this project, you may want to work thru a couple tutorials or even a book on db design.

 

Otherwise, its not a great design, but it is workable.

 

What I would do is get the match and the rating change in a sub query (so it doesn't matter if its player1 or 2), er you don't have a match_id, you should have a pk on your match table

 

SELECT 
FROM (
     SELECT m.id, m.meetID
     IF(m.player1ID = '$playerID', m.rating1Before, m.rating2Before) AS ratingBefore,
     IF(m.player1ID = '$playerID', m.rating1Change, m.rating2Change) AS ratingChange,
     IF(m.player1ID = '$playerID', m.player2ID, m.player1ID) AS opponentID
     FROM matches AS m
     WHERE m.player1ID = '$playerID' OR m.player2ID = '$playerID'
) AS sub
JOIN teams and such...

 

[sladotron]

Hi,

Thanks a lot for your help. I'm new to this, so thats why my queries seem not normal. Its just I didn't really know how to write a query with valid syntax in php, so I followed Dreamweaver's syntax that it generates and it generates it that way, where you have Recordset variable and then each line adds onto the string with the .= syntax.

 

Is there a way just to write Recordset once and then write the query ? I am sure there is, I just don't know the valid syntax IN PHP to do it. The way I wrote works, so I just kept following it.

For instance, when I tried writing it your way, I got an error saying that you have an error in your query around the first FROM clause. Anyway here is the final query that gives me exactly what I need.

 

$query_Recordset5  ="SELECT sub.RatingBefore, sub.RatingChange, Home.TeamName AS HomeName, Away.TeamName AS AwayName, M1.Date ";
$query_Recordset5 .="FROM ( SELECT m.MatchID, m.MeetID, "; 
$query_Recordset5 .="IF(m.Player1ID = '$PlayerID', m.Rating1Before, m.Rating2Before) AS RatingBefore, ";
$query_Recordset5 .="IF(m.Player1ID = '$PlayerID', SUM(m.Rating1Change), SUM(m.Rating2Change)) AS RatingChange ";
$query_Recordset5 .="FROM matches AS m ";
$query_Recordset5 .="WHERE m.Player1ID = '$PlayerID' OR m.Player2ID = '$PlayerID' ";
$query_Recordset5 .="GROUP BY m.Player1ID OR m.Player2ID ) AS sub ";
$query_Recordset5 .="INNER JOIN meets AS M1 ";
$query_Recordset5 .="ON sub.MeetID=M1.MeetID ";
$query_Recordset5 .="INNER JOIN teams as Home ";
$query_Recordset5 .="ON M1.HomeTeamID=Home.TeamID ";
$query_Recordset5 .="INNER JOIN teams as Away ";
$query_Recordset5 .="ON M1.AwayTeamID=Away.TeamID";

 

Sorry for being such a newbie at this.....

If you could write the same query in php a "better way", I would gladly take a look, because I don't like the way it looks myself.

Again, thanks for your help. It worked great !

[artacus]

Is there a way just to write Recordset once and then write the query

 

 

//You could either just write it as one big string
$query = "SELECT ...
              FROM ...
              WHERE...
              GROUP BY... "

//or if  you want to split it up just use "." to join the strings
$query = "SELECT... " .
              "FROM ..." .
              "WHERE ... " . 
              "GROUP BY... ";

 

I prefer the 1st one because it contains new line chars so if something is broken, you can echo it out enclosed in pre tags and have a much more readable query.

[fenway]

Also, why do string concatenation for no reason?