Well, leave this theoretical part, you can read it from internet. I actually not trying to teach PDO, there is so much tutorial available for this. I just trying to make a collection of CRUD codes here using PDO.
It is actually for my practice and also for my future need , if anytime I need it. And also sharing with all if anybody like me will need this. I am still learning.
Ok, CRUD (Create , Read, Update, Delete) is the base of database driven application. I always follow a method to learn codes, that is -
If you want to learn any code, then create a project with this code and follow steps.
So, I also created a project with PDO CRUD. This is the project screenshot -
Now lets start our project.
Before starting CRUD I am sharing some code that is Connecting DB and Creating DB. It is important but not always because , we always need to connect db but we creates db manually from phpmyadmin. So, Creating db is only to keep in mind, not for project.
*** Suppose we are using "myDB" as our project DB ***
=> First Connect DB:
<?php
$servername = "Server";
$username = "username";
$password = "password";
try {
$conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage() ."<br />";
}
?>
=> To Create DB (We are creating pdoDB here, but we will use myDB as our project DB):
<?php
$servername = "Server";
$username = "username";
$password = "password";
try {
$conn = new PDO("mysql:host=$servername", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE pdoDB";
// use exec() because no results are returned
$conn->exec($sql);
echo "Database created successfully<br>";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
=>To Create Table (We will create a table with the fields : firstname,lastname,email,reg_date):
<?php
$servername = "Server";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to create table
$sql = "CREATE TABLE Users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
// use exec() because no results are returned
$conn->exec($sql);
echo "Table Users created successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Well, we just completed some part that will not required for everytime, these part will required only for once except Connection. Now I just share the part that will be used after connection of db.
First we select data from table , to do this , we will create a php file called select.
=>Code in select.php :
<?php
$servername = "Server";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT * FROM Users");
$stmt->execute();
// set the resulting array to associative
$stmt->setFetchMode(PDO::FETCH_ASSOC); // You can use this line or not.
$result = $stmt->fetchAll();
//Data will be shown in a table, or you can make your choice to show data....
?>
<table class="table table-bordered">
<tr>
<th>ID </th>
<th> First Name </th>
<th> Last Name </th>
<th> Email </th>
<th> Action </th>
</tr>
<?php
foreach($result as $user){
?>
<tr>
<td><?=$user['id']?></td>
<td><?=$user['firstname']?></td>
<td><?=$user['lastname']?></td>
<td><?=$user['email']?></td>
<td>
<a href="index.php?id=<?=$user['id']?>&action=edit&firstname=<?=$user['firstname']?>&lastname=<?=$user['lastname']?>&email=<?=$user['email']?>">Update</a>
<a href="index.php?id=<?=$user['id']?>&action=delete">Delete</a>
</td>
</tr>
<?php } ?>
</table>
<?php
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
We just selected Data from table and showed them in table in browser. Now , we need to insert data on the database table. So, we are creating create.php file.
=>Code in create.php file:
<?php
$servername = "Server";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO Users (firstname, lastname, email)
VALUES (:firstname, :lastname, :email)";
$stmt=$conn->prepare($sql);
// I used an array to pass value in the table, but there is also an option to set value using "bindparam" that I used in "delete.php" file. You can see this file ....
$stmt->execute(array(
':firstname'=>$_POST['firstname'],
':lastname'=>$_POST['lastname'],
':email'=>$_POST['email']
));
$last_id = $conn->lastInsertId();
echo "New record created successfully. Last ID is : ". $last_id;
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Ok, we are able to insert data in the table. Now we need to update our data, so, lets create update.php file
=>Code in update.php file:
<?php
$servername = "Server";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE Users SET firstname=:firstname, lastname=:lastname, email=:email WHERE id=:id";
// Prepare statement
$stmt = $conn->prepare($sql);
$stmt->execute(array(
':firstname'=>$_POST['firstname'],
':lastname'=>$_POST['lastname'],
':email'=>$_POST['email'],
':id'=>$_POST['id']
));
// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . " records UPDATED successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Now we also can update our data, so, lets delete unnecessary data. Lets creates delete.php file.
=>Code in delete.php file:
<?php
$servername = "Server";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to delete a record
$sql = "DELETE FROM Users WHERE id=:id";
$stmt=$conn->prepare($sql);
$stmt->bindparam(":id",$_GET['id'],PDO::PARAM_STR);
$stmt->execute();
// use exec() because no results are returned
//$conn->exec($sql);
echo "Record deleted successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Ok, our CRUD is complete, but it need to be implemented . So, we need some file that will use these created file to complete user query. This is only your choice how to do this, I just do everything in one file. I used everything in index.php file. One more thing, I used bootstrap, I download bootstrap and create a folder named "asset" and keep bootstrap files inside "asset" folder. I also downloaded the jquery file and kept it on "js" folder inside " asset" folder.
=>Code in index.php file:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->
<title>PDO CRUD Project</title>
<!-- Bootstrap -->
<link href="asset/css/bootstrap.min.css" rel="stylesheet">
<!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
<script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
<![endif]-->
</head>
<body>
<div class="container">
<div class="jumbotron">
<h1>This is a practice project for CRUD using PDO</h1>
</div>
<div class="row"> <div class="col-md-12">
<form action="index.php" method="post">
<input type="text" name="firstname" value="" placeholder="First Name">
<input type="text" name="lastname" value="" placeholder="Last Name">
<input type="text" name="email" value="" placeholder="Email">
<input type="submit" name="insert" value="Submit">
</form>
<?php
if(isset($_POST['submit'])){
include_once "update.php";
}
if(isset($_POST['insert'])){
include_once "insert.php" ;
}
if(isset($_GET['id']) && isset($_GET['action'])){
if($_GET['action']=='edit' ){
?>
<form action="index.php" method="post">
<input type="text" name="firstname" value="<?=$_GET['firstname']?>">
<input type="text" name="lastname" value="<?=$_GET['lastname']?>">
<input type="text" name="email" value="<?=$_GET['email']?>">
<input type="hidden" name="id" value="<?=$_GET['id']?>">
<input type="submit" name="submit" value="Submit">
</form>
<?php
}elseif($_GET['action']=='delete' ){
include_once "delete.php";
}
}
include_once "select.php" ;
?>
</div>
</div>
</div>
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<!--<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>-->
<script src="asset/js/jquery.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="asset/js/bootstrap.min.js"></script>
</body>
</html>
Ok , our project is complete, we can now Create data, Read data, Update data and Delete data. I know this is a low quality writing , any kind of suggestion and advice will be granted to improve my writing quality.
Thanks for reading this , please comment your opinion or any problem you found related this.
No comments:
Post a Comment