ADD, EDIT and DELETE
Prerequisite: Basic Understanding HTML, CSS, PHP and MySQL.
In this project:
- Add records to the database.
- Edit the information in added records.
- Delete a particular record.
index.php ⬇
<?php
include_once 'includes/conn.php';
if(isset($_GET['action']) && $_GET['action'] == "edit" && is_numeric($_GET['id'])) {
$id = $_GET['id'];
$sql = "SELECT * FROM records WHERE id = '".$id."'";
$exe = mysqli_query($con, $sql);
$rec = mysqli_num_rows($exe);
if ($rec > 0) {
$row = mysqli_fetch_assoc($exe);
$name = $row['name'];
$email = $row['email'];
$gender = $row['gender'];
$reviews = $row['reviews'];
$ID = $row['id'];
}
} else if(isset($_GET['action']) && $_GET['action'] == "delete" && is_numeric($_GET['id'])){
$id = $_GET['id'];
$sql = "DELETE FROM records WHERE id = '".$id."'";
$exe = mysqli_query($con, $sql);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>User Form</title>
<script src="js/jquery.min.js"></script>
<script>
function action(id, val){
$("#action_val").value = val;
document.getElementById('form_'+id).submit();
}
</script>
<style>
sup{color:red}
</style>
</head>
<body>
<div class="form__block" >
<?php
if(isset($_SESSION['msg']) && $_SESSION['msg'] != ''){
echo "<p>".$_SESSION['msg']."</p>";
unset($_SESSION['msg']);
}
?>
<form action="process.php" method="POST" id="Userform">
<label>Name<sup>*</sup></label>
<input type="text" name="name" id="name" value="<?=isset($name)?$name:''?>" />
<label>Email<sup>*</sup></label>
<input type="text" name="email" id="email" value="<?=isset($email)?$email:''?>" />
<label>Gender<sup>*</sup></label>
<input type="radio" name="gender" value="female" <?php if(!isset($gender) || ((isset($gender) && $gender == 'female'))){
echo 'checked="checked"';
}else{
echo '';
} ?>/> Female
<input type="radio" name="gender" value="male" <?php if(!isset($gender) || ((isset($gender) && $gender == 'male'))){
echo 'checked="checked"';
}else{
echo '';
} ?> /> Male<br>
<label>Reviews</label>
<textarea rows="3" cols="23" name="reviews" id="reviews">
<?=isset($reviews)?$reviews:''?>
</textarea>
<input type="hidden" name="recordID" value="<?=(isset($ID) && is_numeric($ID))?$ID : ''?>">
<input type="submit" name="submit" value="SUBMIT" id="form_submit">
</form>
</div>
<div style="margin-top: 50px">
<table cellpadding="10" border="1" cellspacing="0">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Gender</th>
<th>Reviews</th>
<th colspan="2">Action</th>
</tr>
<?php
$sql = "SELECT * FROM records ORDER BY id";
$result = mysqli_query($con, $sql);
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
echo '<tr>';
echo '<td>'.$row['id'].'</td>';
echo '<td>'.$row['name'].'</td>';
echo '<td>'.$row['email'].'</td>';
echo '<td>'.$row['gender'].'</td>';
echo '<td>'.$row['reviews'].'</td>';
echo '<td><input type="hidden" name="action_val" id="action_val" value="">';
echo '<a href="index.php?action=edit&id='.$row['id'].'")>Edit</a></td>';
echo '<td><a href="index.php?action=delete&id='.$row['id'].'")> Delete</a></td>';
echo '</tr>';
}
}
?>
</table>
</div>
</body>
</html>
Run index.php file in your browser to get the following output.
Code Explanation of index.php
• Connection file is included which includes the query to connect to the database.
•
• It is necessary to include jQuery file inorder to use jquery functions. You can download it form https://jquery.com.
DESCRIPTION1. A user form with 2. Each field is given an id and name attribute with required value. for example :
Where, • name input field is given name= • Name attributes are posted to the server while form submission and Id attribute is used to perform JS validation 3. Button is also given an id
We are posting the values via HTTP POST method. |
EXPLANATION OF EDIT RECORD CODE IN INDEX.PHP FILE
echo '<a href="index.php?action=edit&id='.$row['id'].'")>Edit</a></td>';
When user clicks the Edit link, the user is moved to index.php
file with two query string variables i.e. action=edit
and the primary ID of the record to be edited.
- If action=edit and an ID is set, then
if(isset($_GET['action']) && $_GET['action'] == "edit" && is_numeric($_GET['id'])) {
$id = $_GET['id'];
$sql = "SELECT * FROM records WHERE id = '".$id."'";
$exe = mysqli_query($con, $sql);
$rec = mysqli_num_rows($exe);
if ($rec > 0) {
$row = mysqli_fetch_assoc($exe);
$name = $row['name'];
$email = $row['email'];
$gender = $row['gender'];
$reviews = $row['reviews'];
$ID = $row['id'];
}
}
The particular record is fetched and the recorded values are stored in local variables which are further displayed in Userform. For example:
<input type="text" name="name" id="name" value="<?=isset($name)?$name:''?>" />
The record is updated using UPDATE command as is mentioned in process.php file.
EXPLANATION OF DELETE RECORD CODE IN INDEX.PHP FILE
echo '<a href="index.php?action=delete&id='.$row['id'].'")>Delete</a></td>';
When user clicks the Delete link, the user is moved to index.php file with two query string variables i.e. action=delete and the primary ID of the record to be deleted.
- If action=delete and an ID is set, then
if(isset($_GET['action']) && $_GET['action'] == "delete" && is_numeric($_GET['id'])){
$id = $_GET['id'];
$sql = "DELETE FROM records WHERE id = '".$id."'";
$exe = mysqli_query($con, $sql);
}
The record with that particular primary ID is deleted using DELETE command.
Code Explanation of process.php
DESCRIPTION
if submit button of Userform is pressed then the control is transferred to the if block.
If the edit button is pressed and we already have posted the primary ID, then the update command will be executed otherwise it will insert a new row. Values are inserted in users tables according to the syntax: INSERT INTO tablename (columns…) VALUES (values….) |
Summary
In this project, we have learned to add, edit and delete records using PHP.
- HTML and CSS are used to create the layout of webpage.
- Userform is used to add new and edit the old records in the database.
- Entered records displayed in table with edit and delete button at the end of each record.
- Messages are displayed on webpage accordingly.