[SOLVED] Using a loop instead of writing out a ridiculous amount of code?

[gsaldutti]

Hi all,

I have a football website where you pick FOUR winners each week, but you can only use each team THREE times throughout the year. There are 32 teams in the NFL.

So I started writing code below which works, but is DEFINITELY not the best way to do it because I'm sure it can be simplified much more using a loop of some sort.  I'm hoping someone here can point me in the right direction because I'm still pretty new to PHP.

 

The code below simply grabs the specific teamid from the "picks" table and finds how many times the logged-in person picked that team.  If it's = 3, that team's name shows up in a list of "teams they can't pick anymore." If it's more than 3, there is a warning message that they used them too many times and to contact me.

 

As you can see, there is a block of bulky code for EACH of the 32 teams  (I only included the Eagles and Cowboys below because I'm sure you get the idea).

 

I imagine it's really bad programming to make the code connect to the databsae 32 separate times, so I'm hoping someone can tell me how to contact the database just once, and then do the same stuff below using a loop of some sort.

 

Instead of $getEagles, I know I could just do $getTeams and NOT specify a specific "teamid" in the SQL Statement, but then I don't know how to manipulate the returned set of ALL the teams.

 

Any help would be greatly appreciated.

 

Thanks 

 

<?php

$getEagles = "SELECT teamid
FROM picks
WHERE picks.userid = '{$_SESSION["userid"]}'
AND picks.teamid = '1' ";
$Eagles = mysql_query($getEagles, $connection);
if (!$Eagles) {
die("Database query failed: " . mysql_error());
}

$numEagles = mysql_num_rows($Eagles);

if ($numEagles == 3) {
$teamsdone = "Eagles<br/> ";
} elseif ($numEagles > 3) {
$teamsdone = "<span class='problem'>Eagles (You picked them TOO MANY TIMES.  Contact Gary about this.)<br/></span>";
}
else {
}

$getCowboys = "SELECT teamid
FROM picks
WHERE picks.userid = '{$_SESSION["userid"]}'
AND picks.teamid = '2' ";
$Cowboys = mysql_query($getCowboys, $connection);
if (!$Cowboys) {
die("Database query failed: " . mysql_error());
}

$numCowboys = mysql_num_rows($Cowboys);

if ($numCowboys == 3) {
$teamsdone .= "Cowboys<br/>";
} elseif ($numCowboys > 3) {
$teamsdone .= "<span class='problem'>Cowboys (You picked them TOO MANY TIMES.  Contact Gary about this.)<br/></span>";
}
else {
}


//Then the same code above would be list THIRTY more times for the other 30 teams.   Far from efficient.



if (!$teamsdone) {

echo "<p class='teamsdoneheader'>Teams you CANNOT pick again:</p>";
echo "<p class='teamsdone'> None yet </p>";

} else {
echo "<p class='teamsdoneheader'>Teams you CANNOT pick again:</p>";
echo "<p class='teamsdone'>" . $teamsdone . "</p>";

}

?>

[Mchl]

Try this query

 

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

 

This will (should) return a list of teamIds together with how many times they've been picked by given user.

[gsaldutti]

Ok, awesome, thanks.  Please don't laugh at my inexperience, but how do I actually display the list of returned Teams? I wrote the following code but it just returns "Resource ID 25" (and I can't find any information on what this error means).  There must be more required to display the teams than simply "echo $getTeams;" ...

 

$query = "SELECT teamid,COUNT(*) AS count FROM picks WHERE userid = '{$_SESSION['userid']}' GROUP BY teamid ORDER BY teamid";
$getTeams = mysql_query($query, $connection);
if (!$getTeams) {
die("Database query failed: " . mysql_error());
} else {

echo $getTeams;

[unrelenting]

Ok, awesome, thanks.  Please don't laugh at my inexperience, but how do I actually display the list of returned Teams? I wrote the following code but it just returns "Resource ID 25" (and I can't find any information on what this error means).  There must be more required to display the teams than simply "echo $getTeams;" ...

 

$query = "SELECT teamid,COUNT(*) AS count FROM picks WHERE userid = '{$_SESSION['userid']}' GROUP BY teamid ORDER BY teamid";
$getTeams = mysql_query($query, $connection);
if (!$getTeams) {
die("Database query failed: " . mysql_error());
} else {

echo $getTeams;

 

You need to loop through results. Try a while loop...

 

http://www.tizag.com/mysqlTutorial/mysqlfetcharray.php