Jump to content

Update all fields


Mutley

Recommended Posts

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[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?
Link to comment
Share on other sites


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 :)
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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"?
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.