Page 1 of 1

novice mySQL UPDATE of multiple records from form

Posted: Thu Oct 16, 2008 3:17 pm
by rhecker
To simplify, I have stripped my code example down to the essential issue. I have a form that is populated with a list of records from a database. An example would be a list of products and their assiciated prices. There is a auto_increment primary key. With all the product items on one screen, I want to change prices of some, then reupload the whole lot back to the database. I can populate my form just fine, but I can't get the edited data back into the database. I've tried to set up a foreach loop, but no changes were reflected. Any help appreciated.

Code: Select all

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data"> 
 <?php
 include("php_includes/localhost.php");
$result = mysql_query ("SELECT test1, id from test") or die ('Error: '.mysql_error ());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>
<p>
TEST1: <input name="test1" type="text" value="<?php echo ($row["test1"])?>"/>
<input name="id" type="text" value="<?php echo ($row["id"])?>"/>
<?php
;}
?>
 <input type="hidden" name="submitted" value="1" />
<input type="submit" value="Submit" />                                                         
</form>
 
<?php if ($_POST['submitted']) {
 
$test1 = $_POST['test1'];
$id = $_POST['id'];
 
 $sql = "UPDATE test SET
          test1='$test1',
          WHERE id='$id'";
$ok = @mysql_query($sql);         
  }
  ?>

Re: novice mySQL UPDATE of multiple records from form

Posted: Tue Oct 21, 2008 1:28 am
by mallik220613
Better fetch how many records you have and store it in a hidden value.
"<input type='hidden' name='Num_Of_rows' value='<? echo mysql_num_rows($result)?>' />
Now pass the value of the ID of each row to a hidden field with name as given below

Code: Select all

<form>
<?
    $i=0;
    while($row=mysql_fetch_row($result))
    {
      echo "<input type='hidden' name='id$i' value='$row[0]' />";
      echo "<input type='text' name='price$i' value='$row[1]' />";
      $i++;
    }
     
?> 
</form>
After submitting the form, update the price values using below code

Code: Select all

<?
   mysql_connect('hostname','db_username','db_password');
   mysql_select_db('databasename');
   $num_of_rows = $_REQUEST['Num_Of_Rows'];
   for($i=0;$i<$num_of_rows;$i++)
   {
       $id_field_name =  'id'.$i;
       $price_field_name = 'price'.$i;
       $id = $_REQUEST[$id_field_name];
       $price = $_REQUEST[$price_field_name];
       mysql_query("update tablename set price='$price' where id='$id'");
   }
?>

Re: novice mySQL UPDATE of multiple records from form

Posted: Tue Oct 21, 2008 5:02 am
by aceconcepts
An alternative way would be to submit the form fields as arrays:

Code: Select all

 
<form>
<?
    while($row=mysql_fetch_row($result))
    {
      echo "<input type='hidden' name='id[]' value='$row[0]' />";
      echo "<input type='text' name='price[]' value='$row[1]' />";
    }
?>
</form>
 
Using the arrays you can simply reference them in the for loop like this:

Code: Select all

 
for($i=0;$i<$num_of_rows;$i++)
   {
       $id_field_name =  $_POST['id'][$i];
       //etc...
       mysql_query("update tablename set price='$price' where id='$id'");
   }