One query rather than MANY..

[galvin]

Don't worry about the actual names of things in the code below (everything works).  But in the code below, I query the same table three different times for each of three users.  I'm just concerned with condensing the code. I know it would be ridiculous to query the same table in the database 33 times to get this info for each user.

 

You'll see that the only thing that changes with each new query is the "userid" (1,2, 3, etc).  Assuming there is enough information here, can someone tell me the proper MySQL query so that I can query this just ONCE?

 

 

$getbyes = "SELECT teamid, userid
FROM picks
WHERE picks.teamid = '33' 
AND picks.userid = '1'";
$get_bye_user_1 = mysql_query($getbyes, $connection);
if (!$get_bye_user_1) {
die("Database query failed: " . mysql_error());
}
$numbyes = mysql_num_rows($get_bye_user_1);
$byesleft_user_1 = 4 - $numbyes;



$getbyes = "SELECT teamid, userid
FROM picks
WHERE picks.teamid = '33' 
AND picks.userid = '2'";
$get_bye_user_2 = mysql_query($getbyes, $connection);
if (!$get_bye_user_2) {
die("Database query failed: " . mysql_error());
}
$numbyes = mysql_num_rows($get_bye_user_2);
$byesleft_user_2 = 4 - $numbyes;



$getbyes = "SELECT teamid, userid
FROM picks
WHERE picks.teamid = '33' 
AND picks.userid = '3'";
$get_bye_user_3 = mysql_query($getbyes, $connection);
if (!$get_bye_user_3) {
die("Database query failed: " . mysql_error());
}
$numbyes = mysql_num_rows($get_bye_user_3);
$byesleft_user_3 = 4 - $numbyes;

[Maq]

This is NOT tested, but should work... 

 

$getbyes = "SELECT COUNT(teamid), userid FROM picks WHERE teamid = '33' GROUP BY userid";
$get_bye_user = mysql_query($getbyes, $connection) or die(mysql_error();
while($row = mysql_fetch_array($get_bye_user)){
   echo "There are ". $row['COUNT(teamid)'] ." for userid: ". $row['userid'] . "
";

}

 

You don't need to specify tables (i.e. picks.teamid) because you're only dealing with a single table.  Hope this is what you're aiming at!

[galvin]

That does work to tell me how many there are for each user, so thank you.  But using the info that is pulled back from the table using that query, I need to make it automatically create a variable for each user, essentially like...

 

$get_bye_user_X = #

 

I thought the following might work in the "While" loop...

 

$get_bye_user_{$row['userid']} = $row['COUNT(teamid)'];

 

...In other words, I'd like it to create...

 

$get_bye_user_1 = 2

$get_bye_user_2 = 4

$get_bye_user_3 = 2

 

..and so on through 33 users. With the point being that I want to display each number somewhere else on the page using the variable $get_bye_user_X, and I need access to all 33.

 

I'm sure that makes no sense

 

[xtopolis]

wait, misread