commit after update transaction

Question

I'm new to the php. I have created a update form as follows. I need to update the following fields.

Category,Short Description,Full Description. It's happening partially i.e, If I update only category field then remaining fields will go blank. So how to do it? Any help would be appreciated. Step 1. In view.php when the user click on Edit button it will go to the updateview.php.

Step 2. In updateview.php when the user changes any field value and press the update button it will go to the update.php

Step 3. From update.php it will return back to the view.php with updated value.

Thanks

View.php

<table id="example" class="row-border" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>SRN</th>
                <th>Client</th>
                <th>Category</th>
                <th>Short Description</th>
                <th>Full Description</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
        <?php while($row = mysql_fetch_array($selectQ)){ ?>
         <tr>
            <td><?php echo $row['srn'];?></td>
            <td><?php echo $row['client'];?></td>
            <td><?php echo $row['category'];?></td>
            <td><?php echo $row['sd'];?></td>
            <td><?php echo $row['fd'];?></td>
            <td><a href="updateview.php?srn=<?php echo $row['srn']; ?>" target="_blank">Edit</a></td>
    </tr>
        <?php } ?>
        </tbody>
    </table>

dbconn.php

<?php
$username = "root";
$password = "root";
$hostname = "localhost"; 
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");
$selected = mysql_select_db("eservice",$dbhandle) 
  or die("Could not select database");
?>

updateview.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head>
<div id="main-content">
<fieldset>
<?php
if(isset($_SESSION['example']))
{
echo $_SESSION['example'];
}
else
{
echo "Session destroyed..";
}
?>
</div>
<?php
include_once('dbconn.php');
$srn = $_GET['srn'];
$selQ = "Select * from main where srn = '".$srn."'";
$selectQ = mysql_query($selQ);
?>
<?php
      while($row = mysql_fetch_array($selectQ)){ ?>
<form action="update.php" method="post" enctype="multipart/form-data" novalidate>
<div class="item">
    <label> <span>SRN</span>
<input name="srn" type="text" id="srn" size="15" readonly="readonly" maxlength="40" value="<?php echo $row['srn']; ?>"/>
    </label>
    </div>
    <div class="item">
    <label> <span>Client</span>
     <select class="required" name="client"  value="<?php echo $row['client']; ?>" disabled="disabled"/>
                            <?php include_once('dbconn.php'); ?>
                  <option value=""><?php echo $row['client']; ?></option>
             <?php
mysql_connect ("localhost","root","");
                    mysql_select_db ("eservice");
                    $select="eservice";
                    if (isset ($select)&&$select!="")
{
                        $select=$_POST ['NEW'];
}
?>
<?php
                    $list=mysql_query("select * from client");
                    while($row_list=mysql_fetch_assoc($list))
{
?>
          <?php $ct = $row_list['cname'];?>
          <option value="<?php echo $ct; ?>"<?php if($ct==$select){ echo "selected"; } ?> > <?php echo $ct; ?></option>
          <?php } ?>
      </select>
    <input type="hidden" name="client" value = "<?php echo $row['client']; ?>" />
    </label>
</div>
    <div class="item">
    <label> <span>Category</span>
         <select class="required" name="category"  value="<?php echo $row['category']; ?>"/>
                            <?php include_once('dbconn.php'); ?>
                  <option value=""><?php echo $row['category']; ?></option>
             <?php
mysql_connect ("localhost","root","");
                    mysql_select_db ("eservice");
                    $select="eservice";
                    if (isset ($select)&&$select!="")
{
                        $select=$_POST ['NEW'];
}
?>
          <?php
                    $list=mysql_query("select * from category");
                    while($row_list=mysql_fetch_assoc($list))
}
?>
          <?php $ct = $row_list['name'];?>
          <option value="<?php echo $ct; ?>"<?php if($ct==$select){ echo "selected"; } ?> > <?php echo $ct; ?></option>
          <?php } ?>
  </select>
    </label>
</div>
<div class="item">
<label> <span>Short Description</span>
    <textarea required="required" name='sd'><?php echo $row['sd']; ?></textarea>
</div>
<div class="item">
<label> <span>Full Description</span>
    <textarea required="required" name='fd'><?php echo $row['fd']; ?></textarea>
</div>
<div class="item">
<button id='cancel' type='cancel'>Cancel</button>
<button id='send' type='submit'>Update</button>
</div>
</form>
<?php } ?>

update.php

<?php
include_once('dbconn.php');
$srn          = $_POST['srn'];
$client       = $_POST['client']; //required
$cate         = $_POST['category'];
$sd           = $_POST['sd']; //required
$fd           = $_POST['fd']; //required

$updQry = "Update main Set client = '".$client."',category = '".$cate."',sd= '".$sd."',fd= '".$fd."' where srn = '".$srn."'";
$updateQ = mysql_query($updQry);
header("Location: view.php?res=U");
?>

Do not use the `mysql_*` functions these API is deprecated. you should also use prepared statements. — Jens, Feb 11 '15 at 06:42
read the [manual](http://php.net/manual/de/mysqli.quickstart.prepared-statements.php) — Jens, Feb 11 '15 at 06:59

score 0 · Answer 1 · edited May 23 '17 at 10:24

Note:

Make sure your srn column is unique. You call again your database connection inside your updateview.php, by include and by all of its code.

include_once('dbconn.php');

mysql_connect ("localhost","root","");
                    mysql_select_db ("eservice");

Wherein, your dbconn.php username and password is both root, but inside your updateview.php, username is root but no password is indicated. And you call it inside your while loop.

I think, there is no problem with your update query, it just uses the old deprecated mysql_* functions and it is prone to SQL injections. You should be using mysqli_* prepared statement or PDO instead.

Recommendation:

I've redone your code to much more recommendable mysqli_* prepared statement. Be patient to understand, but it is easy.

Your dbconn.php:

<?php

$mysqli = new mysqli("localhost", "root", "root", "eservice");

/* ESTABLISH CONNECTION */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>

Your updateview.php

<form action="update.php" method="post" enctype="multipart/form-data" novalidate>

<?php
include_once('dbconn.php');
$srn = $_GET['srn'];

  if($stmt = $mysqli->prepare("SELECT srn, client, category, sd, fd FROM main WHERE srn=?")){

    $stmt->bind_param("s",$_GET["srn"]);
    $stmt->execute();
    $stmt->bind_result($srn,$client,$category,$sd,$fd);
    $stmt->fetch();
    $stmt->close();

  }

  ?>

<div class="item">
  <label> <span>SRN</span>
  <input name="srn" type="text" id="srn" size="15" readonly="readonly" maxlength="40" value="<?php echo $srn; ?>"/>
  </label>
</div>

<div class="item">
  <label> <span>Client</span>    
  <select class="required" name="client"/>
  <?php

    if($stmt = $mysqli->prepare("SELECT cname FROM client")){

      $stmt->execute();
      $stmt->bind_result($cname);

      while($stmt->fetch()){

        ?>
          <option value="<?php echo $cname; ?>" <?php if($cname==$client){ echo "selected"; } ?>> <?php echo $cname; ?> </option>
        <?php

      } /* END OF WHILE LOOP */

      $stmt->close();

    } /* END OF PREPARED STATEMENT OF CLIENT */

  ?>
  </select>
  </label>
</div>

<div class="item">
  <label> <span>Category</span>      
  <select class="required" name="category"/>
  <?php

    if($stmt = $mysqli->prepare("SELECT name FROM category")){

      $stmt->execute();
      $stmt->bind_result($name);

      while($stmt->fetch()){

        ?>
          <option value="<?php echo $name; ?>" <?php if($name==$category){ echo "selected"; } ?>> <?php echo $name; ?> </option>
        <?php

      } /* END OF WHILE LOOP */

      $stmt->close();

    } /* END OF PREPARED STATEMENT OF CATEGORY */

  ?>
  </select>
  </label>
</div>

<div class="item">
<label> <span>Short Description</span>
    <textarea required="required" name='sd'><?php echo $sd; ?></textarea>
</div>
<div class="item">
<label> <span>Full Description</span>
    <textarea required="required" name='fd'><?php echo $fd; ?></textarea>
</div>
<div class="item">
<button id='cancel' type='cancel'>Cancel</button>
<button id='send' type='submit'>Update</button>
</div>
</form>

update.php

<?php

    include('dbconn.php');

    $stmt = $mysqli->prepare("UPDATE main SET client=?, category=?, sd=?, fd=? WHERE srn=?");

    $stmt->bind_param('sssss', $_POST["client"], $_POST["category"], $_POST["sd"], $_POST["fd"], $_POST["srn"]);

    $stmt->execute();

?>

Now I'm working on it. Once I get the error I'll get back to you. — Kiran, Feb 13 '15 at 07:41

commit after update transaction

1 Answers1

Note:

Recommendation: