Update all fields

[Mutley]

I know how to update a row at a time but not all at once, here is my code:

update.php (This is the form and lists all the rows in the database to be updated)
[code]<table align="center" width="60%">
  <tr>
    <td>Team</td>
    <td>P</td>
    <td>W</td>
    <td>D</td>
    <td>L</td>
    <td>For</td>
    <td>Against</td>
    <td>Diff</td>
    <td>Pts</td>
  </tr>
<?php
require_once("connection.php");
mysql_select_db("rufc");

$result = mysql_query("SELECT * FROM league ORDER BY pts DESC, diff DESC");
while($row = mysql_fetch_array( $result ))
    {

$team = $row['team'];
?>
<form action="admin_updateleague.php" method="POST">
  <tr>
    <td>
<input type="hidden" name="team" value="<?php echo $row['team']; ?>">
<?=$team?>
</td>
    <td><input class="form" type="text" size="3" name="p" value="<?=$row['p']; ?>"></td>
    <td><input class="form" type="text" size="3" name="w" value="<?=$row['w']; ?>"></td>
    <td><input class="form" type="text" size="3" name="d" value="<?=$row['d']; ?>"></td>
    <td><input class="form" type="text" size="3" name="l" value="<?=$row['l']; ?>"></td>
    <td><input class="form" type="text" size="3" name="for" value="<?=$row['for']; ?>"></td>
    <td><input class="form" type="text" size="3" name="against" value="<?=$row['against']; ?>"></td>
    <td><input class="form" type="text" size="3" name="diff" value="<?=$row['diff']; ?>"></td>
    <td><input class="form" type="text" size="3" name="pts" value="<?=$row['pts']; ?>"></td>
  </tr>
<?php
      }
?>
<tr>
<td><input class="form" type="submit" value="Update League Table"></td>
</tr>
</form>
<tr>
<td>
</td>
</tr>
</table>[/code]


update_confirm.php (This is what updates the database fields)

[code]require_once("connection.php");

if(isset($_POST['team'])){
$team = $_POST['team'];
$p = $_POST['p'];
$w = $_POST['w'];
$d = $_POST['d'];
$l = $_POST['l'];
$for = $_POST['for'];
$against = $_POST['against'];
$diff = $_POST['diff'];
$pts = $_POST['pts'];


mysql_select_db("rufc");

$sql = "UPDATE league SET team='$team', p='$p', w='$w',
d='$d', l='$l', for='$for', against='$against', diff='$diff', pts='$pts' ";
mysql_query($sql);

  echo "The selected information was updated! ";
  echo "<a href='league.php'>Click here</a> to continue";
  echo $sql;
}
?>
[/code]


Now the problems are, it can't update all the fields, like every 'team', it just doesn't work, I echoed $sql and it just does one 'team' with every field ="" .... nothing.

What I want it to do is load all the rows in the form update.php then update every row. Not just 1.

[markbett]

first put everythign in a FORM tag

second give form elements names that correspond to what you need to pass to the db

[makeshift_theory]

Well it seems like your only putting one variable in the loop, when you query that db it's going to print out a form for each team however, when you run your check on it on the other page, it doesn't know which form to pull the info from.  So basically what I'm saying is it's like your sending all the variables on the same form and it can't tell which form to get the info from.  you need to put the form tags outside the while loop so that way it's only one form I think this may help.

[makeshift_theory]

[quote author=markbett link=topic=108196.msg434999#msg434999 date=1158338742]
first put everythign in a FORM tag

second give form elements names that correspond to what you need to pass to the db
[/quote]

He's done that in the update form at the bottom, so I don't think that is the problem.

[Mutley]

[quote author=makeshift_theory link=topic=108196.msg435005#msg435005 date=1158338907]
Well it seems like your only putting one variable in the loop, when you query that db it's going to print out a form for each team however, when you run your check on it on the other page, it doesn't know which form to pull the info from.  So basically what I'm saying is it's like your sending all the variables on the same form and it can't tell which form to get the info from.  you need to put the form tags outside the while loop so that way it's only one form I think this may help.
[/quote]

How should the PHP be structured?

[Mutley]

Anyone know whats wrong?  ???

[joshi_v]

In ipload.php file when u r displaying rows use an increment counter to keep track of row number else u can use for loop also..

like this
$num_rows=mysql_num_rows($result);
for($i=0;$i<$num_rows;$i++)
{
$res=mysql_fetch_array($result));

}

else
$i=0;

in while loop increment the i counter .

Next when u displaying the form fields like 
<td><input class="form" type="text" size="3" name="p" value="<?=$row['p']; ?>"></td>

replace it with this
<td><input class="form" type="text" size="3" name="p<?php echo $i; ?> value="<?=$row['p']; ?>"></td>

at the end of the form pass a hidden variable with number of rows value
<input type="hidden" name="num_rows" value="<?=$num_rows;?>>


Do the same for all other form fields. after submission of the form, take another for or while loop


for($i=0;$i<$num_rows;$i++)
{
$team = $_POST['team'];
$p = $_POST[p.$i];
$w = $_POST[w.$i];
$d = $_POST[d.$i];
$l = $_POST[l.$i];
$for = $_POST[for.$i];
$against = $_POST[against.$i];
$diff = $_POST[diff.$i];
$pts = $_POST[pts.$i];


mysql_select_db("rufc");

$sql = "UPDATE league SET team='$team', p='$p', w='$w',
d='$d', l='$l', for='$for', against='$against', diff='$diff', pts='$pts' ";
mysql_query($sql);

  echo "The selected information was updated! ";
  echo "<a href='league.php'>Click here</a> to continue";
  echo $sql;
}
?>


Hope this will help you

Cheers :)

[Mutley]

Unexpected "else" on line 23:

[code]<table align="center" width="60%">
  <tr>
    <td>Team</td>
    <td>P</td>
    <td>W</td>
    <td>D</td>
    <td>L</td>
    <td>For</td>
    <td>Against</td>
    <td>Diff</td>
    <td>Pts</td>
  </tr>
<?php
require_once("connection.php");
mysql_select_db("rufc");

$result = mysql_query("SELECT * FROM league ORDER BY pts DESC, diff DESC");
$num_rows=mysql_num_rows($result);
for($i=0;$i<$num_rows;$i++)
{
$res=(mysql_fetch_array($result));

} else {
$i=0;
$team = $row['team'];
?>
<form action="admin_updateleague.php" method="POST">
  <tr>
    <td>
<input type="hidden" name="team" value="<?php echo $row['team']; ?>">
<?=$team?>
</td>
    <td><input class="form" type="text" size="3" name="<?php echo $i; ?>" value="<?=$row['p']; ?>"></td>
    <td><input class="form" type="text" size="3" name="<?php echo $i; ?>" value="<?=$row['w']; ?>"></td>
    <td><input class="form" type="text" size="3" name="<?php echo $i; ?>" value="<?=$row['d']; ?>"></td>
    <td><input class="form" type="text" size="3" name="<?php echo $i; ?>" value="<?=$row['l']; ?>"></td>
    <td><input class="form" type="text" size="3" name="<?php echo $i; ?>" value="<?=$row['for']; ?>"></td>
    <td><input class="form" type="text" size="3" name="<?php echo $i; ?>" value="<?=$row['against']; ?>"></td>
    <td><input class="form" type="text" size="3" name="<?php echo $i; ?>" value="<?=$row['diff']; ?>"></td>
    <td><input class="form" type="text" size="3" name="<?php echo $i; ?>" value="<?=$row['pts']; ?>"></td>
  </tr>
<?php
      }
?>
<tr>
<td><input class="form" type="submit" value="Update League Table"></td>
</tr>
<input type="hidden" name="num_rows" value="<?=$num_rows;?>">
</form>
<tr>
<td>
</td>
</tr>
</table>[/code]

Can someone check if that would work anyway?

Here is the other file done as you said:

[code]require_once("connection.php");

for($i=0;$i<$num_rows;$i++)
{
$team = $_POST['team'];
$p = $_POST[p.$i];
$w = $_POST[w.$i];
$d = $_POST[d.$i];
$l = $_POST[l.$i];
$for = $_POST[for.$i];
$against = $_POST[against.$i];
$diff = $_POST[diff.$i];
$pts = $_POST[pts.$i];


mysql_select_db("rufc");

$sql = "UPDATE league SET team='$team', p='$p', w='$w',
d='$d', l='$l', for='$for', against='$against', diff='$diff', pts='$pts' ";
mysql_query($sql);

  echo "The selected information was updated! ";
  echo "<a href='league.php'>Click here[/url] to continue";
  echo $sql;
}
?>

<!--html-->

<?php
}
[/code]

[Mutley]

Any ideas?

[AndyB]

The syntax for update is wrong/incomplete - you need to state [b]which row[/b] needs to be updated.

UPDATE tablename SET a = '$a', .... WHERE something = '$something_else'

If your database table records have an id, pass it's value in the form and use that as the WHERE condition, otherwise choose something constant in the table row - like the team name perhaps?

[Mutley]

Ok, it looks like this now:

[code]require_once("connection.php");

for($i=0;$i<$num_rows;$i++)
{
$team = $_POST['team'];
$p = $_POST[p.$i];
$w = $_POST[w.$i];
$d = $_POST[d.$i];
$l = $_POST[l.$i];
$for = $_POST[for.$i];
$against = $_POST[against.$i];
$diff = $_POST[diff.$i];
$pts = $_POST[pts.$i];


mysql_select_db("rufc");

$sql = "UPDATE league SET team='$team', p='$p', w='$w',
d='$d', l='$l', for='$for', against='$against', diff='$diff', pts='$pts' WHERE team='$team'";
mysql_query($sql);

  echo "The selected information was updated! ";
  echo "<a href='league.php'>Click here[/url] to continue";
  echo $sql;
}
[/code]

But the problem is still with the league.php which has an error on line 23 due to unexpected "ELSE"?

[Mutley]

Updating all fields can't be this hard to get no reply? Unless there is an easier way? :(

[makeshift_theory]

[code]
$result = mysql_query("SELECT * FROM league ORDER BY pts DESC, diff DESC");
while($row = mysql_fetch_array( $result ))
    {

$team = $row['team'];
?>
<form action="admin_updateleague.php" method="POST">
  <tr>
    <td>
<input type="hidden" name="team" value="<?php echo $row['team']; ?>">
<?=$team?>
</td>
    <td><input class="form" type="text" size="3" name="p" value="<?=$row['p']; ?>"></td>
    <td><input class="form" type="text" size="3" name="w" value="<?=$row['w']; ?>"></td>
    <td><input class="form" type="text" size="3" name="d" value="<?=$row['d']; ?>"></td>
    <td><input class="form" type="text" size="3" name="l" value="<?=$row['l']; ?>"></td>
    <td><input class="form" type="text" size="3" name="for" value="<?=$row['for']; ?>"></td>
    <td><input class="form" type="text" size="3" name="against" value="<?=$row['against']; ?>"></td>
    <td><input class="form" type="text" size="3" name="diff" value="<?=$row['diff']; ?>"></td>
    <td><input class="form" type="text" size="3" name="pts" value="<?=$row['pts']; ?>"></td>
  </tr>
<?php
      }
?>[/code]

On that page I don't think it will work the way your intending.  You are exiting your while loop when you close the php tag so it will not get the row values if i'm not mistaken.  I would use echo's or print's to print your table structure so you don't have to exit the loop.

[Mutley]

Thanks, I can't see if that works because this file needs changing in order to work with yours above:

[code]require_once("connection.php");

for($i=0;$i<$num_rows;$i++)
{
$team = $_POST['team'];
$p = $_POST[p.$i];
$w = $_POST[w.$i];
$d = $_POST[d.$i];
$l = $_POST[l.$i];
$for = $_POST[for.$i];
$against = $_POST[against.$i];
$diff = $_POST[diff.$i];
$pts = $_POST[pts.$i];


mysql_select_db("rufc");

$sql = "UPDATE league SET team='$team', p='$p', w='$w',
d='$d', l='$l', for='$for', against='$against', diff='$diff', pts='$pts' WHERE team='$team'";
mysql_query($sql);

  echo "The selected information was updated! ";
  echo "<a href='league.php'>Click here[/url] to continue";
  echo $sql;
}[/code]

I guess the "FOR" statement is wrong at the top? What should it be?

Thanks for anyones help.

[Mutley]

What should I do here:

[code]
for($i=0;$i<$num_rows;$i++)
{[/code]

Is everything else in the above posts correct?

[Mutley]

PLEASE could someone help me with this, it is becoming urgent.  ???

[Barand]

I've started form your original posted code, altered the form so all rows in a single form and renamed the form fields so names end with "[]". This will cause them to be posted in an array of fields.

[code]
<form action="admin_updateleague.php" method="POST"> 
<table align="center" width="60%">
  <tr>
    <td>Team</td>
    <td>P</td>
    <td>W</td>
    <td>D</td>
    <td>L</td>
    <td>For</td>
    <td>Against</td>
    <td>Diff</td>
    <td>Pts</td>
  </tr>
<?php
require_once("connection.php");
mysql_select_db("rufc");

$result = mysql_query("SELECT * FROM league ORDER BY pts DESC, diff DESC");
while($row = mysql_fetch_array( $result ))
    {

$team = $row['team'];
?>
  <tr>
    <td>
<input type="hidden" name="team[]" value="<?php echo $row['team']; ?>">
<?=$team?>
</td>
    <td><input class="form" type="text" size="3" name="p[]" value="<?=$row['p']; ?>"></td>
    <td><input class="form" type="text" size="3" name="w[]" value="<?=$row['w']; ?>"></td>
    <td><input class="form" type="text" size="3" name="d[]" value="<?=$row['d']; ?>"></td>
    <td><input class="form" type="text" size="3" name="l[]" value="<?=$row['l']; ?>"></td>
    <td><input class="form" type="text" size="3" name="for[]" value="<?=$row['for']; ?>"></td>
    <td><input class="form" type="text" size="3" name="against[]" value="<?=$row['against']; ?>"></td>
    <td><input class="form" type="text" size="3" name="diff[]" value="<?=$row['diff']; ?>"></td>
    <td><input class="form" type="text" size="3" name="pts[]" value="<?=$row['pts']; ?>"></td>
  </tr>
<?php
      }
?>
<tr>
<td><input class="form" type="submit" value="Update League Table"></td>
</tr>
<tr>
<td>
</td>
</tr>
</table>
</form>
[/code]

To process, get the array key of each team and the other related data has same key. Pick up all the values for the team and update. Do for each team.

[code]
<?php
require_once("connection.php");
mysql_select_db("rufc");


if(isset($_POST['team'])) {
    foreach ($_POST['team'] as $k => $team) {

        $p = $_POST['p'][$k];
        $w = $_POST['w'][$k];
        $d = $_POST['d'][$k];
        $l = $_POST['l'][$k];
        $for = $_POST['for'][$k];
        $against = $_POST['against'][$k];
        $diff = $_POST['diff'][$k];
        $pts = $_POST['pts'][$k];
       
        $sql = "UPDATE league SET `p`='$p', `w`='$w',
        `d`='$d', `l`='$l', `for`='$for', `against`='$against', `diff`='$diff', `pts`='$pts'
        WHERE `team` = '$team' ";
        mysql_query($sql) or die(mysql_error());       
    }




  echo "The information was updated! <br>";
  echo "<a href='league.php'>Click here</a> to continue <br>";

}?>

[/code]

Note, when updating I would recalc the pts and diff before updating instead of just taking the input values.