Query involving 2 variables

[padams]

I've got a query that works when I do it directly in phpmyadmin but doesn't work on my site.

 

There are two variables sent from a previous page and are integrated into the WHERE part of the query. It worked fine when there was only the teamID ($team), but as soon as I added the season ($season) as well, using a WHERE...AND... syntax, it gave me the error "Unknown column 'tries.trySeason' in 'where clause'" - but there is a trySeason field in the table!

 

When I echo the query, it appears as "SELECT tries.playerID, players.playerFirstName, players.playerLastName, COUNT(tries.playerID) as triesscored FROM tries JOIN players ON tries.playerID = players.playerID WHERE tries.tryTeam = '1' AND tries.trySeason = '2002' GROUP BY tries.playerID ORDER BY triesscored DESC", and if I paste this into phpmyadmin it works fine, just not on the site.

 

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

$season = $_GET['season'];

} else {

$season = 2002;

}

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

$team = $_GET['teamID'];

} 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 WHERE tries.tryTeam = '".$team."' AND tries.trySeason = '".$season."' GROUP BY tries.playerID ORDER BY triesscored DESC";

[haaglin]

are you executing te query?

 

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

[padams]

Yes. Full code is below:

 

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

$season = $_GET['season'];

} else {

$season = 2002;

}

 

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

$team = $_GET['teamID'];

} 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 WHERE tries.tryTeam = '".$team."' AND tries.trySeason = '".$season."' GROUP BY tries.playerID ORDER BY triesscored DESC";

echo $tries;

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

$rsTries = mysql_fetch_assoc($tries_query);

[MadTechie]

only problem i see if the line

ORDER BY triesscored DESC

i assume should be

ORDER BY tries.scored DESC

try that

[padams]

triesscored was an alias, created by COUNT(tries.playerID) as triesscored

 

I was able to successfully ORDER BY triesscored when there was only one WHERE condition, but as soon as I've added the AND tries.trySeason = '".$season."' I get the error.

 

tries.trySeason is a column in the table.

 

When I echo the query and then copy it into phpmyadmin and run it there it works fine.

[MadTechie]

check that trySeason doesn't have any spaces at the start or end, when you run it from PMA, it will probably add backticks, you could copy the whole thing and add the variables again.. just an idea!

[padams]

Would the fact that the information being sent from the previous page via GET is an integer affect how I insert the variables into the query?

 

I removed the quote marks around the variable and it seems to be working.

 

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

[trq]

Would the fact that the information being sent from the previous page via GET is an integer

 

Anything sent via $_GET or $_POST is a string, wether it represent a number or not.

[padams]

So why would removing the quote marks around the variable make a difference? It worked fine with the quote marks in phpmyadmin, but for whatever reason it wouldn't work when sent from a webpage.