• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Form save button to update all records at once

I have the following form. Right now every record has its own save button so the user has to hit save button after he make change to every row. Now what I want to do is just have one save button at the end of table, which will automatically update all the rows . How can I do that. Please see my code below.
Thanks
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="100%">
<tr>
<td class="hr">&nbsp;</td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "ID" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("ID") ?></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Field1" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Field1") ?></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Field2" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Field2") ?></a></td>
 
</tr>
<?php
  for ($i = $startrec; $i < $reccount; $i++)
  {
    $row = mysql_fetch_assoc($res);
    $style = "dr";
    if ($i % 2 != 0) {
      $style = "sr";
    }
?>
<form action="index.php" method="post">
<tr>
<td class="<?php echo $style ?>"><a href="index.php?a=edit&recid=<?php echo $i ?>">Edit</a></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["ID"]) ?><input type="hidden" name ="IDf" value="<?php  echo $row["ID"]; ?>" class="<?php echo $style ?>"></td>
<td class="<?php echo $style ?>"><input type="text" name="Field1f" value="<?php echo $row["Field1"]; ?>" class="<?php echo $style ?>" border="hidden"></td>
<td class="<?php echo $style ?>"><input type="text" name="Field2f" value="<?php echo htmlspecialchars($row["Field2"]) ?>" class="<?php echo $style ?>"border="hidden"></td>
 
<td class="<?php echo $style ?>"><input name="Save" type="submit" Value="save"></form></td>
</tr>
<?php
 
  }
  mysql_free_result($res);
  if(isset($_POST["Save"]))
{
 
 
$conn = mysql_connect("localhost", "root", "password");
  mysql_select_db("db");
 mysql_query("update mytable set Field1 = '".$_POST["Field1f"]."' , Field2 = '".$_POST["Field2f"]."' where ID='".$_POST["IDf"]."'"); 
 $addrowstotable = mysql_query("select Field1 from mytable where Field1 is null or Field1=''");
 $addrow = mysql_num_rows($addrowstotable);
 if($addrow >0)
 {
 echo "no need ";
  header("location: index.php");
 }
 else
 {
 for ($i=0; $i<=5; $i++)
 {
 
  $VL = "";
 mysql_query("insert into mytable (Field1) Values ('".$VL."')");
  header("location: index.php");
 }
 
 }
 
 
 
 
 
}
?>
</table>
 
<br>

Open in new window

0
syedasimmeesaq
Asked:
syedasimmeesaq
  • 5
  • 5
1 Solution
 
Roger BaklundCommented:
Move the form tags out of the table:

<form ...>
<table ...>
...
</table>
</form>

There is no need to use PHP to output a static string: "<?php echo 'static'; ?>" can be replaced by "static".

border="hidden" is not a valid attribute for the input element.

Empty html elements (like <input>) should end with " />". <br> should be <br />.

There must be an unique id in the record ($row['ID']?), it should be numerical (integer). You should not rely on a record counter ($i in your code) to identify records. What happens if another user removes a record? $i would point to the wrong record, and the update would overwrite it!

Each input element must have an unique name. Use the row ID to make the name unique:

<input type="hidden" name ="IDf" value="<?php  echo $row["ID"]; ?>" class="<?php echo $style ?>">

should be

<input type="hidden" name ="IDf_<?php  echo $row["ID"]; ?>" value="<?php  echo $row["ID"]; ?>" class="<?php echo $style ?>" />

The same change must be done with all input fields:

<input type="text" name="Field1f" value="<?php echo $row["Field1"]; ?>" class="<?php echo $style ?>" border="hidden">

should be

<input type="text" name="Field1f_<?php  echo $row["ID"]; ?>" value="<?php echo $row["Field1"]; ?>" class="<?php echo $style ?>" />

Now, to fetch the value of Field1f for the row with ID=$id, you would use $_POST['Field1f_'.$id]. See sample code below.

I assume this is not going to be an open online application, so I won't go in detail on filtering user input. But in general when writing code to be published on the web, you should consider any input from the users to be a potential risk. Read about sql injection in the php manual :

http://php.net/manual/en/security.database.sql-injection.php

Below I define a sample $filter function, and use it to fetch user data. Exactly how to code this is application specific. This example removes all html tags and backslashes, then adds the backslashes needed by mysql.
  $filter=create_function('$s',
   '$tmp = strip_tags($s);
    $tmp = str_replace(chr(92),"",$tmp);
    return mysql_real_escape_string($tmp);');
 
 foreach($_POST as $key=>$value) {
   if(substr($key,0,4)=='IDf_') {
     $row_id = (int) $value; # integer, filter not needed
     $field1 = $filter($_POST["Field1f_$row_id"]);
     $field2 = $filter($_POST["Field2f_$row_id"]);
     if(!mysql_query(sprintf(
       "update mytable set Field1 = '%s', Field2 = '%s' where ID=%d",
       $field1,$field2,$row_id))) die('Error: '.mysql_error()); 
   }
 }

Open in new window

0
 
syedasimmeesaqAuthor Commented:
i tried that and it didn't update anything
0
 
Roger BaklundCommented:
Post your updated code, complete.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
syedasimmeesaqAuthor Commented:
ok and other problem is that it erased all my data from Field 1. Please find the complete code below.
Thanks
<?php session_start();
  if (isset($_GET["order"])) $order = @$_GET["order"];
  if (isset($_GET["type"])) $ordtype = @$_GET["type"];
 
  if (!isset($order) && isset($_SESSION["order"])) $order = $_SESSION["order"];
  if (!isset($ordtype) && isset($_SESSION["type"])) $ordtype = $_SESSION["type"];
 
?>
 
<html>
<head>
<title>mytable</title>
<meta name="generator" http-equiv="content-type" content="text/html">
<style type="text/css">
  body {
    background-color: #FFFFFF;
    color: #0000CD;
    font-family: Arial;
    font-size: 12px;
  }
  .bd {
    background-color: #FFFFFF;
    color: #0000CD;
    font-family: Arial;
    font-size: 12px;
  }
  .tbl {
  background-color:#003399;
  border:solid 2px;
  }
  a:link { 
    background-color: #FFFFFF01;
    color: #FF0000;
    font-family: Arial;
    font-size: 12px;
  }
  a:active { 
    background-color: #FFFFFF01;
    color: #0000FF;
    font-family: Arial;
    font-size: 12px;
  }
  a:visited { 
    color: #800080;
    font-family: Arial;
    font-size: 12px;
  }
  .hr {
    background-color: #ADD8E6;
    color: #0066CC;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:link {
    color: #0066CC;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:active {
    color: #0066CC;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:visited {
    color: #0066CC;
    font-family: Arial;
    font-size: 12px;
  }
  .dr {
  background-color:#DDF0EF;
    color: #5F605F;
    font-family: Arial;
    font-size: 12px;
	
  }
  .sr {
  background-color:#F2F2F2;
    color: #5F605F;
    font-family: Arial;
    font-size: 12px;
	
  }
</style>
</head>
<body>
 
<?php
  $conn = connect();
  $showrecs = 100;
  $pagerange = 10;
 
  $a = @$_GET["a"];
  $recid = @$_GET["recid"];
  $page = @$_GET["page"];
  if (!isset($page)) $page = 1;
 
  $sql = @$_POST["sql"];
 
  switch ($sql) {
    case "update":
      sql_update();
      break;
  }
 
  switch ($a) {
    case "edit":
      editrec($recid);
      break;
    default:
      select();
      break;
  }
 
  if (isset($order)) $_SESSION["order"] = $order;
  if (isset($ordtype)) $_SESSION["type"] = $ordtype;
 
  mysql_close($conn);
?>
 
</body>
</html>
 
<?php function select()
  {
  global $a;
  global $showrecs;
  global $page;
  global $order;
  global $ordtype;
 
 
  if ($a == "reset") {
    $order = "";
    $ordtype = "";
  }
 
  if ($ordtype == "asc") { $ordtypestr = "desc"; } else { $ordtypestr = "asc"; }
  $res = sql_select();
  $count = sql_getrecordcount();
  if ($count % $showrecs != 0) {
    $pagecount = intval($count / $showrecs) + 1;
  }
  else {
    $pagecount = intval($count / $showrecs);
  }
  $startrec = $showrecs * ($page - 1);
  if ($startrec < $count) {mysql_data_seek($res, $startrec);}
  $reccount = min($showrecs * $page, $count);
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr><td>Table: mytable</td></tr>
<tr><td>Records shown <?php echo $startrec + 1 ?> - <?php echo $reccount ?> of <?php echo $count ?></td></tr>
</table>
<hr size="1" noshade>
<?php showpagenav($page, $pagecount); ?>
<br>
<table class="tbl" border="2" cellspacing="1" cellpadding="5"width="95%">
<tr>
<td class="hr">&nbsp;</td>
<td class="hr"><a class="hr" href="one.php?order=<?php echo "ID" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("ID") ?></a></td>
<td class="hr"><a class="hr" href="one.php?order=<?php echo "Field1" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Field1") ?></a></td>
<td class="hr"><a class="hr" href="one.php?order=<?php echo "Field2" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Field2") ?></a></td>
 
</tr>
<?php
  for ($i = $startrec; $i < $reccount; $i++)
  {
    $row = mysql_fetch_assoc($res);
    $style = "dr";
    if ($i % 2 != 0) {
      $style = "sr";
    }
?>
<form action="one.php" method="post">
<tr>
<td class="<?php echo $style ?>"><a href="one.php?a=edit&recid=<?php echo $i ?>">Edit</a></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["ID"]) ?><input type="hidden" name ="IDf_<?php echo $row["ID"]; ?>" value="<?php  echo $row["ID"]; ?>" class="<?php echo $style ?>"></td>
<td class="<?php echo $style ?>"><input type="text" name="Field1f_<?php echo $row["ID"]; ?> " value="<?php echo $row["Field1"]; ?>" class="<?php echo $style ?>" border="hidden"></td>
<td class="<?php echo $style ?>"><input type="text" name="Field2f_<?php echo $row["ID"]; ?>" value="<?php echo htmlspecialchars($row["Field2"]) ?>" class="<?php echo $style ?>"border="hidden"></td>
 
<td class="<?php echo $style ?>"></td>
</tr>
<?php
 
  }
  mysql_free_result($res);
  if(isset($_POST["Save"]))
{
 
 
$conn = mysql_connect("localhost", "root", "password");
  mysql_select_db("db");
  
  
  
  $filter=create_function('$s',
   '$tmp = strip_tags($s);
    $tmp = str_replace(chr(92),"",$tmp);
    return mysql_real_escape_string($tmp);');
 
 foreach($_POST as $key=>$value) {
   if(substr($key,0,4)=='IDf_') {
     $row_id = (int) $value; # integer, filter not needed
     $field1 = $filter($_POST["Field1f_$row_id"]);
     $field2 = $filter($_POST["Field2f_$row_id"]);
     if(!mysql_query(sprintf(
       "update mytable set Field1 = '%s', Field2 = '%s' where ID=%d",
       $field1,$field2,$row_id))) die('Error: '.mysql_error()); 
   }
 }
 
 $addrowstotable = mysql_query("select Field1 from mytable where Field1 is null or Field1=''");
 $addrow = mysql_num_rows($addrowstotable);
 if($addrow >0)
 {
 echo "no need ";
  header("location: one.php");
 }
 else
 {
 for ($i=0; $i<=5; $i++)
 {
 
  $VL = "";
 mysql_query("insert into mytable (Field1) Values ('".$VL."')");
  header("location: one.php");
 }
 
 }
 
 
 
 
 
}
?>
</table>
<input name="Save" type="submit" Value="save"></form>
 
<br>
<?php showpagenav($page, $pagecount); ?>
<?php } ?>
 
<?php function showroweditor($row, $iseditmode)
  {
  global $conn;
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="50%">
<tr>
<td class="hr"><?php echo htmlspecialchars("Field1")."&nbsp;" ?></td>
<td class="dr"><textarea cols="35" rows="4" name="Field1"><?php echo str_replace('"', '&quot;', trim($row["Field1"])) ?></textarea></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Field2")."&nbsp;" ?></td>
<td class="dr"><textarea cols="35" rows="4" name="Field2" maxlength="1000"><?php echo str_replace('"', '&quot;', trim($row["Field2"])) ?></textarea></td>
</tr>
</table>
<?php } ?>
 
<?php function showpagenav($page, $pagecount)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<?php if ($page > 1) { ?>
<td><a href="one.php?page=<?php echo $page - 1 ?>">&lt;&lt;&nbsp;Prev</a>&nbsp;</td>
<?php } ?>
<?php
  global $pagerange;
 
  if ($pagecount > 1) {
 
  if ($pagecount % $pagerange != 0) {
    $rangecount = intval($pagecount / $pagerange) + 1;
  }
  else {
    $rangecount = intval($pagecount / $pagerange);
  }
  for ($i = 1; $i < $rangecount + 1; $i++) {
    $startpage = (($i - 1) * $pagerange) + 1;
    $count = min($i * $pagerange, $pagecount);
 
    if ((($page >= $startpage) && ($page <= ($i * $pagerange)))) {
      for ($j = $startpage; $j < $count + 1; $j++) {
        if ($j == $page) {
?>
<td><b><?php echo $j ?></b></td>
<?php } else { ?>
<td><a href="one.php?page=<?php echo $j ?>"><?php echo $j ?></a></td>
<?php } } } else { ?>
<td><a href="one.php?page=<?php echo $startpage ?>"><?php echo $startpage ."..." .$count ?></a></td>
<?php } } } ?>
<?php if ($page < $pagecount) { ?>
<td>&nbsp;<a href="one.php?page=<?php echo $page + 1 ?>">Next&nbsp;&gt;&gt;</a>&nbsp;</td>
<?php } ?>
</tr>
</table>
<?php } ?>
 
<?php function showrecnav($a, $recid, $count)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="one.php">Index Page</a></td>
<?php if ($recid > 0) { ?>
<td><a href="one.php?a=<?php echo $a ?>&recid=<?php echo $recid - 1 ?>">Prior Record</a></td>
<?php } if ($recid < $count - 1) { ?>
 
<td><a href="one.php?a=<?php echo $a ?>&recid=<?php echo $recid + 1 ?>">Next Record</a></td>
<?php } ?>
</tr>
</table>
<hr size="1" noshade>
<?php } ?>
 
<?php function editrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("edit", $recid, $count);
?>
<br>
<form enctype="multipart/form-data" action="one.php" method="post">
<input type="hidden" name="sql" value="update">
<input type="hidden" name="xID" value="<?php echo $row["ID"] ?>">
<?php showroweditor($row, true); ?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php
  mysql_free_result($res);
} ?>
 
<?php function connect()
{
  $conn = mysql_connect("localhost", "root", "passowrd");
  mysql_select_db("db");
  return $conn;
}
 
function sqlvalue($val, $quote)
{
  if ($quote)
    $tmp = sqlstr($val);
  else
    $tmp = $val;
  if ($tmp == "")
    $tmp = "NULL";
  elseif ($quote)
    $tmp = "'".$tmp."'";
  return $tmp;
}
 
function sqlstr($val)
{
  return str_replace("'", "''", $val);
}
 
function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  $sql = "SELECT ID, `Field1`, `Field2` FROM `mytable`";
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}
 
function sql_getrecordcount()
{
  global $conn;
  global $order;
  global $ordtype;
  $sql = "SELECT COUNT(*) FROM `mytable`";
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  reset($row);
  return current($row);
}
 
function sql_update()
{
  global $conn;
  global $_POST;
 
  $sql = "update `mytable` set `Field1`=" .sqlvalue(@$_POST["Field1"], true).", `Field2`=" .sqlvalue(@$_POST["Field2"], true) ." where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
}
function sql_update1()
{
  global $conn;
  global $_POST;
 
  $sql = "update `mytable` set `Field1`=" .sqlvalue(@$_POST["Field1"], true).", `Field2`=" .sqlvalue(@$_POST["Field2"], true);
  mysql_query($sql, $conn) or die(mysql_error());
}
function primarykeycondition()
{
  global $_POST;
  $pk = "";
  $pk .= "(ID";
  if (@$_POST["xID"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xID"], true);
  };
  $pk .= ")";
  return $pk;
}
 ?>

Open in new window

0
 
Roger BaklundCommented:
The form start tag is still inside the loop. Move line 174 to before line 157.

You connect to the database in line 88, and again in line 191. Instead, you should use global $conn; in function select() (if you need the $conn db resource handle, I don't think you do.).

global $_POST; is redundant, $_POST is a "superglobal".

What is going on in line 212-229 ?

You can not use header("location: one.php") after output of html.  Will give warning "headers already sent".

The function sql_update() has hardcoded values for the $_POST array, but I guess it is not in use in this test.

For debuging purpouses, replace the lines 206-208:

     if(!mysql_query(sprintf(
       "update mytable set Field1 = '%s', Field2 = '%s' where ID=%d",
       $field1,$field2,$row_id))) die('Error: '.mysql_error());

...with this:

     $query = sprintf(
       "update mytable set Field1 = '%s', Field2 = '%s' where ID=%d",
       $field1,$field2,$row_id);
     echo $query.'<br />';
     #if(!mysql_query($query)) die('Error: '.mysql_error());

How is the table defined, is there a primary key? Show output of the following SQL statement:

SHOW CREATE TABLE mytable;
0
 
syedasimmeesaqAuthor Commented:
here are the show table results

Array ( [Table] => mytable [Create Table] => CREATE TABLE `mytable` ( `ID` bigint(255) NOT NULL auto_increment, `Field1` varchar(1000) default NULL, `Field2` varchar(1000) default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 )

thanks
0
 
Roger BaklundCommented:
Looks ok, the ID column is numeric and it is a primary key.

Have you modified the script and tried again? Does it output the SQL update statements?
0
 
syedasimmeesaqAuthor Commented:
somehow I figured it out..I used a totally different approach ..it works and I will paste it here soon. However I have one problem

when I execute this function and if my string contains '
which is ( ' )
then my this query  fails

function sql_update()
{
  global $conn;
  global $_POST;
 
  $sql = "update `mytable` set `Field1`=" .sqlvalue(@$_POST["Field1"], true).", `Field2`=" .sqlvalue(@$_POST["Field2"], true) ." where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
}

do u know why it would be happening
Thanks
0
 
Roger BaklundCommented:
The sqlvalue() function calls the sqlstr() function, and this is escaping strings wrong for mysql.  ' should be escaped as \', not as '' (two single quotes). The builtin function mysql_real_escape_string() does this as well as escaping some other special characters.
function sqlstr($val)
{
  return str_replace("'", "''", $val);
}
 
...should be 
 
function sqlstr($val)
{
  return mysql_real_escape_string($val);
}

Open in new window

0
 
syedasimmeesaqAuthor Commented:
perfect!

Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now