To update data that already exist in the database, the UPDATE statement is used.
In the below example we update the employee data from MySQL database.
we used 2 files for update data
- database.php- To connecting database.
- update.php- TO retrieve data from a database with an update option.
- update-process.php- TO update data from the database.
1. database
CREATE TABLE `employee` (
`userid` int(8) NOT NULL,
`first_name` varchar(55) NOT NULL,
`last_name` varchar(55) NOT NULL,
`city_name` varchar(55) NOT NULL,
`email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
2. database.php
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "db_php_test";
$conn=mysqli_connect($servername,$username,$password, "$dbname");
if(!$conn)
{
die('Could not Connect My Sql:' .mysql_error());
}
?> |
3. Insert.php
<?php
include_once 'process.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP INSERT</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Library -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"/>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js" integrity="sha384-vFJXuSJphROIrBnz7yo7oB41mKfc8JzQZiCq4NCceLEaO4IHwicKwpJf9c9IpFgh" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js" integrity="sha384-alpBpkh1PFOepccYVYDB4do5UnbKysX5WZXm3XxPqe5iKTfUKjNkCk9SaVuEZflJ" crossorigin="anonymous"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/additional-methods.min.js"></script>
</head>
<body>
<style>
.form-control, .btn,.container {
border-radius: 1px;
}
</style>
<br>
<br>
<br>
<div class="container"> <!-- Container -->
<div class="shadow p-3 mb-5 bg-white rounded"><!-- Regular shadow -->
<h3 class="mt33">PHP INSERT</h3>
<h5 class="text-center text-success" id="message"><?= $sucess ;?></h5>
<br>
<form class="mt33" action="insert.php" method = "post"><!--form -->
<!-- First name -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">First name *</label>
<div class="col-sm-9">
<input type="text" class="form-control" id="first_name" name="first_name" placeholder="Enter first name" required>
</div>
</div>
<!-- last name -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">Last name *</label>
<div class="col-sm-9">
<input type="text" class="form-control" id="last_name" name="last_name" placeholder="Enter last name" required>
</div>
</div>
<!-- City name -->
<div class="form-group row">
<label for="rootCause" class="control-label col-sm-3">City name *</label>
<div class="col-sm-9">
<select class="custom-select" id="city_name" name="city_name" aria-label="city_name" required>
<option value=""> Plase select city name</option>
<option value="Toky">Toky</option>
<option value="Phnom Penh">Phnom Penh</option>
<option value="USA">USA</option>
</select>
</div>
</div>
<!-- Email -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">Email *</label>
<div class="col-sm-9">
<input type="email" class="form-control" id="email" name="email" placeholder="Enter email" required>
</div>
</div>
<!-- Show Message -->
<div class="text-success text-center d-none" id="msg_div">
<h4 id="res_message">Insert has been successfully.</h4>
</div>
<!-- btn insert data -->
<div class="form-group row">
<div class="offset-sm-3 col-sm-9 pull-right">
<button type="submit"id="save" name="save" class="btn btn-primary">Save</button>
</div>
</div>
</form><!--end form -->
</div><!--end container -->
</div><!-- Regular shadow -->
<script>
$(document).ready(function()
{
setTimeout(function()
{
$('#message').hide();
},3000);
});
</script>
</body>
</html>
|
4. process.php
<?php
include_once 'database.php';
$sucess = "";
if(isset($_POST['save']))
{
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$city_name = $_POST['city_name'];
$email = $_POST['email'];
$sql = "INSERT INTO employee (first_name,last_name,city_name,email)
VALUES ('$first_name','$last_name','$city_name','$email')";
if (mysqli_query($conn, $sql))
{
$sucess = "Insert has been successfully.!";
}
else
{
echo "Error: " . $sql . "
" . mysqli_error($conn);
}
mysqli_close($conn);
}
?>
<?php
include_once 'database.php';
$sucess = "";
if(isset($_POST['save']))
{
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$city_name = $_POST['city_name'];
$email = $_POST['email'];
$sql = "INSERT INTO employee (first_name,last_name,city_name,email)
VALUES ('$first_name','$last_name','$city_name','$email')";
if (mysqli_query($conn, $sql))
{
$sucess = "Insert has been successfully.!";
}
else
{
echo "Error: " . $sql . "
" . mysqli_error($conn);
}
mysqli_close($conn);
}
?> |
5. update-process.php
<?php
include_once 'database.php';
$city_name = mysqli_query($conn, "SELECT DISTINCT city_name FROM employee");
$update = '';
if(count($_POST)>0)
{
mysqli_query($conn,"UPDATE employee set first_name='" . $_POST['first_name'] . "', last_name='" . $_POST['last_name'] . "', city_name='" . $_POST['city_name'] . "' ,email='" . $_POST['email'] . "' WHERE id='" . $_GET['id'] . "'");
$update = "Update has been successfully.!";
}
$edite = mysqli_query($conn,"SELECT * FROM employee WHERE id='" . $_GET['id'] . "'");
$row= mysqli_fetch_array($edite);
?>
<html>
<head>
<title>Update Employee Data</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Library -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"/>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js" integrity="sha384-vFJXuSJphROIrBnz7yo7oB41mKfc8JzQZiCq4NCceLEaO4IHwicKwpJf9c9IpFgh" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js" integrity="sha384-alpBpkh1PFOepccYVYDB4do5UnbKysX5WZXm3XxPqe5iKTfUKjNkCk9SaVuEZflJ" crossorigin="anonymous"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/additional-methods.min.js"></script>
</head>
<body>
<style>
.form-control, .btn,.container {
border-radius: 1px;
}
</style>
<br>
<br>
<br>
<div class="container"> <!-- Container -->
<div class="shadow p-3 mb-5 bg-white rounded"><!-- Regular shadow -->
<h3 class="mt33">PHP INSERT</h3>
<a href="retrieve.php">Back to list</a>
<br>
<br>
<h5 class="text-center text-success" id="update"><?= $update ;?></h5>
<form class="mt33" action="" method = "post"><!--form -->
<!-- First name -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">First name *</label>
<div class="col-sm-9">
<input type="text" class="form-control" id="first_name" name="first_name" value="<?=$row['first_name']; ?>" required>
</div>
</div>
<!-- last name -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">Last name *</label>
<div class="col-sm-9">
<input type="text" class="form-control" id="last_name" name="last_name" value="<?= $row['last_name']; ?>" required>
</div>
</div>
<!-- City name -->
<div class="form-group row">
<label for="rootCause" class="control-label col-sm-3">City name *</label>
<div class="col-sm-9">
<select class="custom-select" id="city_name" name="city_name" aria-label="city_name" required>
<?php foreach($city_name as $rows):?>
<option value="<?php echo $rows['city_name']; ?>"<?php if($row['city_name'] == $rows['city_name']) echo 'selected="selected"'; ?>><?php echo $rows['city_name']; ?></option>
<?php endforeach;?>
</select>
</div>
</div>
<!-- Email -->
<div class="form-group row">
<label for="description" class="control-label col-sm-3">Email *</label>
<div class="col-sm-9">
<input type="email" class="form-control" id="email" name="email" value="<?= $row['email']; ?>" required>
</div>
</div>
<!-- Show Message -->
<div class="text-success text-center d-none" id="msg_div">
<h4 id="res_message">Insert has been successfully.</h4>
</div>
<!-- btn insert data -->
<div class="form-group row">
<div class="offset-sm-3 col-sm-9 pull-right">
<button type="submit"id="submit" name="submit" class="btn btn-primary">Update</button>
</div>
</div>
</form><!--end form -->
</div><!--end co
<!-- js -->
<script>
$(document).ready(function()
{
setTimeout(function()
{
$('#update').hide();
},3000);
});
// ---------------------------------------------------
// function data table
$(document).ready(function ()
{
$('#tableHorizontalWrapper').DataTable(
{
"scrollX": true
});
$('.dataTables_length').addClass('bs-select');
});
</script>
</body>
</html> |
7. delete-process.php
<?php
include_once 'database.php';
$sql = " DELETE FROM employee WHERE id='" . $_GET["id"] . "'";
if (mysqli_query($conn, $sql))
{
$delete = "Record deleted successfully";
header('Location:retrieve.php');
}
else
{
$delete = "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
|
8. retrieve.php.php
<?php
include_once 'database.php';
$update = '';
$delete = '';
$result = mysqli_query($conn, "SELECT * FROM employee");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Retrive</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- library -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap4.min.css">
<!-- library bootstrap -->
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>
<br>
<br>
<div class="container">
<div class="container-fluid">
<h5 class="text-center text-success" id="update"><?= $update ;?></h5>
<h5 class="text-center text-success" id="delete"><?= $delete ;?></h5>
<table id="tableHorizontalWrapper" class="table table-striped table-bordered table-sm text-center" cellspacing="0"width="100%">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>City Name</th>
<th>Email</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
while($row = mysqli_fetch_array($result))
{
?>
<tr>
<td><?= $row['id'];?></td>
<td><?= $row['first_name'];?></td>
<td><?= $row['last_name'];?></td>
<td><?= $row['city_name'];?></td>
<td><?= $row['email'];?></td>
<td><a href="update-process.php?id=<?php echo $row["id"]; ?>">Edite</a></td>
<td><a href="delete-process.php?id=<?php echo $row["id"]; ?>" onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
</tr>
<?php
}
// close connection database
mysqli_close($conn);
?>
</tbody>
</table>
</div>
</div>
<!-- js -->
<script>
$(document).ready(function()
{
setTimeout(function()
{
$('#update').hide();
},3000);
});
$(document).ready(function()
{
setTimeout(function()
{
$('#delete').hide();
},3000);
});
// ---------------------------------------------------
// function data table
$(document).ready(function ()
{
$('#tableHorizontalWrapper').DataTable(
{
"scrollX": true
});
$('.dataTables_length').addClass('bs-select');
});
</script>
</body>
</html>
|
0 Comments
CAN FEEDBACK
Emoji