In the previous tutorial we learned about PDO Extension . In this tutorial we will learn CRUD operation using PDO Extension.
CRUD Stands for create, read, update and delete record in the database.
File Structure for CRUD Operation
dbconfig.php- used for database connection
tblusers.sql– Contain the structure of the database table
insert.php- used for add a record in the database
index.php– Used for read the record from database .
update.php– Used for updating a record.
Step 1– Create a database
Open browser type http://localhost/phpmyadmin, create a database named ‘phpcrudpdo’ . After creating database run the sql script or import the sql file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Table structure for table `tblusers` -- CREATE TABLE IF NOT EXISTS `tblusers` ( `id` int(11) NOT NULL, `FirstName` varchar(150) NOT NULL, `LastName` varchar(150) NOT NULL, `EmailId` varchar(120) NOT NULL, `ContactNumber` char(11) NOT NULL, `Address` varchar(255) NOT NULL, `PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Indexes for table `tblusers` -- ALTER TABLE `tblusers` ADD PRIMARY KEY (`id`); -- AUTO_INCREMENT for dumped tables -- AUTO_INCREMENT for table `tblusers` -- ALTER TABLE `tblusers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; |
Step 2– Create a database connection file (dbconfig.php)
create a dbconfig.php file and place the code given below :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php // DB credentials. define('DB_HOST','localhost'); define('DB_USER','root'); define('DB_PASS',''); define('DB_NAME','phpcrudpdo'); // Establish database connection. try { $dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS); } catch (PDOException $e) { exit("Error: " . $e->getMessage()); } ?> |
Step 3 : Insert a record in database
Create a insert.php file for insert record in the database . This page include a HTML form with input field where we can fill the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>PHP CURD Operation using PDO Extension </title> <meta name="viewport" content="width=device-width, initial-scale=1"> <link href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet"> <script src="http://code.jquery.com/jquery-1.11.1.min.js"></script> <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script> </head> <body> <div class="container"> <div class="row"> <div class="col-md-12"> <h3>Insert Record | PHP CRUD Operations using PDO Extension</h3> <hr /> </div> </div> <form name="insertrecord" method="post"> <div class="row"> <div class="col-md-4"><b>First Name</b> <input type="text" name="firstname" class="form-control" required> </div> <div class="col-md-4"><b>Last Name</b> <input type="text" name="lastname" class="form-control" required> </div> </div> <div class="row"> <div class="col-md-4"><b>Email id</b> <input type="email" name="emailid" class="form-control" required> </div> <div class="col-md-4"><b>Contactno</b> <input type="text" name="contactno" class="form-control" maxlength="10" required> </div> </div> <div class="row"> <div class="col-md-8"><b>Address</b> <textarea class="form-control" name="address" required></textarea> </div> </div> <div class="row" style="margin-top:1%"> <div class="col-md-8"> <input type="submit" name="insert" value="Submit"> </div> </div> </form> </div> </div> </body> </html> |
Code for insert a record in database. Once the user filled all the data and click on the submit button then data will be saved in the database using the below code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<?php // include database connection file require_once'dbconfig.php'; if(isset($_POST['insert'])) { // Posted Values $fname=$_POST['firstname']; $lname=$_POST['lastname']; $emailid=$_POST['emailid']; $contactno=$_POST['contactno']; $address=$_POST['address']; // Query for Insertion $sql="INSERT INTO tblusers(FirstName,LastName,EmailId,ContactNumber,Address) VALUES(:fn,:ln,:eml,:cno,:adrss)"; //Prepare Query for Execution $query = $dbh->prepare($sql); // Bind the parameters $query->bindParam(':fn',$fname,PDO::PARAM_STR); $query->bindParam(':ln',$lname,PDO::PARAM_STR); $query->bindParam(':eml',$emailid,PDO::PARAM_STR); $query->bindParam(':cno',$contactno,PDO::PARAM_STR); $query->bindParam(':adrss',$address,PDO::PARAM_STR); // Query Execution $query->execute(); // Check that the insertion really worked. If the last inserted id is greater than zero, the insertion worked. $lastInsertId = $dbh->lastInsertId(); if($lastInsertId) { // Message for successfull insertion echo "<script>alert('Record inserted successfully');</script>"; echo "<script>window.location.href='index.php'</script>"; } else { // Message for unsuccessfull insertion echo "<script>alert('Something went wrong. Please try again');</script>"; echo "<script>window.location.href='index.php'</script>"; } } ?> |
Step 4 : Read record from the database
Create a index.php file for read all records from database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
<?php // include database connection file require_once'dbconfig.php'; ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>PHP CRUD Operations using PDO Extension </title> <meta name="viewport" content="width=device-width, initial-scale=1"> <link href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet"> <style type="text/css"> </style> <script src="http://code.jquery.com/jquery-1.11.1.min.js"></script> <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script> </head> <body> <div class="container"> <div class="row"> <div class="col-md-12"> <h3>PHP CRUD Operations using PDO Extension</h3> <hr /> <a href="insert.php"><button class="btn btn-primary"> Insert Record</button></a> <div class="table-responsive"> <table id="mytable" class="table table-bordred table-striped"> <thead> <th>#</th> <th>First Name</th> <th>Last Name</th> <th>Email</th> <th>Contact</th> <th>Address</th> <th>Posting Date</th> <th>Edit</th> <th>Delete</th> </thead> <tbody> <?php $sql = "SELECT FirstName,LastName,EmailId,ContactNumber,Address,PostingDate,id from tblusers"; //Prepare the query: $query = $dbh->prepare($sql); //Execute the query: $query->execute(); //Assign the data which you pulled from the database (in the preceding step) to a variable. $results=$query->fetchAll(PDO::FETCH_OBJ); // For serial number initialization $cnt=1; if($query->rowCount() > 0) { //In case that the query returned at least one record, we can echo the records within a foreach loop: foreach($results as $result) { ?> <!-- Display Records --> <tr> <td><?php echo htmlentities($cnt);?></td> <td><?php echo htmlentities($result->FirstName);?></td> <td><?php echo htmlentities($result->LastName);?></td> <td><?php echo htmlentities($result->EmailId);?></td> <td><?php echo htmlentities($result->ContactNumber);?></td> <td><?php echo htmlentities($result->Address);?></td> <td><?php echo htmlentities($result->PostingDate);?></td> <td><a href="update.php?id=<?php echo htmlentities($result->id);?>"><button class="btn btn-primary btn-xs"><span class="glyphicon glyphicon-pencil"></span></button></a></td> <td><a href="index.php?del=<?php echo htmlentities($result->id);?>"><button class="btn btn-danger btn-xs" onClick="return confirm('Do you really want to delete');"><span class="glyphicon glyphicon-trash"></span></button></a></td> </tr> <?php // for serial number increment $cnt++; }} ?> </tbody> </table> </div> </div> </div> </div> </body> </html> |
Step 5 : Update record in the database
Step 5.1 : Get data in HTML From
create update.php file. For updating a record we have to get the row id of that record and store in $id. We access the $_GET[‘id’] variable to do it.
Code for get a record based on the given id. Through this way we can get data autofill-data in HTML Form.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
<?php // Get the userid $userid=intval($_GET['id']); $sql = "SELECT FirstName,LastName,EmailId,ContactNumber,Address,PostingDate,id from tblusers where id=:uid"; //Prepare the query: $query = $dbh->prepare($sql); //Bind the parameters $query->bindParam(':uid',$userid,PDO::PARAM_STR); //Execute the query: $query->execute(); //Assign the data which you pulled from the database (in the preceding step) to a variable. $results=$query->fetchAll(PDO::FETCH_OBJ); // For serial number initialization $cnt=1; if($query->rowCount() > 0) { //In case that the query returned at least one record, we can echo the records within a foreach loop: foreach($results as $result) { ?> <form name="insertrecord" method="post"> <div class="row"> <div class="col-md-4"><b>First Name</b> <input type="text" name="firstname" value="<?php echo htmlentities($result->FirstName);?>" class="form-control" required> </div> <div class="col-md-4"><b>Last Name</b> <input type="text" name="lastname" value="<?php echo htmlentities($result->LastName);?>" class="form-control" required> </div> </div> <div class="row"> <div class="col-md-4"><b>Email id</b> <input type="email" name="emailid" value="<?php echo htmlentities($result->EmailId);?>" class="form-control" required> </div> <div class="col-md-4"><b>Contactno</b> <input type="text" name="contactno" value="<?php echo htmlentities($result->ContactNumber);?>" class="form-control" maxlength="10" required> </div> </div> <div class="row"> <div class="col-md-8"><b>Address</b> <textarea class="form-control" name="address" required><?php echo htmlentities($result->Address);?></textarea> </div> </div> <?php }} ?> <div class="row" style="margin-top:1%"> <div class="col-md-8"> <input type="submit" name="update" value="Update"> </div> </div> </form> |
Step 5.2 : Code for update the record
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
<?php // include database connection file require_once'dbconfig.php'; if(isset($_POST['update'])) { // Get the userid $userid=intval($_GET['id']); // Posted Values $fname=$_POST['firstname']; $lname=$_POST['lastname']; $emailid=$_POST['emailid']; $contactno=$_POST['contactno']; $address=$_POST['address']; // Query for Updation $sql="update tblusers set FirstName=:fn,LastName=:ln,EmailId=:eml,ContactNumber=:cno,Address=:adrss where id=:uid"; //Prepare Query for Execution $query = $dbh->prepare($sql); // Bind the parameters $query->bindParam(':fn',$fname,PDO::PARAM_STR); $query->bindParam(':ln',$lname,PDO::PARAM_STR); $query->bindParam(':eml',$emailid,PDO::PARAM_STR); $query->bindParam(':cno',$contactno,PDO::PARAM_STR); $query->bindParam(':adrss',$address,PDO::PARAM_STR); $query->bindParam(':uid',$userid,PDO::PARAM_STR); // Query Execution $query->execute(); // Mesage after updation echo "<script>alert('Record Updated successfully');</script>"; // Code for redirection echo "<script>window.location.href='index.php'</script>"; } ?> |
Step 6 : Delete a record from the database
Place this code in the index.php file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<?php // include database connection file require_once'dbconfig.php'; // Code for record deletion if(isset($_REQUEST['del'])) { //Get row id $uid=intval($_GET['del']); //Qyery for deletion $sql = "delete from tblusers WHERE id=:id"; // Prepare query for execution $query = $dbh->prepare($sql); // bind the parameters $query-> bindParam(':id',$uid, PDO::PARAM_STR); // Query Execution $query -> execute(); // Mesage after updation echo "<script>alert('Record Updated successfully');</script>"; // Code for redirection echo "<script>window.location.href='index.php'</script>"; } ?> |
