Thanks to Ashley who helped me in a previous thread.
I have now got working an update query to update one line/primary number at once.
I am now trying to create a table of football stats for my daughters football team.
I would like to have on a webpage a table below (my sql table is also like the colums below)
Player Name, Games Played, Goals Scored, Yellow Cards, Red Cards & Minutes Played.
If I have 12 or 13 players to update. How can I up date from one page all at once?
I have thought about linking 1 query to a check box for each player and only click the boxes that I want to update.
Then I would click the submit box to run all the queries.
How does that sound ?
Thanks
Dicko
Sounds interesting, I presume security isn't vital at the moment. I could cook something up that places each value into an html form, and then simply updates them all in one query, based on the values in the form. Though you really should make an id field with auto_incrament and primary key first, so the table is as such:
ID, Player Name, Games Played, Goals Scored, Yellow Cards, Red Cards & Minutes Played.
I am not sure how you are gathering them from the database, so my solution will only involve the form produced from an already gathered array of results. I'll help you implement the gathering query too, if you like.
<?php
//if the form has not already been sent (action is the same page)
if(!isset($_POST['apply'])){
/*
Just a sample multidimensional array for 3 players who were selected,
perhaps, because they are the 3 with the most played games: ordered
by that firstly and minutes played secondly, it isn't important for
this test.
*/
$playersToEdit = array(
array(1,'name 1',12,5,1,0,815),
array(8,'name 8',12,4,3,1,805),
array(3,'name 3',10,6,0,0,732)
);
//begin the form
echo '<form method="post">';
//set the playerNum variable to 0
$playerNum = 1;
//loop through every one, showing their row to be edited
foreach($playersToEdit as $player){
//a hidden field linking the player's number in the form to their id
echo '<input type="hidden" value="'. $player[0]
.'" name="playerId_'. $playerNum .'" />';
//name text box
echo '<input type="text" name="name_'. $player[0] .'" value="'.
$player[1] .'" /> '.
//games played text box
'Games: <input type="text" name="games_'. $player[0] .'" value="'.
$player[2] .'" /> '.
//goals scored text box
'Goals: <input type="text" name="goals_'. $player[0] .'" value="'.
$player[3] .'" /> '.
//yellow cards
'Yellows: <input type="text" name="yCards_'. $player[0]
.'" value="'. $player[4] .'" /> '.
//red cards
'Reds: <input type="text" name="rCards_'. $player[0] .'" value="'.
$player[5] .'" /> '.
//minutes played
'Minutes: <input type="text" name="minutes_'. $player[0]
.'" value="'. $player[6] .'" />'.
//a new line, for each player's row
'<br />';
//increment playerNum
$playerNum ++;
}
//a hidden field with the number of players to be edited
echo '<input type="hidden" value="'. count($playersToEdit)
.'" name="playersCount" />';
//now the end of the form
echo '<input type="submit" value="Apply Changes" name="apply" />';
//the form has been posted, so perform the following block of php
} else {
//loop the number of times as stated in the hidden input playersCount
for($playerNum = 1;$playerNum <= $_POST['playersCount'];$playerNum++){
//get their id
$playerId = $_POST['playerId_'. $playerNum];
//get their field values
$playerName = $_POST['name_'. $playerId];
$playerGames = $_POST['games_'. $playerId];
$playerGoals = $_POST['goals_'. $playerId];
$playerYellows = $_POST['yCards_'. $playerId];
$playerReds = $_POST['rCards_'. $playerId];
$playerMinutes = $_POST['minutes_'. $playerId];
/*
Form a query for this user based on the form values and append it
to the current one.
*/
$playersQ .= "UPDATE players WHERE id = $playerId SET ".
"'Player Name' = '$playerName', 'Games Played' = '$playerGames', ".
"'Goals Scored' = '$playerGoals', ".
"'Yellow Cards' = '$playerYellows', 'Red Cards' = '$playerReds', ".
"'Minutes Played' = '$playerMinutes'\n";
}
//show the query (with new line characters swapped to <br /> tags
echo nl2br($playersQ);
//run the query
//mysql_query($playersQ) or die(mysql_error());
}
?>
OK, that should do the trick. But you will need some form of log in to keep it safe, and entering odd values will give mysql errors or ruin the data because I havn't put any input validation on it (no real need, its just an example). Also, you may want to clean it up with html tables and put the field headings at the top. Again, you may need help getting the initial data queried. A while loop with mysql_fetch_row would be better than foreach but as I have not set up a mysql table to work on it with I had to resort to the foreach loop. When you add the id field you will have to manually set the ids for the current records (starts counting at 1).
