Dynamic Form Processing

airpirate007 · November 29, 2011

So I know a little about PHP but I am no expert by any means. But I have a project that I am working on for a fantasy football league and need some help. My users pick players from a list and then their selections are put into a database. So more than one user is likely to pick the same player. Then I need to score the players based off their games for the week. So I have code that gets the Distinct PlayerID and creates a form to update the player score (see code below), but I have no idea how to process the form. It's a little more complicated then the forms I've used before because the MySQL query would need to UPDATE all the rows for each individual PlayerID. Am I making any sense?

Anyway, here is the code. If anyone has suggestions on how to process this form or a better way of doing it then please let me know.

<?

print '<form id="form1" name="form1" method="post" action="update_player.php">';

// Connecting, selecting database

$link = mysql_connect('localhost','user','pass');

if (!$link) {

die('Could not connect: ' . mysql_error());

}

//Query

$query=mysql_query("select DISTINCT(PlayerID), PlayerName, Team From fantasy4.temp ORDER BY Team;") or die ('Could not connect: ' . mysql_error());

print'

<td width=50 align=center><b>Player ID </b></td>

<td width=200 align=center><b>Player Name</b></td>

<td width=50 align=center><b>Score</b></td>

';

while($row=mysql_fetch_array($query)){

if($color == 1)

{

print '<tr bgcolor=#dDdDdD>

<td align=center> ' . $row['PlayerID'] . ' </td>

<td align=center> ' . $row['PlayerName'] . ' </td>

</tr>';

$color=0;

}

else

{

print '<tr>

<td align=center> ' . $row['PlayerID'] . ' </td>

<td align=center> ' . $row['PlayerName'] . ' </td>

</tr>';

$color=1;

}

print '</table>';

print '<input type="submit" name="button" id="button" value="Update Player Scores" /></form>';

?>

This is what I tried that did not work

<?

// Connecting, selecting database

$link = mysql_connect('localhost','user','pass');

if (!$link) {

die('Could not connect: ' . mysql_error());

}

//Query

$query=mysql_query("select DISTINCT(PlayerID) From fantasy4.temp;") or die ('Could not connect: ' . mysql_error());

while($row=mysql_fetch_array($query)){

$PlayerID = $_POST[$row['PlayerID']];

}

while($score = array($_POST['$PlayerID'])){

//Insert Query

$query2=mysql_query("UPDATE fantasy4.temp set Score='$score' where PlayerID='$PlayerID'") or die ('Yikes could not connect: ' .mysql_error());

$result = @mysql_query($query2);

}

//Check whether the query was successful or not

if($result) {

header("location: register-success.php");

exit();

}else {

die("Query failed - " .mysql_error());

}

?>

Psycho · November 29, 2011

I'm not sure I follow what you are needing. Part of the problem is that you are referring to "player" in several instances and it is not clear in each one if you are referring to the football players or the users. For example:

. . . the MySQL query would need to UPDATE all the rows for each individual PlayerID . . .

If you are referring to the football player, I'm not sure what rows you are talking about. I would assume there would only be one row for each player each week. And, if you are referring to the users, then I am even more confused. There is no need to save records for each user's score each week. You already have the users tied to the football players, so you only need to store one record for each football player's score (each week) which is associated with the football player. You can then dynamically determine a user's score.

I would think you need the following tables:

USERS: A table to store basic information about the users

PLAYERS: A table to store basic information about the football players

USER_PICKS: A table to store each user's picks

PLAYER_SCORES: A table that will hold one record for each player each week with the player's score.

Then you can determine any user's weekly or total score with a single query. For example, the following is an example to get a user's total score for all of their selected players.

SELECT SUM(score)
FROM PLAYER_SCORES
JOIN USER_PICKS ON USER_PICKS.playerID = PLAYER_SCORES.playerID
JOIN USERS ON USERS.userID = USER_PICKS.userID
WHERE USERS.userID = $userID
GROUP BY USERS.userID

airpirate · November 29, 2011

The idea of the site is that one of the users picks 4 football players (where the PlayerID comes in to play) each week. Their picks are stored in table temp. The table temp is generated by a form they fill out to pick their players. It stores four rows per Users pick, one for the QB, RB, WR, and Kicker. The MemberID is my users ID Number in another table that stores the users information.

What I need to be able to do is have a form that is dynamically generated based on what football players are picked that week (by PlayerID, the football players ID number) by my users (MemberID). I have that built, that is the first code that I posted. It works but there may be a better way of doing it that makes the processing of the form easier.

Then an admin would go to the form mentioned above every week and fill out a score for the Football Player which I need to go through and update the temp table which has a "Score" field on every row. So every row that is that table would have and ID (unique number for that row), MemberID (my users ID number), PlayerID (the football players ID number), PositionID (QB, RB, WR, and K), and Score (this is what I need to update, the football players score for the week).

So the form I made users the PlayerID as the form field ID that would would need to be the variable in the processing code. And then the MySQL query would need to "Update temp set='Score' where PlayerID='the variable or PlayerID'" but it would have to do it multiple times. I'm not sure that's even possible.

I hope I am explaining myself well or maybe I'm just confusing you more.

Psycho · November 30, 2011

I think you need to rethink your table structure. If you have multiple members that are picking the same player in a given week, you should not store the score for that player in multiple records - only one.

I would use a table (say picks) that stores each members picks of players with a column to identify the week of the pick. So you would store all their picks for the season. Then you would have a table to store the score for each player each week. You can then determine a members score for the week by making the appropriate JOINs. You could also easily pull up a form (or forms) to enter the players' scores based upon which ones were picked that week.

Having multiple records for each member associated with a player and storing the players score in all of those is poor design. Here is what I would go with:

Members
member_id, member_name

Players
player_id, player_position, player_name

Picks
week_no, member_id, player_id

Scores:
week_no, player_id, score

I really, really suggest you consider changing your design. But, if you don't want to you can easily add the score for each player to multiple records. Just run a SELECT query for the players that are picked that week in your temp table and do a group by the players. You should then have a result set of all the players picked that week. Take those results and build a form to enter all the players' scores.

Player 1 <input type='text' name='score[15]' >
Player 2 <input type='text' name='score[19]' >

The '15' and '19' represent the ID's of the players which you will use in the update script.

When the form is posted the update script will cycle through the $_POST['score'] array and get the player id and the player score for that week. I always advise against running queries in loops but I am too lazy to look up the right syntax for this right now and it would only be used for an admin to run once a week. So, just run an update query such as

foreach($_POST['score'] as $playerID => $playerScore)
{
    $query = "UPDATE table
              SET score = $playerScore
              WHERE player_id = $playerID";
    mysql_query($query);
}

If you are storing the previous weeks data in the same table then you would only want to update those records for the player where there is currently no score, so you could add an addition parameter to the WHERE clause

UPDATE table
SET score = $playerScore
WHERE player_id = $playerID"
  AND score = ''

Sign In

Dynamic Form Processing

Recommended Posts

airpirate007

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

airpirate

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information