Thu, Nov 2024

How to edit data from PostgreSQL using PHP

By Mehul Kareliya 18 Nov, 2024 27 mins read 12 views

0 comments 0 likes

How to edit data from PostgreSQL using PHP

We learned how to use PHP to connect, insert , fetch data into PostgreSQL in the last tutorial. This article will teach you how to use PHP to get and read data that has been update/edit into PostgreSQL.

config.php: This is used to connect PHP to PostgreSQL databases.

read.php: This is where HTML tables are utilized, and we'll also place the PHP code for data fetch and read here.

The PostgreSQL database and table from the last tutorial will be used.

Workers' structure

Config.php

insert.php

edit.php

PHP code to retrieve data from PostgreSQL

Read / Fetch the particular record (getdata.php)

create getdata.php file. For fetching a record we have to get the row id of that record and store in $id. We access the $_GET[‘id’] variable to do it.

Code for gets a record based on the given id.


<table class="table table-striped mt-4">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Name</th>
                        <th>Email Id</th>
                        <th>Mobile No</th>
                        <th>Department</th>
                        <th>Creation Date</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
<?php $query= pg_query($conn,"select * from employee");
$cnt=1;
while($row=pg_fetch_array($query)){
?>

                    <tr>
                        <td><?php echo $cnt;?></td>
                        <td><?php echo $row['name'];?></td>
                        <td><?php echo $row['emailid'];?></td>
                        <td><?php echo $row['mobileno'];?></td>
                        <td><?php echo $row['department'];?></td>
                        <td><?php echo $row['creationdate'];?></td>
                        <td><a href="edit.php?id=<?php echo $row['id'];?>" class="btn btn-info btn-sm">Edit</td>
                    </tr>
                <?php $cnt++;} ?>
                    
                </tbody>
            </table>

Now Fetch the data in the HTML Form.

create edit.php file. For updating a record we have to get the row id of that record and store in $id. We access the $_GET[‘id’] variable to do it.
Code for gets a record based on the given id. Through this way, we can get data autofill-data in HTML Form.

<form method="post" class="form-horizontal">
<?php
$empid=$_GET['id'];
$query= pg_query($conn,"select * from employee where id='$id'");
$cnt=1;
while($row=pg_fetch_array($query)){
?>


        <div class="form-group row">
<label class="col-form-label col-4">Name</label>
<div class="col-8">
       <input type="text" class="form-control" name="name" value="<?php echo $row['empname'];?>" required="required">
            </div>        
        </div>

<div class="form-group row">
<label class="col-form-label col-4">Email</label>
<div class="col-8">
<input type="email" class="form-control" name="email" value="<?php echo $row['emailid'];?>" required="required">
            </div>        
        </div>

<div class="form-group row">
<label class="col-form-label col-4">Mobile</label>
<div class="col-8">
<input type="text" class="form-control" name="mobile" value="<?php echo $row['mobileno'];?>" required="required">
          </div>        
        </div>

<div class="form-group row">
<label class="col-form-label col-4">Department</label>
<div class="col-8">
<input type="text" class="form-control" name="dept" value="<?php echo $row['department'];?>" required="required">
            </div>        
        </div>

<div class="form-group row">
<label class="col-form-label col-4">Creation Date</label>
<div class="col-8">
<input type="text" class="form-control" name="cdate" value="<?php echo $row['creationdate'];?>" readonly>
  </div>        
   </div>
   <?php } ?>

<div class="form-group row">
<div class="col-8 offset-4">
<button type="submit" name="update" class="btn btn-primary btn-lg">Update</button>
</div>  
</div>
</form>

edit.php

<?php include_once('config.php');
if(isset($_POST['update']))
{
$ename=$_POST['name'];
$eemail=$_POST['email'];
$emobile=$_POST['mobile'];
$edept=$_POST['pdept'];
$empid=$_GET['id'];
// Execute the query with parameters
$result = pg_query($conn, "update employee set empname='$name',emailid='$email',mobileno='$mobile',department='$dept' where id='$id'");

if ($result) {
     echo '<script>alert("Employee Details edit successfully!")</script>';
    echo "<script type='text/javascript'> document.location = 'getdata.php'; </script>";
} else {
    echo "Error: " . pg_last_error($conn);
}
pg_close($conn);
}
?>