Solved

Form save button to update all records at once

Posted on 2008-10-21
10
334 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
Comment Utility
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
Comment Utility
i tried that and it didn't update anything
0
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
Post your updated code, complete.
0
 
LVL 9

Author Comment

by:syedasimmeesaq
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Author Comment

by:syedasimmeesaq
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
perfect!

Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mail Not Sent 6 41
Wordpress syntax error 1 27
Converting SQL server date to string 3 15
sql sentence 2 8
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.  …
This article discusses how to create an extensible mechanism for linked drop downs.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now