Surfe.be - passive income

CRUD PHP with PDO and MySQLi

CRUD PHP with PDO

We are going to show the procedure of PHP CRUD system with PDO. In this tutorial we will complete it. So be patient and keep reading. Why will you learn PDO ? We can make this crud application with PHP & MySQLi easily . But here we will use PDO

ADVANTAGES OF PDO

Object Oriented . Bind parameters in statements (security) . Allows for prepared statements and rollback functionality (consistency) .Throws catchable exceptions for better error handling (quality) .One API for a multitude of RDBMS brands .

PDO offers unified interface to access many different databases. But one of the most important feature that makes it extremely useful is, it abstracts basic operations which otherwise will have to be repeated multiple times – this saves a lot of your work. There are many helper functions too which helps you automate routine operations.

“Prepared statements” function allows you to put markers into the query by using PDO::prepare() and then using PDO::execute() to pass the variable. This greatly enhances the security as there is no possibility of attacks like SQL injection.

php crud with pdo

Before starting our project we have to connect our database. 

CONNECT DATABASE :

To connect databse create a file whose name will be Database.php and paste those following code

<?php

 class Database{
 	private $hostdb="localhost";
 	private $userdb="root";
 	private $passdb;
 	private $namedb="db_crud";
 	public $pdo;

 	public function __construct(){
 		if(!isset($this->pdo))
 		{
 			try{
 				$link=new PDO("mysql:host=".$this->hostdb.";dbname=".$this->namedb,$this->userdb,$this->passdb);
 				$link->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
 				$link->exec("SET CHARACTER SET utf8");
 				$this->pdo=$link;

 			}
 			catch(PDOException $e){
 				die("Failed to connect with Database".$e->getMessage());

 			}
 		}

 	}
 }

?>

Create User.php File

<?php

include "Database.php";

 class User{

 	private $db;
 	public function __construct(){
 		$this->db=new Database();
 	}
}
?>

Our setup file is completed , now we are ready to start our crud operation . 

READ DATA :

First include Database.php file and create database object to access method which are created inside the Database.php file. Create index.php file and write following code. 

<?php
   include 'User.php';
   $user = new User(); // Creating User Class Object to access User class method
?>

	<table class="table table-stripped">
		<th>Serial</th>
		<th>Name</th>
		<th>Action</th>
		<?php

			$userdata = $user->Getting_ALL_User_Data();
			if($userdata){
				$i=0;
				foreach ($userdata as $data) {
					$i++;
		?>
		<tr>
			<td><?php echo $i;?></td>
			<td><?php echo  $data['name'];?></td>
			<td>
			<a class="btn bnt-primary" href="edit.php?id=<?php echo  $data['id'];?>">Edit</a> |
           <a class="btn bnt-primary" href="?id=<?php echo  $data['id'];?>">Delete</a> |
			</td>
		</tr>
	<?php } }else { ?>
		<tr><td colspan="5"><h2>No user data found..</h2></td></tr>
		<?php } ?>

	</table>

Now go User.php file and make a method like below  

public function Getting_ALL_User_Data()
 	{
 		$sql = "SELECT * FROM tbl_user ORDER BY id DESC";
 		$query = $this->db->pdo->prepare($sql);
 		$query->execute();
 		$result = $query->fetchAll();
 		return $result;
 	}

Hope you will understand. Now Insert part. 

INSERT DATA :

Now create a new file whose name will be insert.php and write following code . Keep in mind that the form method must be “post"

<?php include 'User.php'; ?>
 <?php
    $user = new User();
    if($_SERVER['REQUEST_METHOD']=='POST' && isset($_POST['register']))
    {
    	$usrRegi = $user->UserInsertData($_POST);
    }
 ?>

	<h2>User Registration</h2>

	<?php

		if(isset($usrRegi)){
			echo $usrRegi;
		}
	?>

<form action="" method="POST">
	<div class="form-group">
		<label for="name">Name</label>
		<input type="text" id="name" name="name" class="form-control" />
	</div>
	<button type="submit" name="register" class="bntn bnt-success">Submit</button>
</form>

Now go User.php class and create UserInsertData($_POST) method like below 

public function UserInsertData($data)
	{
		$name=$data['name'];
		if($name=="")
		{
			$msg= "<div><strong>ERROR ! </strong>Field must not be empty!</div>";
			return $msg;
		}

		$sql="INSERT INTO tbl_user(name) VALUES(:name)";
		$query = $this->db->pdo->prepare($sql);
		$query->bindValue(':name',$name);
		$result = $query->execute();
		if($result)
		{
			$msg= "<div class='alert alert_success'>SUCCESS!</div>";
			return $msg;
		}
		else

		{
			$msg= "<div class='alert alert_danger'>SORRY !</div>";
			return $msg;
		}
	}

Now time to edit data. To edit data we already catch specific id when we read data in index page. For update data we have to print updated data in hmtl form first. So create a page like edit.php and paste the following code. 

Edit Data

<?php include 'User.php'; ?>

 <?php
 		if(isset($_GET['id'])){
 		 $userid = preg_replace('/[^-a-zA-Z0-9_]/', '',$_GET['id']);
 		}
 		$user = new User();

    $user = new User();
    if($_SERVER['REQUEST_METHOD']=='POST' && isset($_POST['update']))
    {
    	$updateusr=$user->UpdateUserData($userid,$_POST);
    }

 ?>

	<?php
		if(isset($updateusr)){
			echo $updateusr; //Show Update Message
		}
	?>
	<?php
		$userdata=$user->GetDataUserById($userid); //For form data
		if($userdata){

	?>
	<form action="" method="POST">
		<div class="form-group">
			<label for="name">Name</label>
			<input type="text" id="name" name="name" class="form-control" value="<?php echo $userdata->name;?>" />

		<button type="text" name="update" class="bntn btn-success">Update</button>
	</form>
<?php } ?>

Now create two method one is UpdateUserData($userid,$_POST) and other is GetDataUserById($userid) . To create it go User.php and paste the following code.

public function GetDataUserById($id)
 	{
 		$sql="SELECT * FROM tbl_user WHERE id = :id LIMIT 1";
 		$query = $this->db->pdo->prepare($sql);
 		$query->bindValue(':id',$id);
 		$query->execute();
 		$result = $query->fetch(PDO::FETCH_OBJ);
 		return $result;
 	}

 	public function UpdateUserData($id,$data){
 		$name = $data['name'];

 		$sql="UPDATE tbl_user set
 		          name= :name,
 		          WHERE id= :id";

 		$query = $this->db->pdo->prepare($sql);
 		$query->bindValue(':name',$name);
 		$query->bindValue(':id',$id);
 		$result = $query->execute();

 		if($result)
 		{
 			$msg= "<div class='alert alert_success'>Success!</div>";
 			return $msg;
 		}
 		else

 		{
 			$msg= "<div class='alert alert_success'>Try Again!</div>";
 			return $msg;
 		}

 	}

Now we are in last part . so go index.php file and paste the following code in above. 

<?php
 		if(isset($_GET['id'])){
 		 $deleteId = preg_replace('/[^-a-zA-Z0-9_]/', '',$_GET['id']);
 		}
    $user = new User();
    	$deleteUser = $user->DeleteUserData($deleteId);


 ?>

<?php
	if(isset($deleteUser )){
	  echo $deleteUser ; //Show Delete Message
	}
?>

Now go User.php file and make this method to delete data .

public function DeleteUserData($id){

 $sql = "DELETE FROM $table WHERE id=:id";
 $query = $this->db->pdo->prepare($sql);
 $result = $query->execute(array(':id'=>$id));


 		if($result)
 		{
 			$msg= "<div class='alert alert_success'>Success!</div>";
 			return $msg;
 		}
 		else

 		{
 			$msg= "<div class='alert alert_success'>Try Again!</div>";
 			return $msg;
 		}
}

Hope you will understand all these procedure. If you like this tutorial please leave a comments and share with your friends.

Note: If you are not using PHP PDO in your applications, it is recommended to use PDO in your applications. To improve security and also not hacked by SQL injection attacks.If you want to learn more about PHP PDO, you can join my course. It will be published soon.If you have any doubts, let me know through the comment form below.

Leave a comments

LET'S SOCIALITE

Recent Tweets