[SOLVED] Problem querying mysql

[padams]

I'm trying to filter information from my database, displaying results based on what a user selects. They choose the team on a previous page, and this is sent to this page via GET. The query involves COUNT, JOIN, GROUP BY and ORDER BY, and works fine until I add the WHERE part. The tries table has a column called teamID so I thought I would be able to simply add the WHERE part in and it would filter out only those records where the teamID matches the one selected by the user. Unfortunately something isn't working!

 

if (isset($_GET['team'])) {

$team = $_GET['team'];

} else {

$team = 1;

}

 

$tries = "SELECT tries.playerID, players.playerFirstName, players.playerLastName, COUNT(tries.playerID) as triesscored FROM tries JOIN players ON tries.playerID = players.playerID GROUP BY tries.playerID WHERE tries.teamID = '".$team."' ORDER BY triesscored DESC";

$tries_query = mysql_query($tries) or die(mysql_error());

$rsTries = mysql_fetch_assoc($tries_query);

[ShoeLace1291]

You could just try

 

WHERE tries.teamID = '$team' ORDER BY triesscored DESC";

[padams]

Tried that and still no luck. I've even tried working in phpmyadmin and replacing the variable with an integer:

i.e. WHERE tries.teamID = '1'

but it still gives me an error.

[padams]

Sorted it out at last. The WHERE part needs to appear before the GROUP BY.

 

$tries = "SELECT tries.playerID, players.playerFirstName, players.playerLastName, COUNT(tries.playerID) as triesscored FROM tries JOIN players ON tries.playerID = players.playerID WHERE tries.teamID = '".$team."' GROUP BY tries.playerID ORDER BY triesscored DESC";

[MadTechie]

Can you post the error,

 

you may also  try

 

$tries = "
SELECT tries.playerID, players.playerFirstName, players.playerLastName, COUNT(tries.playerID) as triesscored 
FROM tries 
JOIN players ON tries.playerID = players.playerID 
WHERE tries.teamID = '".$team."' 
GROUP BY tries.playerID 
ORDER BY triesscored DESC";

 

OR

 

$tries = "
SELECT tries.playerID, players.playerFirstName, players.playerLastName, COUNT(tries.playerID) as triesscored, players.playerID 
FROM tries, players 
WHERE tries.playerID = players.playerID  AND tries.teamID = '".$team."' 
GROUP BY tries.playerID 
ORDER BY triesscored DESC";

 

EDIT: D'oh too slow!