Skip to main content

using PDO database connection add,update,delete,edit operation

PDO advantage :
1-Object Oriented
2-Bind parameters in statements (security)
3-Allows for prepared statements and rollback functionality (consistency)
4-Throws catcheable exceptions for better error handling (quality)
5-Exception mode; no need to check error state after each API call.
It's best to tell PDO how you'd like the data to be fetched. You have the following options:
1-PDO::FETCH_ASSOC: returns an array indexed by column name.
2-PDO::FETCH_BOTH: (default):returns an array indexed by both column name and number.
3-PDO::FETCH_BOUND:Assigns the values of your columns to the variables set with the ->bindColumn() method.
4-PDO::FETCH_CLASS: Assigns the values of your columns to properties of the named class. It will create the properties if matching properties do not exist.
5-PDO::FETCH_INTO:Updates an existing instance of the named class.
6-PDO::FETCH_LAZY: Combines.
7-PDO::FETCH_BOTH/PDO:FETCH_OBJ, creating the object variable names as they are used.
8-PDO::FETCH_NUM:returns an array indexed by column number.
9-PDO::FETCH_OBJ:returns an anonymous object with property names that correspond to the column names
FETCH_ASSOC

This fetch type creates an associative array, indexed by column name. This should be quite familiar to anyone who has used the mysql/mysqli extensions. Here's an example of selecting data with this method:

 

$STH = $DBH->query('SELECT name, addr, city from folks');

# setting the fetch mode
$STH->setFetchMode(PDO::FETCH_ASSOC);

while($row = $STH->fetch()) {
echo $row['name'] . "\n";
echo $row['addr'] . "\n";
echo $row['city'] . "\n";
}
FETCH_OBJ
This fetch type creates an object of std class for each row of fetched data. Here's an example:

 
# creating the statement
$STH = $DBH->query('SELECT name, addr, city from folks');
# setting the fetch mode
$STH->setFetchMode(PDO::FETCH_OBJ);
# showing the results
while($row = $STH->fetch()) {
echo $row->name . "\n";
echo $row->addr . "\n";
echo $row->city . "\n";
}
FETCH_CLASS
This fetch method allows you to fetch data directly into a class of your choosing. When you use FETCH_CLASS, the properties of your object are set BEFORE the constructor is called.
Now, when you repeat the previous example with this fetch mode (PDO::FETCH_PROPS_LATE) the address will NOT be obscured, constructor when fetching data into objects with PDO:

 
$STH->setFetchMode(PDO::FETCH_CLASS, 'secret_person', array('stuff'));

Demo Link

Demo

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 $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


 
<?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">&times;</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:

<script>
<?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">
     &nbsp; <a href="home.php">&larr; 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:








<?php
$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">&times;</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">&times;</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">&times;</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">&times;</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
 <?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">&times;</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">&times;</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">&times;</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">&times;</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">
&nbsp; <a href="home.php">&larr; 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">&times;</span>
</button>
<strong>Well done!</strong> You successfully deleted student infroamtion
</div>';
/* Redirect browser */
header("Location: home.php");
exit();
?>















I hope that above example understand and enjoy code, if you like my blog please comment and share

























Labels:
































Comments








Post a Comment
























Popular posts from this blog



















Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links




























  Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links  user want to share any information then use following code  and read step by step Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links   if you want share profile on following social link :  1-Facebook  2-twitter.com  3-LinkedIn  4-google +,    Code link :   https://drive.google.com/open?id=1IzTZZh_0euDqFSHL_vPRiQePlNTw3h-q   Demo link :   http://freeteachnology.hol.es/socialshare/   To modify a page's thumbnail image, description, and additional metadata for these services, you can provide meta tags in the HTML code of the page.Implementing Open Graph Meta Tags   You can implement meta tags in a number of ways. In content management systems might  be allow you to modify a page's meta tags ,   then use following code in meta section of your project code,     <link href="bootstrap.min.css" rel="stylesheet"> <link href="bootstrap-tour.m...




















Post a Comment









Read more




























GUID for globally unique identifier




























   How to create GUID in php   1-guid stands for globally unique identifier generally used to create random unique strings in php, create access token in php   2-Mostly use of GUID for generating access token, generate unique id,  generating unique string in php. Using this article how to create guide  in php you can create a random string for any use to keep unique    3-GUID consists of alphanumeric characters only and is grouped in five groups separated by hyphens as seen in this example:   3F2504E0-4F89-11D3-9A0C-0305E82C3301   Eg:-   <?php  /**  * Generate Globally Unique Identifier (GUID)  * E.g. 2EF40F5A-ADE8-5AE3-2491-85CA5CBD6EA7  *  * @param boolean $include_braces Set to true if the final guid needs  *                                to be wrapped in curly braces  * @return string  */  function generateGuid($include_braces = false) {  if (function_exists('com_create_guid')) {  if ($include_braces === true) {  return com_create_guid();  } else {  return substr(com_cr...




















2 comments









Read more