Following is best exmple add,update and delete record using PDO :
Download project code link :
Create following file structure :
1-connection.php,
2-header.php,
3-footer.php
4-add.php,
5-edit.php,
6-add-action.php,
7-home.php,
8-delete.php
now connection file created connection bewteen mysql and php,he DSN (Data Source Name)contains the driver name (mysql),he last thing you might have noticed is that we wrapped our object initialization attempt in a try-catch block. PDO will throw a PDOException
following connection are created connection :-
Code:
<?php
/*
* Start:: Database connection::-
*/
$hostname="localhost";
$username="root";
$password="";
$dbname="testPDO";
try {
$db = new PDO("mysql:host=$hostname;dbname=$dbname;charset=utf8mb4", $username, $password);
}
catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
/*
* End:: Database connection::-
*/
?>
Above code is hostname is DSN (data source name), if the connection not established then automatic throw exception in catch function :
Function :-
$e->getMessage();
2-header.php
header.php file contain header code and database file connection.php and contain header title and css and js of bootstrap file with CND link:
Code:
<?php
/*
*include connection file and fetach if you want record::-
*/
include('connection.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PDO CRUD Operation</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.1.1.js" integrity="sha256-16cdPddA6VdVInumRGo6IbivbERE8p7CQR3HzTBuELA=" crossorigin="anonymous"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
</head>
3-footer.php
footer file contain error message link and end of html,body tags,
Code:
<script>
// $(".errorMsg").alert('close');
$(".errorMsg").slideUp(2000);
</script>
</body>
</html>
4-home.php
home.php file generate report or listing of fetch data form database using pdo method,
fetch data from using pdo fetchall() method, fetchall method execute query with associated data result,
<?php
$stmt = $db->query('SELECT * FROM student');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
associated array gets key and key name with value, and header store error message result after query add,update and delete at the top of header file
Code:
<?php
session_start();
if (!empty($_SESSION['statusMsg'])) {
echo '<p>' . $_SESSION['statusMsg'] . '</p>';
unset($_SESSION['statusMsg']);
}
?>
following code is home.php files
Code:
<?php include('header.php'); ?>
<?php
session_start();
if (!empty($_SESSION['statusMsg'])) {
echo '<p>' . $_SESSION['statusMsg'] . '</p>';
unset($_SESSION['statusMsg']);
}
$stmt = $db->query('SELECT * FROM student');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<body>
<div class="container">
<div class="alert alert-success">
<a href="#">PDO in PHP</a>!
<a class="pull-right" href="add.php"><i class="glyphicon glyphicon-plus"></i></a>
</div>
</div>
<div class="clearfix"></div>
<div class="container">
<?php if (!empty($results)) { ?>
<table class='table table-bordered table-responsive'>
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Contact No</th>
<th colspan="2" align="center">Actions</th>
</tr>
<?php
//SQL::fecth all infroamtion of studnet using fetch_assoc array result:
foreach ($results as $row) {
$i = 1;
?>
<tr>
<td><?php print($i); ?></td>
<td><?php print($row['std_fname']); ?></td>
<td><?php print($row['std_lname']); ?></td>
<td><?php print($row['std_address']); ?></td>
<td><?php print($row['std_mobile']); ?></td>
<td align="center">
<a href="edit.php?edit_id=<?php print($row['pk_id']); ?>"><i class="glyphicon glyphicon-edit"></i></a>
</td>
<td align="center">
<a href="delete.php?delete_id=<?php print($row['pk_id']); ?>"><i class="glyphicon glyphicon-remove-circle"></i></a>
</td>
</tr>
<?php
} $i++;
?>
</table>
<?php } else {
?>
<div class="alert alert-danger" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Sorry!</strong> No more record avaliable.
Please click here to <a href="add.php">add </a> record
</div>
<?php } ?>
</div>
<?php include('footer.php'); ?>
5-add.php
add.php files only text box and particular fileds,its simple part not exmplain more and more only add.php file store hidden filed action type store value of which type of form fill such as add or edit filed,
<input type="hidden" name="action_type" value="add"/>
Code:
<?php include('header.php');?>
<?php
session_start();
if(!empty($_SESSION['statusMsg'])){
echo '<p>'.$_SESSION['statusMsg'].'</p>';
unset($_SESSION['statusMsg']);
}
?>
<body>
<div class="container">
<div class="alert alert-success">
<a href="#">PDO in PHP</a>!
</div>
</div>
<div class="clearfix"></div>
<div class="container">
<div class="row col-lg-10">
<div class="panel panel-default user-add-edit">
<div class="panel-heading">Add Student </div>
<ul class="pager">
<li class="previous">
<a href="home.php">← Back</a>
</li>
</ul>
<div class="panel-body">
<form class="form-horizontal" action="add-action.php" method="post">
<div class="form-group">
<label for="inputEmail3" class="col-sm-2 control-label">First Name</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="firstname" name="firstname" placeholder="First name">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Last Name</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="lastname" name="lastname" placeholder="Last name">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Address</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="address" name="address" placeholder="Address">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Mobile no</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="mobile" name="mobile" placeholder="Mobile no">
</div>
</div>
<input type="hidden" name="action_type" value="add"/>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" class="btn btn-primary" name="submit" value="Submit"/>
<input type="reset" class="btn btn-default" value="Cancel" />
</div>
</div>
</form>
</div>
</div>
</div>
</div>
<?php include('footer.php');?>
6-add-action.php
this file contain add and update record code, first start session at top of add-action.php file and then store post value in another variables,if the action type and submit type is match then insert record in database and get last insert id if last insert id is get then redirect to home page and show successfully record insert message otherwise get wrong response error message,
Code:
$sql = "INSERT INTO student(std_fname,
std_lname,
std_address,
std_mobile) VALUES (
:std_fname,
:std_lname,
:std_address,
:std_mobile)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':std_fname', $firstname, PDO::PARAM_STR);
$stmt->bindParam(':std_lname', $lastname, PDO::PARAM_STR);
$stmt->bindParam(':std_address', $address, PDO::PARAM_STR);
$stmt->bindParam(':std_mobile', $mobile, PDO::PARAM_STR);
$stmt->execute();
$newId = $db->lastInsertId();
if ($insert_id != "0") {
$_SESSION['statusMsg'] = '<div class="alert alert-info errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Well done!</strong> You successfully add student infroamtion
</div>';
/* Redirect browser */
header("Location: home.php");
exit();
} else {
$_SESSION['statusMsg'] = '<div class="alert alert-danger errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Sorry!</strong> Something goes wrong.
</div>';
/* Redirect browser */
header("Location: add.php");
exit();
}
above code is insert query written and then prepare if query is prepare prepare() mehtod then bind one by one parameter using pdo method, after that execute query using execute() method
same as edit code, so simple if action type edit and submit action is submit then record is updated
<?php
$pk_id = $_POST['pk_id'];
//SQL:: update record into databse and return last insert id::-
$sql = "UPDATE student SET
std_fname = :std_fname,
std_lname = :std_lname,
std_address = :std_address,
std_mobile = :std_mobile WHERE pk_id = :pk_id";
$stmt = $db->prepare($sql);
$stmt->bindParam(':std_fname', $firstname, PDO::PARAM_STR);
$stmt->bindParam(':std_lname', $lastname, PDO::PARAM_STR);
$stmt->bindParam(':std_address', $address, PDO::PARAM_STR);
$stmt->bindParam(':std_mobile', $mobile, PDO::PARAM_STR);
$stmt->bindParam(':pk_id', $pk_id, PDO::PARAM_STR);
$stmt->execute();
$_SESSION['statusMsg'] = '<div class="alert alert-info errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Well done!</strong> You successfully updated student infroamtion
</div>';
/* Redirect browser */
header("Location: home.php");
exit();
} else {
$_SESSION['statusMsg'] = '<div class="alert alert-danger errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Sorry!</strong> Something goes wrong.
</div>';
/* Redirect browser */
header("Location: add.php");
exit();
?>
above code is update query written and then prepare if query is prepare prepare() mehtod then bind one by one parameter using pdo method, after that execute query using execute() method
Code:
<?php
/*
* include connection file and fetach if you want record::-
*/
include('connection.php');
session_start();
//Post data value::-
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$mobile = $_POST['mobile'];
$action_type = $_POST['action_type'];
$submit = $_POST['submit'];
if ((strcmp($submit, 'Submit') == 0) && trim($action_type) == "add") {
$sql = "INSERT INTO student(std_fname,
std_lname,
std_address,
std_mobile) VALUES (
:std_fname,
:std_lname,
:std_address,
:std_mobile)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':std_fname', $firstname, PDO::PARAM_STR);
$stmt->bindParam(':std_lname', $lastname, PDO::PARAM_STR);
$stmt->bindParam(':std_address', $address, PDO::PARAM_STR);
$stmt->bindParam(':std_mobile', $mobile, PDO::PARAM_STR);
$stmt->execute();
$newId = $db->lastInsertId();
if ($insert_id != "0") {
$_SESSION['statusMsg'] = '<div class="alert alert-info errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Well done!</strong> You successfully add student infroamtion
</div>';
/* Redirect browser */
header("Location: home.php");
exit();
} else {
$_SESSION['statusMsg'] = '<div class="alert alert-danger errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Sorry!</strong> Something goes wrong.
</div>';
/* Redirect browser */
header("Location: add.php");
exit();
}
} if ((strcmp($submit, 'Submit') == 0) && trim($action_type) == "edit") {
$pk_id = $_POST['pk_id'];
//SQL:: update record into databse and return last insert id::-
$sql = "UPDATE student SET
std_fname = :std_fname,
std_lname = :std_lname,
std_address = :std_address,
std_mobile = :std_mobile WHERE pk_id = :pk_id";
$stmt = $db->prepare($sql);
$stmt->bindParam(':std_fname', $firstname, PDO::PARAM_STR);
$stmt->bindParam(':std_lname', $lastname, PDO::PARAM_STR);
$stmt->bindParam(':std_address', $address, PDO::PARAM_STR);
$stmt->bindParam(':std_mobile', $mobile, PDO::PARAM_STR);
$stmt->bindParam(':pk_id', $pk_id, PDO::PARAM_STR);
$stmt->execute();
$_SESSION['statusMsg'] = '<div class="alert alert-info errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Well done!</strong> You successfully updated student infroamtion
</div>';
/* Redirect browser */
header("Location: home.php");
exit();
} else {
$_SESSION['statusMsg'] = '<div class="alert alert-danger errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Sorry!</strong> Something goes wrong.
</div>';
/* Redirect browser */
header("Location: add.php");
exit();
}
?>
7-edit.php
if record is edit get particular id and fetch record using fetch() method.
And show in text box value atttibute field,
Code:
<?php include('header.php'); ?>
<?php
$user_id = $_GET['edit_id'];
//
$stmt = $db->query('SELECT * FROM student where pk_id=' . $user_id . '');
$results = $stmt->fetch(PDO::FETCH_ASSOC);
//echo '<pre>';
//print_r($results);
//die;
session_start();
if (!empty($_SESSION['statusMsg'])) {
echo '<p>' . $_SESSION['statusMsg'] . '</p>';
unset($_SESSION['statusMsg']);
}
?>
<body>
<div class="container">
<div class="alert alert-success">
<a href="#">PDO in PHP</a>!
</div>
</div>
<div class="clearfix"></div>
<div class="container">
<div class="row col-lg-10">
<div class="panel panel-default user-add-edit">
<div class="panel-heading">Add Student </div>
<ul class="pager">
<li class="previous">
<a href="home.php">← Back</a>
</li>
</ul>
<div class="panel-body">
<form class="form-horizontal" action="add-action.php" method="post">
<div class="form-group">
<label for="inputEmail3" class="col-sm-2 control-label">First Name</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="firstname" name="firstname" placeholder="First name" value="<?php print($results['std_fname']); ?>">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Last Name</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="lastname" name="lastname" placeholder="Last name" value="<?php print($results['std_lname']); ?>">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Address</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="address" name="address" placeholder="Address" value="<?php print($results['std_address']); ?>">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Mobile no</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="mobile" name="mobile" placeholder="Mobile no" value="<?php print($results['std_mobile']); ?>">
</div>
</div>
<input type="hidden" name="pk_id" value="<?php print($results['pk_id']); ?>"/>
<input type="hidden" name="action_type" value="edit"/>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" class="btn btn-primary" name="submit" value="Submit"/>
<input type="reset" class="btn btn-default" value="Cancel" />
</div>
</div>
</form>
</div>
</div>
</div>
</div>
<?php include('footer.php'); ?>
please check above code its simple that no more explination required.
8-delete.php
if home.php on click delete button then pass particular filed id and goes to delete.php file in delete.php file get particular record id prepare delete query bind passed parameter and execute query,
Code:
<?php
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
include('connection.php');
session_start();
$pk_id = $_GET['delete_id'];
$sql = "DELETE FROM student WHERE pk_id = :pk_id";
$stmt = $db->prepare($sql);
$stmt->bindParam(':pk_id', $pk_id, PDO::PARAM_INT);
$stmt->execute();
//
$_SESSION['statusMsg'] = '<div class="alert alert-info errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Well done!</strong> You successfully deleted student infroamtion
</div>';
/* Redirect browser */
header("Location: home.php");
exit();
?>
Thanks for supporting me , i hope that code is working well.
Comments