INSERT SELECT UPDATE AND DELETE WITH PDO

CRUD is most important thing in any programming language. After reading Programming basics ,we move to CRUD(create,read, update, delete).

When we use MySQL database ,it is recommended to use PDO to secure web application from SQL injection.

How to use mysqli_connect in PHP

How to Setup MySQL Connection Using PDO in PHP

In this tutorial, we will see how to use create, select, update and delete with PDO.

Let’s see the snippet.

First of all see how to connect to database with PDO in PHP

<?php 
$dbhost  = 'localhost'; 
$dbname  = 'test'; 
$dbuser  = 'root'; 
$dbpass  = '';   
try {     
$dbConn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);         $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);      
} 
catch(PDOException $e) {
     echo $e->getMessage(); 
} ?>  

INSERT

$stmt = $dbConn->prepare("INSERT INTO user(name, age, email) VALUES(:name, :age, :email)"); 
$stmt->bindparam(':name', $name,PDO::PARAM_STR); 
$stmt->bindparam(':age', $age,PDO::PARAM_STR);  
$stmt->bindparam(':email', $email,PDO::PARAM_STR);  
$stmt->execute(); 
$id = $dbConn->lastInsertId(); //Get last inserted id  

SELECT

$stmt = $dbConn->prepare("SELECT * FROM `user` WHERE `id`=:userId"); 
$stmt->bindParam(":userId", $id, PDO::PARAM_INT); 
$stmt->execute(); 
$data = $stmt->fetch(PDO::FETCH_OBJ);  

SELECT ALL

$stmt = $dbConn->prepare("SELECT * FROM `user`"); 
$stmt->execute(); 
$data = $stmt->fetchAll(PDO::FETCH_OBJ);  

UPDATE

$stmt = $dbConn->prepare("UPDATE user SET `phone` = :mobile, `address` = :address WHERE `id` = :userID"); 
$stmt->bindParam(':mobile', $mobile, PDO::PARAM_STR); 
$stmt->bindParam(':address', $address, PDO::PARAM_STR); 
$stmt->bindParam(':userID', $userid, PDO::PARAM_INT); 
$stmt->execute();  

DELETE

$stmt = $dbConn->prepare("DELETE FROM user WHERE id = :userID"); 
$stmt->bindParam(':userID', $userid, PDO::PARAM_INT); 
$stmt->execute();  

Hope this article will help you to understand CRUD operation with PDO in PHP.