I am trying to join two tables so that I can grab the information and order them by the members points. Here is the query I am trying to use:
Code:
$members_info = mysqli_query($connection, "
SELECT
team_member,
stage,
points
FROM team_member_scores
RIGHT JOIN team_members
ON team_members.team_id = team_member_scores.team_id
WHERE game_id = '".$game_id_info."'
AND team_member_scores.team_id = '".$team_id."'
GROUP BY team_member
ORDER BY points DESC
LIMIT 5")
or die(mysqli_error($connection));
This query pulls out all the team members correctly, but instead of grouping them and then ordering them by their points, it seem to just sum the points for all the members and lists that same summed number for every member. I need to pull out the info from both tables because I need to order them in a specific way, and not all team members will show up in the team_member_scores table if they didn't participate in the last game.
I would like to pull every match from the team members table that has the team id, regardless if they are in the team_member_scores table. I can get it to pull out the right information only from the team_members table. I can run two queries, but then I wont' get the sort order I am hoping for.
Anyway, if you need more info, please let me know. Any help and suggestions are greatly appreciated.
What POSSIBLE reason do you have for using GROUP BY in that query?
That's the culprit.
KILL IT.
Any other DB wouldn't allow you to use it like that, but MySQL not only allows it but makes it have some very weird side effects.
***********
Incidentally, your use of RIGHT JOIN there is incorrect. Or, rather, because of the rest of the query your RIGHT JOIN is silently being converted into an INNER JOIN.
You can *NOT* use any condition on the DEPENDENT table in an outer join in the WHERE clause. Such conditions *MUST* be made in the ON clause. (Sole exceptions: tests for NULL or NOT NULL.)
So:
Code:
SELECT team_member, stage, points
FROM team_member_scores
RIGHT JOIN team_members
ON team_members.team_id = team_member_scores.team_id
AND team_member_scores.team_id = '".$team_id."'
WHERE game_id = '".$game_id_info."'
ORDER BY points DESC LIMIT 5
Though I would bet a latte that you do not need or want the apostrophes around '$team_id' or '$game_id_info'. You should never put apostrophes around NUMBERS if you are comparing with or assigning to numeric fields.
May I *strongly* suggest that before you use a query in your PHP code you first check it out using some DB query tool? MySQLWorkbench, perhaps. Or even ugly MySQLPHPAdmin or whatever.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Thanks again Old Pedant, I learn something new each time I post on this forum, and for that I am very grateful.
The only issue is that it shows the same amount of points for every team member pulled from the table. In other words, it doesn't seem to assign the right points to the right member when pulling them from the table, if that makes sense.
The username is being used as the foreign key in the team_member_scores table (I didn't design the team_members table, but I can redesign it if you think it would be better.)
The username is being used as the foreign key in the team_member_scores table
Ummm...no, it is not. At least not in your query.
And that's surely the problem.
Foreign keys are *NOT* automatically used in queries. Ever. They *MUST* be explicitly specified in the JOIN or they will be ignored. A foreign key DECLARATION in the table is only useful for preserving data integrity, not for queries.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
The real problem here is that the team_id field in the team_member_scores table is extraneous. It was leading you down the garden path!
WHERE, though, did the field member_name come from? In what you just showed me, there is no such field? Now you seem to be using username instead? I'll go with that, but...
May I try completely rewriting the query, then?
Code:
$sql = "
SELECT M.username, M.stage, S.points
FROM team_members AS M
LEFT JOIN team_member_scores AS S ON M.username = S.username
WHERE M.team_id = $team_id AND M.game_id = $game_id_info
ORDER BY points DESC LIMIT 5";
(You don't need to use string concatenation with the period; PHP allows you to drop PHP variables into the middle of a quoted string so long as your use "..." instead of '...' for the quotes.)
Now, that will *NOT* get you the SUM of points for each team_member. That will just show you who scored the most points a SINGLE GAME.
Since you have limited the query to just one game_id that's probably okay.
Variations on that:
Code:
$sql = "
SELECT M.username, M.stage, SUM(S.points) AS totalPoints
FROM team_members AS M
LEFT JOIN team_member_scores AS S ON M.username = S.username
WHERE M.team_id = $team_id
GROUP BY M.username, M.stage
ORDER BY points DESC LIMIT 5";
And that will get you the total points scored by all members of a given team in all games. (Note that I omitted that AND M.game_id = ... here. Note also that both fields are needed in the GROUP BY, as shown.)
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Last edited by Old Pedant; 03-08-2013 at 06:39 AM..
And almost forgot to say: That will show all team members, even if they have no scores at all in the team_member_scores table. Your query would have, too, except for the one bogus condition in the WHERE clause and if the JOIN had been done correctly.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
The real problem here is that the team_id field in the team_member_scores table is extraneous. It was leading you down the garden path!
WHERE, though, did the field member_name come from? In what you just showed me, there is no such field? Now you seem to be using username instead? I'll go with that, but...
May I try completely rewriting the query, then?
Code:
$sql = "
SELECT M.username, M.stage, S.points
FROM team_members AS M
LEFT JOIN team_member_scores AS S ON M.username = S.username
WHERE M.team_id = $team_id AND M.game_id = $game_id_info
ORDER BY points DESC LIMIT 5";
(You don't need to use string concatenation with the period; PHP allows you to drop PHP variables into the middle of a quoted string so long as your use "..." instead of '...' for the quotes.)
Now, that will *NOT* get you the SUM of points for each team_member. That will just show you who scored the most points a SINGLE GAME.
Since you have limited the query to just one game_id that's probably okay.
Variations on that:
Code:
$sql = "
SELECT M.username, M.stage, SUM(S.points) AS totalPoints
FROM team_members AS M
LEFT JOIN team_member_scores AS S ON M.username = S.username
WHERE M.team_id = $team_id
GROUP BY M.username, M.stage
ORDER BY points DESC LIMIT 5";
And that will get you the total points scored by all members of a given team in all games. (Note that I omitted that [icode]AND M.game_id = ...[/code] here. Note also that both fields are needed in the GROUP BY, as shown.)
Thanks again! Yeah, I changed it from member_name to username just to show that they were referring to the same info, but forgot to mention that much needed fact when I posted. Sorry about that.
Before you post, read our: 
