Solved

Form save button to update all records at once

Posted on 2008-10-21
10
349 Views
Last Modified: 2013-12-13
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
Comment
Question by:syedasimmeesaq
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22772387
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
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 22772451
i tried that and it didn't update anything
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22772502
Post your updated code, complete.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 22779021
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22780300
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
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 22780594
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22780827
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
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 22782126
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 22782182
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
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 22787963
perfect!

Thanks
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question