Page 1 of 1

update multiple rows using one button

Posted: Mon Feb 23, 2015 6:40 am
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

Re: update multiple rows using one button

Posted: Mon Feb 23, 2015 8:34 am
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.

Re: update multiple rows using one button

Posted: Mon Feb 23, 2015 5:06 pm
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.

Re: update multiple rows using one button

Posted: Tue Feb 24, 2015 4:49 am
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

Re: update multiple rows using one button

Posted: Tue Feb 24, 2015 6:20 am
by Celauran
For openers, I don't see $Submit being defined anywhere.

Re: update multiple rows using one button

Posted: Tue Feb 24, 2015 6:40 am
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);
}
}

Re: update multiple rows using one button

Posted: Tue Feb 24, 2015 6:51 am
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.

Re: update multiple rows using one button

Posted: Tue Feb 24, 2015 6:56 am
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