[SOLVED] A MySQL query to only bring back things that are in a table THREE times...

[galvin]

I have a table called "picks" with submitted football picks.  In that table, "TeamID" is the field I'm concerned with.  The Eagles, for example, have a TeamID of "1", the Cowboys are "2", the "Giants are 3" and so on.

 

I would like to do a MySQL query that brings back ONLY the TeamIDs that have been used (i.e. picked) exactly 3 times by the current user.  So if the current user picked the Eagles three times, I would want "1" to come back in the result set.  If they were only picked once or twice, I wouldn't want "1" to be returned

 

I was messing with the following but to no avail...

 

$query = "SELECT picks.teamid,
COUNT(*) AS count, 
FROM picks
WHERE userid = '{$_SESSION['userid']}' 
AND count =3"

 

But that just tells me that "count" is an unknown column in WHERE clause, so I'm obviously not doing this correctly.  Any ideas?

 

[Mchl]

This should work

 

$query = "SELECT picks.teamid,
COUNT(*) AS `count`,
FROM picks
WHERE userid = '{$_SESSION['userid']}'
HAVING `count` = 3"

[galvin]

Thanks, I tried that and it keeps saying...

 

Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM picks WHERE userid = '1' HAVING count = 3' at line 3

 

Any thoughts?

[Mchl]

There's unnecessary comma before FROM

[galvin]

Thanks for noticing that.  It works now, but is not bringing back any results.  However,  I know that I have 8 TEAMIDs that have been used exactly three times. So there should be 8 TEAMIDs in the results array.

 

If you have any other ideas, let me know.

 

Thanks,

Greg

[PFMaBiSmAd]

Post an example of your data and what the expected results should be.

 

That query will give a count of all the rows that have userid = '{$_SESSION['userid']}' and only return a result if the count is 3.

[galvin]

Oh I'm misunderstanding then, sorry.  Here goes...I have a table called "picks" and there are only four columns...

 

-userid

-teamid  (a number between 1 and 32 b/c there are 32 total teams)

-week (a number between 1 and 17 b/c there are 17 weeks)

-result (either a "w" for Win or a "l" for loss or an "o" if the game is not settled yet)

 

So in that table, let's say there is the following for me (my userid is 1)...

 

 

                      teamid    userid    week    result

      -  1  1  1  w

- 32 1 1 l

- 20 1 1 w

- 23 1 1 w

- 16 1 2 w

- 9 1 2 w

- 13 1 2 l

- 33 1 2 w

- 3 1 3 w

- 19 1 3 w

- 20 1 3 l

- 12 1 3 w

- 27 1 4 w

- 11 1 4 w

- 23 1 4 w

- 10 1 4 w

- 11 1 5 w

- 2 1 5 w

- 27 1 5 l

- 7 1 5 l

- 4 1 6 l

- 6 1 6 w

- 17 1 6 w

- 10 1 6 w

- 9 1 7 w

- 25 1 7 w

- 3 1 7 w

- 18 1 7 l

- 21 1 8 w

- 17 1 8 w

- 1 1 8 w

- 14 1 8 l

- 8 1 9 w

- 27 1 9 l

- 1 1 9 w

- 33 1 9 w

- 32 1 10 w

- 16 1 10 w

- 17 1 10 w

- 18 1 10 w

- 28 1 11 w

- 3 1 11 w

- 11 1 11 w

- 18 1 11 w

- 23 1 12 w

- 2 1 12 w

- 29 1 12 l

- 26 1 12 l

- 26 1 13 w

- 2 1 13 w

- 19 1 13 o

- 21 1 13 o

 

 

So in that first column (teamid) there are some numbers that are listed exactly THREE times and those are the only ones that I want.

I just want an array that contains the teamids that exist THREE times.

 

Let me know what you think.

[PFMaBiSmAd]

You need a GROUP BY teamid -

 

$query = "SELECT picks.teamid,
COUNT(*) AS `count`
FROM picks
WHERE userid = '{$_SESSION['userid']}'
GROUP BY teamid
HAVING `count` = 3"

[galvin]

It's giving results now but only giving...

 

Array ( [0] => 1 [teamid] => 1 [1] => 3 [count] => 3 )

 

So that is telling me teamid "1" has been used three times, but it's not telling me about the seven other teamids that have been used three times.

 

Below is the code I'm using to print that results Array.  If I'm doing something wrong that jumps out at you, please let me know...

 

<?php
/////testing to get teams that have been used 3 times alresady

$query = "SELECT picks.teamid,
COUNT(*) AS `count`
FROM picks
WHERE userid = '{$_SESSION['userid']}'
GROUP BY teamid
HAVING `count` = 3";

$getThreeTeams = mysql_query($query, $connection);
if (!$getThreeTeams) {
die("Database query failed: " . mysql_error());
} else {

$threeresults = mysql_fetch_array($getThreeTeams);

print_r($threeresults);

}
?>

[Mchl]

 

<?php
/////testing to get teams that have been used 3 times alresady

$query = "SELECT picks.teamid,
COUNT(*) AS `count`
FROM picks
WHERE userid = '{$_SESSION['userid']}'
GROUP BY teamid
HAVING `count` = 3";

$getThreeTeams = mysql_query($query, $connection);
if (!$getThreeTeams) {
die("Database query failed: " . mysql_error());
} else {

         while($threeresults = mysql_fetch_array($getThreeTeams)) {
        print_r($threeresults);
}
}
?>

 

[galvin]

That did it. Thanks SO MUCH to all of you!!!!