How to update database in php

[Roldan_1]

Hello to all, i have my problem using php in updating mysql database, i have codes here. my problem is, when i click update, all data update altogether, i dont know what is wrong on my codes, your help is much appreciated.

   <?php

     if(isset($_POST['req_code'])){

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "elev8groupportal";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 



$req_date = 'req_status';


$sql = "UPDATE request SET req_status='Waiting for Note', req_dateapprove = NOW() WHERE req_code='".$dn1['req_code']."' ";


if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();

}

     ?>    
              <div class="content">  
                <form method="POST" name="update">  
                   <label>Date Created: </label><input type="text" name="req_code" id="req_code" value="<?php echo ($dn1['req_code']); ?>" /><br />
                   <label>Date Created: </label><input type="test" name="req_date" id="req_date" value="<?php echo ($dn1['req_date']); ?>" /><br />
                   <label>Request By: </label><input type="text" name="empname" id="empname" value="<?php echo ($dn1['empname']) ?>" /><br />
                   <label>Form Name: </label><input type="text" name="form_name" id="form_name" value="<?php echo $dn1['form_name']?>" /><br />
                   <label>Request Type: </label><input type="text" name="itrf_type" id="itrf_type" value="<?php echo $dn1['itrf_type']?>" /><br />
                   <label>Details: </label><input type="text" name="itrf_details" id="itrf_details" value="<?php echo $dn1['itrf_details']?>" /><br />
                   <label> Date Needed:</label><input type="text" name="req_dateneeded" id="req_dateneeded" value="<?php echo $dn1['req_dateneeded']?>" /><br />

                  <button type="submit" class="btn btn-primary" name="req_code" >Approved</button>
                  <button type="submit" class="btn btn-primary" name="update2">Disapproved</button>

                 </form> 

[philjen]

check your where variable.. $dn1['req_code'] where did you declare this one..?

[Roldan_1]

  HERE is my full codes 

    <h3 class="box-title">List of Pending Approval</h3>
                </div><!-- /.box-header -->
                <div class="box-body">
                  <table id="example2" class="table table-bordered table-hover">
                    <thead>
                      <tr>
                        <th>Date of Request</th>
                        <th>Requestor Name</th>
                        <th>Form Type</th>
                        <th>Request Type</th>
                         <th>Details</th>
                         <th>Date Needed</th>
                          <th>Status</th>
                        <th>Action</th>

                      </tr>
                    </thead>

                <?php
                      if(isset($_GET['req_code'])){


                      }




                    $req1 = mysql_query('select * from mainviewrequestor where username="'.$_SESSION['username'].'"  and req_dateapprove IS NULL');
                      //where empname="'.$_SESSION['username'].'"  and req_dateapprove IS NULL');
//where req_approveuser="'.$_SESSION['username'].'" and req_dateapprove IS NULL'
while($dn1 = mysql_fetch_array($req1))
{
?>
<tr>

       <td><?php echo date($dn1['req_date']); ?></td>
      <td class="left"><?php echo htmlentities($dn1['empname'], ENT_QUOTES, 'UTF-8'); ?></a></td>
      <td><?php echo htmlentities($dn1['form_name'], ENT_QUOTES, 'UTF-8'); ?></td>
      <td> <?php echo ($dn1['itrf_type']);?></td>
      <td> <?php echo ($dn1['itrf_details']);?></td>
      <td> <?php echo ($dn1['req_dateneeded']);?></td>
      <td> <?php echo ($dn1['req_status']);?></td>

       <td class="left"><a href="#proceed-<?php echo $dn1['req_code'];?>" data-toggle="modal"><button type="submit" class="btn btn-primary" name="approved">Proceed</button></a>


   </tr>

   <div class = "modal fade" id="proceed-<?php echo $dn1['req_code'];?>" role="dialog" >
  <div class = "modal-dialog modal-lg">
       <div class = "modal-content">
          <div class = "modal-header">
              <h4>REQUEST TABLES</h4>
                <h2>------------------------------------------------------------------------------------------</h2>


               <?php
               $servername = "localhost";
$username = "root";
$password = "";
$dbname = "elev8groupportal";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 


//$req_code = $_POST['req_code'];
//$req_code = $_GET['req_code'];
$req_date = 'req_status';


$sql = "UPDATE request SET req_status='Waiting for Note', req_dateapprove = NOW() WHERE req_code='".$dn1['req_code']."' ";
//WHERE req_code= '".$_POST['req_code']."' and req_dateapprove = NOW() ";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();

}
 ?>
           <?php

           ?>      
              <div class="content">  
                <form method="POST" name="update">  
                   <label>Date Created: </label><input type="text" name="req_code" id="req_code" value="<?php echo ($dn1['req_code']); ?>" /><br />
                   <label>Date Created: </label><input type="test" name="req_date" id="req_date" value="<?php echo ($dn1['req_date']); ?>" /><br />
                   <label>Request By: </label><input type="text" name="empname" id="empname" value="<?php echo ($dn1['empname']) ?>" /><br />
                   <label>Form Name: </label><input type="text" name="form_name" id="form_name" value="<?php echo $dn1['form_name']?>" /><br />
                   <label>Request Type: </label><input type="text" name="itrf_type" id="itrf_type" value="<?php echo $dn1['itrf_type']?>" /><br />
                   <label>Details: </label><input type="text" name="itrf_details" id="itrf_details" value="<?php echo $dn1['itrf_details']?>" /><br />
                   <label> Date Needed:</label><input type="text" name="req_dateneeded" id="req_dateneeded" value="<?php echo $dn1['req_dateneeded']?>" /><br />

                  <button type="submit" class="btn btn-primary" name="req_code" >Approved</button>
                  <button type="submit" class="btn btn-primary" name="update2">Disapproved</button>

                 </form> 


                </div>


    <?php





}

if(intval(mysql_num_rows($req1))==0)
{
?>
  <tr>
      <td colspan="4" class="center">You have no unread message.</td>
    </tr>
<?php
}
?>
</table>
<br />

<?php
}
?>


                  </table>
                </div><!-- /.box-body -->
              </div><!-- /.box -->



            </div><!-- /.col -->
          </div><!-- /.row -->
        </section><!-- /.content -->
      </div><!-- /.content-wrapper -->

     <?php





{

                 ?>


 <?php
}

if(intval(mysql_num_rows($req1))==0)
{
?>

<?php
}
?>
<?php

?>

[lps]

Check your query with database structure. Check if database column of 'req_code' is unique. If it is not unique, it will tempt to update all the records that matched the req_code.

[Gideon_1]

First of all, why are you using two database connection layers in one script; mysql and mysqli. Please, I encorage you to use mysqli since mysql is deprecated.

Also I think you should be precise of the form method you are using so that you don't just use $_GET or $_POST.

But to your question, it seems you are updating the table against a field called req_code. I will advise you to update your database tables using primary keys (usually id's are used as primary keys in the database).

In your example, it can be possible that, that req_code is the same across all fields in the database. But for id's (primary key), it can never be the same.

So if you don't have an id or primary key field, you go ahead and create one and then update your rows where id = userid.