update multiple rows using one button

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

update multiple rows using one button

Post by jonnyfortis »

i have a payment list thats shows individual payments. what happened in the past is the username was their ID but as the system has grown i needed to give each user a numerical ID. this is all done and working. the trouble is in the payment list it was the username that link the payment table with the user table. i am having to add the new userID to each payment record.
the way i am currently doing this is using the payment list.
the SQL

Code: Select all

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

mysql_select_db($database_hostprop, $hostprop);
$query_rs = "SELECT * FROM plus_signupComplete, host_payments2014 WHERE host_payments2014.payment_userid = plus_signupComplete.userid";
$rs = mysql_query($query_rs, $hostprop) or die(mysql_error());
$row_rs = mysql_fetch_assoc($rs);
$totalRows_rs = mysql_num_rows($rs);

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE host_payments2014 SET id=%s WHERE payment_id=%s",
                       GetSQLValueString($_POST['id'], "int"),
                       GetSQLValueString($_POST['payment_id'], "int"));

  mysql_select_db($database_hostprop, $hostprop);
  $Result1 = mysql_query($updateSQL, $hostprop) or die(mysql_error());
}
the table below

Code: Select all

<table width="1000" border="1" cellspacing="0" cellpadding="5">
    <tr>
      <td>Student Name</td>
      <td>student new id</td>
      <td>id payment id</td>
      <td><p>add student id to payment id</p></td>
      <td>id on payment list</td>
      </tr>
    <?php do { ?>
      <tr>
        <td><?php echo $row_rs['userid']; ?></td>
        <td><?php echo $row_rs['studentID']; ?></td>
        <td><?php echo $row_rs['payment_userid']; ?></td>
        <td><p>&nbsp;</p>
          <form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
            <table align="center">
              <tr valign="baseline">
                <td nowrap="nowrap" align="right">student Id:</td>
                <td><input type="text" name="id" value="<?php echo htmlentities($row_rs['id'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
              </tr>
              <tr valign="baseline">
                <td nowrap="nowrap" align="right">&nbsp;</td>
                <td><input type="submit" value="Update record" /></td>
              </tr>
            </table>
            <input type="hidden" name="MM_update" value="form1" />
            <input type="hidden" name="payment_id" value="<?php echo $row_rs['payment_id']; ?>" />
          </form>
          <p>&nbsp;</p></td>
        <td><?php echo $row_rs['id']; ?></td>
      </tr>
      <?php } while ($row_rs = mysql_fetch_assoc($rs)); ?>
  </table>
so as you can see each record is displayed on a new link and there is an update form for each record. i am having to add the new userID the update it to the payment table. this is done each time.
i want to be able to input each userID but them submit the form once rather than after each time i add a new userID

thanks
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: update multiple rows using one button

Post by Celauran »

Looks like you want to get rid of the one form per row approach and use a single form for the whole page, using arrays in your form field names. You can then iterate over the array once you have submitted the form, updating one row on each iteration.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: update multiple rows using one button

Post by jonnyfortis »

Celauran wrote:Looks like you want to get rid of the one form per row approach and use a single form for the whole page, using arrays in your form field names. You can then iterate over the array once you have submitted the form, updating one row on each iteration.
ok thanks i will look into how to do this.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: update multiple rows using one button

Post by jonnyfortis »

Celauran wrote:Looks like you want to get rid of the one form per row approach and use a single form for the whole page, using arrays in your form field names. You can then iterate over the array once you have submitted the form, updating one row on each iteration.
i found some script that i have adapted http://www.phpeasystep.com/mysql/10.html

but it doesnt update the records.

Code: Select all

<head>
</head>

<body>
<?php
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM plus_signupComplete, host_payments2014 WHERE host_payments2014.payment_userid = plus_signupComplete.userid AND host_payments2014.payment_id > '1520' ORDER BY host_payments2014.payment_id ASC";
$result=mysql_query($sql);

// Count table rows 
$count=mysql_num_rows($result);
?>

<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr> 
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">

<tr>
<td align="center"><strong>Payment Id</strong></td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>id</strong></td>
<td align="center"><strong>add id to payment table</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td align="center">
<? $paymentid[]=$rows['payment_id']; ?><? echo $rows['payment_id']; ?>
</td>
<td align="center">
<? echo $rows['userid']; ?>
</td>
<td align="center">
<? echo $rows['studentID']; ?>
</td>
<td align="center">
<input name="id[]" type="text" id="id" value="<? echo $rows['id']; ?>">
</td>
</tr>

<?php
}
?>

<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>

<?php

// Check if button name "Submit" is active, do this 
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE host_payments2014 SET id='$id[$i]' WHERE id='$paymentid[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:update_multiple.php");
}
mysql_close();
?>
</body>
</html>

cant see what i have missed
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: update multiple rows using one button

Post by Celauran »

For openers, I don't see $Submit being defined anywhere.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: update multiple rows using one button

Post by jonnyfortis »

Celauran wrote:For openers, I don't see $Submit being defined anywhere.
like this you mean?
$Submit = $_POST['submit'] == 'Submit';
// Check if button name "Submit" is active, do this

if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE host_payments2014 SET id='$id[$i]' WHERE id='$paymentid[$i]'";
$result1=mysql_query($sql1);
}
}
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: update multiple rows using one button

Post by Celauran »

Yes, that's what I meant.

You're also relying on two separate arrays ($paymentid and $_POST) containing the same number of elements. Accidentally skip a row when you're filling out the form and your database is busted. You really need to tie those together.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: update multiple rows using one button

Post by jonnyfortis »

Celauran wrote:Yes, that's what I meant.

You're also relying on two separate arrays ($paymentid and $_POST) containing the same number of elements. Accidentally skip a row when you're filling out the form and your database is busted. You really need to tie those together.
can you suggest a cleaner way of doing it.
thanks
jon
Post Reply