Add, Edit, Delete with data table using PDO in PHP/MySQL

Submitted by alpha_luna on Saturday, May 14, 2016 - 10:54.

This simple project is created using PDO or it's called PHP Data Objects and it's a database driven using MySQL as a database. This project is intended for beginners in using PDO. It has a basic code so everyone can easily to understand and learn.

Creating our Table

We are going to make our database.

Open the PHPMyAdmin.
Create a database and name it as "add_pdo".
After creating a database name, then we are going to create our table. And name it as "student".
Kindly copy the code below.

CREATE TABLE IF NOT EXISTS `student` (
  `student_id` INT(100) NOT NULL AUTO_INCREMENT,
  `fname` VARCHAR(100) NOT NULL,
  `mname` VARCHAR(100) NOT NULL,
  `lname` VARCHAR(100) NOT NULL,
  `address` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Database Connection

<?php $conn = new PDO('mysql:host=localhost; dbname=add_pdo','root', ''); ?>

Add, Update, and Delete in PDO Query

Modal Add Form Result

                                        <form method="post" action="add.php"  enctype="multipart/form-data">
                                        <table class="table1">
                                                <tr>
                                                        <td><label style="color:#3a87ad; font-size:18px;">FirstName</label></td>
                                                        <td width="30"></td>
                                                        <td><input type="text" name="fname" placeholder="FirstName" required /></td>
                                                </tr>
                                                <tr>
                                                        <td><label style="color:#3a87ad; font-size:18px;">MiddleName</label></td>
                                                        <td width="30"></td>
                                                        <td><input type="text" name="mname" placeholder="MiddleName" required /></td>
                                                </tr>
                                                <tr>
                                                        <td><label style="color:#3a87ad; font-size:18px;">LastName</label></td>
                                                        <td width="30"></td>
                                                        <td><input type="text" name="lname" placeholder="LastName" required /></td>
                                                </tr>
                                                <tr>
                                                        <td><label style="color:#3a87ad; font-size:18px;">Address</label></td>
                                                        <td width="30"></td>
                                                        <td><input type="text" name="address" placeholder="Address" required /></td>
                                                </tr>
                                                <tr>
                                                        <td><label style="color:#3a87ad; font-size:18px;">Email</label></td>
                                                        <td width="30"></td>
                                                        <td><input type="email" name="email" placeholder="Email" required /></td>
                                                </tr>
                                                
                                        </table>
                                        
        
    </div>
    <div class="modal-footer">
    <button class="btn" data-dismiss="modal" aria-hidden="true">Close</button>
<button type="submit" name="Submit" class="btn btn-primary">Add</button>
    </div>
        
 
                                        </form>

Add PDO Query

<?php
require_once('db.php');
 
$fname= $_POST['fname'];
$mname= $_POST['mname'];
$lname= $_POST['lname'];
$address= $_POST['address'];
$email= $_POST['email'];
 
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO student (fname, mname, lname, address, email)
VALUES ('$fname', '$mname', '$lname', '$address', '$email')";
 
$conn->exec($sql);
echo "<script>alert('Account successfully added!'); window.location='index.php'</script>";
?>

Result After Adding Data Result

Edit Form Result

<?php
include('db.php');
$result = $conn->prepare("SELECT * FROM student where student_id='$ID'");
$result->execute();
for($i=0; $row = $result->fetch(); $i++){
$id=$row['student_id'];
?>
<form class="form-horizontal" method="post" action="edit_PDO.php<?php echo '?student_id='.$id; ?>"  enctype="multipart/form-data" style="float: right;">
                                <legend><h4>Edit</h4></legend>
                                <h4>Personal Information</h4>
                                <hr>
                                                                <div class="control-group">
                                    <label class="control-label" for="inputPassword">FirstName:</label>
                                    <div class="controls">
                                        <input type="text" name="fname" required value=<?php echo $row['fname']; ?>>
                                    </div>
                                </div>
                                                                <div class="control-group">
                                    <label class="control-label" for="inputPassword">MiddleName:</label>
                                    <div class="controls">
                                        <input type="text" name="mname" required value=<?php echo $row['mname']; ?>>
                                    </div>
                                </div>
                                <div class="control-group">
                                    <label class="control-label" for="inputEmail">LastName:</label>
                                    <div class="controls">
                                        <input type="text" name="lname" required value=<?php echo $row['lname']; ?>>
                                    </div>
                                </div>
                                <div class="control-group">
                                    <label class="control-label" for="inputPassword">Address:</label>
                                    <div class="controls">
                                        <input type="text" name="address" required value=<?php echo $row['address']; ?>>
                                    </div>
                                </div>
                                <div class="control-group">
                                    <label class="control-label" for="inputPassword">Email:</label>
                                    <div class="controls">
                                        <input type="email" name="email" required value=<?php echo $row['email']; ?>>
                                    </div>
                                </div>
                                                                
                                                                 <div class="control-group">
                                    <div class="controls">
 
                                        <button type="submit" name="update" class="btn btn-success" style="margin-right: 65px;">Save</button>
                                                                                <a href="index.php" class="btn">Back</a>
                                    </div>
                                </div>
                            </form>
<?php } ?>

Edit PDO Query

<?php
include 'db.php';
 
$get_id=$_REQUEST['student_id'];
 
$fname= $_POST['fname'];
$mname= $_POST['mname'];
$lname= $_POST['lname'];
$address= $_POST['address'];
$email= $_POST['email'];
 
$sql = "UPDATE student SET fname ='$fname', mname ='$mname', lname ='$lname', 
address ='$address', email ='$email' WHERE student_id = '$get_id' ";
 
$conn->exec($sql);
echo "<script>alert('Successfully Edit The Account!'); window.location='index.php'</script>";
?>

Result After Edditing Data Result

Modal Delete Form Result

<div id="delete<?php  echo $id;?>" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-header">
<h3 id="myModalLabel">Delete</h3>
</div>
<div class="modal-body">
<p><div style="font-size:larger;" class="alert alert-danger">Are you Sure you want Delete <b style="color:red;"><?php echo $row['fname']." ".$row['mname']." ".$row['lname'] ; ?></b> Data?</p>
</div>
<hr>
<div class="modal-footer">
<button class="btn btn-inverse" data-dismiss="modal" aria-hidden="true">No</button>
<a href="delete.php<?php echo '?student_id='.$id; ?>" class="btn btn-danger">Yes</a>
</div>
</div>
</div>

Delete PDO Query

<?php
require_once('db.php');
 
$get_id=$_GET['student_id'];
 
// sql to delete a record
$sql = "Delete from student where student_id = '$get_id'";
 
// use exec() because no results are returned
$conn->exec($sql);
header('location:index.php');
?>

Result After Deleting Data Result

And, that's all, you can have Add, Edit, Delete with data table using PDO in PHP/MySQL or kindly click the "Download Code" button to download the full source code. This is the full source code for Inserting Data, Updating Data, and Deleting Data in MySQL. Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.

Comments

Why?!!!

Why is modal add form is incomplete?

Add, Edit, Delete Image with data table using PDO in PHP/MySQL

hi, do you know how to add a picture to the data. can you send me the same project with picture added please. thank you

I have tried this so many…

I have tried this so many times I cannot get this to work for me, I keep getting close, I have had to modify the DB.php to another one I found online cause this one didn't work at all for me, I have finally gotten to the point where the main page finds the records I have in the database, but it will not display them, the table says there are 14 entries, and there are but it will not display them. I am not a coder, but I have changed them.. https://bt.inokamanori.com/dataupdate1/index.php. I am at a loss. the error I am getting now is. [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 37 [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 40 [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 41 [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 42 [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 43 [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 44 [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 56 [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 56 [02-Feb-2022 18:51:47 UTC] PHP Warning: Trying to access array offset on value of type bool in /home/g88pa0itn9u7/bt.inokamanori.com/dataupdate1/index.php on line 56

Add new comment

Add new comment
6621 views