Jump to content


Photo

Update all fields


  • Please log in to reply
16 replies to this topic

#1 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 15 September 2006 - 04:37 PM

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)
<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>


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

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;
}
?>


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.
~ Mutley.

#2 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 15 September 2006 - 04:45 PM

first put everythign in a FORM tag

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

#3 makeshift_theory

makeshift_theory
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts

Posted 15 September 2006 - 04:48 PM

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.
<b>Emacs</b> it's great for the body.
<br><br>
<b>Trust the TechnoLust</b>

<b><u>Have a question check here:</u></b>
PHP Manual: http://www.php.net

#4 makeshift_theory

makeshift_theory
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts

Posted 15 September 2006 - 04:49 PM

first put everythign in a FORM tag

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


He's done that in the update form at the bottom, so I don't think that is the problem.
<b>Emacs</b> it's great for the body.
<br><br>
<b>Trust the TechnoLust</b>

<b><u>Have a question check here:</u></b>
PHP Manual: http://www.php.net

#5 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 15 September 2006 - 05:52 PM

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.


How should the PHP be structured?
~ Mutley.

#6 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 16 September 2006 - 07:59 AM

Anyone know whats wrong?  ???
~ Mutley.

#7 joshi_v

joshi_v
  • Members
  • PipPipPip
  • Advanced Member
  • 168 posts

Posted 16 September 2006 - 08:50 AM


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 :)


#8 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 16 September 2006 - 07:28 PM

Unexpected "else" on line 23:

<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>

Can someone check if that would work anyway?

Here is the other file done as you said:

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 
}

~ Mutley.

#9 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 17 September 2006 - 07:53 AM

Any ideas?
~ Mutley.

#10 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 17 September 2006 - 12:43 PM

The syntax for update is wrong/incomplete - you need to state which row 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?
Legend has it that reading the manual never killed anyone.
My site

#11 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 17 September 2006 - 03:28 PM

Ok, it looks like this now:

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;
}

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

#12 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 19 September 2006 - 09:26 PM

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

#13 makeshift_theory

makeshift_theory
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts

Posted 20 September 2006 - 05:04 PM

$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
      }
?>

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.
<b>Emacs</b> it's great for the body.
<br><br>
<b>Trust the TechnoLust</b>

<b><u>Have a question check here:</u></b>
PHP Manual: http://www.php.net

#14 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 21 September 2006 - 06:40 PM

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

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;
}

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

Thanks for anyones help.

~ Mutley.

#15 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 22 September 2006 - 06:08 PM

What should I do here:

for($i=0;$i<$num_rows;$i++)
{

Is everything else in the above posts correct?
~ Mutley.

#16 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 23 September 2006 - 10:37 AM

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

#17 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 23 September 2006 - 11:12 AM

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.

<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>

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.

<?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>";

}?>


Note, when updating I would recalc the pts and diff before updating instead of just taking the input values.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users