How to Create Simple CRUD Application in PHP PDO - 7 Easy steps
4
Develop Secure Applications with PHP PDO – Ultimate Guide

How to Create Simple CRUD Application in PHP PDO – 7 Easy steps

Using PHP PDO is recommended because it’s more secure and protects from SQL injections. PHP PDO CRUD application, previously we have seen CRUD application in PHP & MySQL. Here in this article, I’m going to build CRUD application with PHP Data Objects.

Before continuing with creating CRUD application in PDO, you need to know basics about PDO. If you don’t know anything about PDO, don’t worry I’m creating a course for you to easily understand PHP PDO, It will out soon. I’ll update you as soon as it’s published.

This article is same as the PHP CRUD application, here I’m using PDO style. All the features are same.

About CRUD Application with PHP PDO

Coming to CRUD application, it’s simple CRUD application with few input fields. With this application we will insert submitted form data into the database, this is the create operation.

Next is displaying the records that are fetched from the database, we will display this data in table format. This is the Read Operation.

Next is Update operation, we will fetch the data from the database and displayed in the form instead of a table. And we will update this data in the database after submitting using id, this specific record will be updated using id passed in the URL. This is Update Operation.

And final operation is delete operation. After clicking on the delete link from Read Operation page record will be deleted from the database.

1. Create Database Table

First of all, we need to create the database. If you are following from the PHP CRUD application you can use the same database. Here is the SQL code to create the database table.

Oracle PL/SQL
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
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 29, 2017 at 02:31 PM
-- Server version: 10.1.16-MariaDB
-- PHP Version: 5.6.24
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
--
-- Database: `demo`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `crud`
--
 
CREATE TABLE `crud` (
  `id` int(11) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `age` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `crud`
--
 
INSERT INTO `crud` (`id`, `firstname`, `lastname`, `email`, `gender`, `age`) VALUES
(5, 'Vivek', 'Vengala', 'vivek@codingcyber.com', 'male', '24'),
(6, 'Teresa', 'Day', 'teresa.day81@example.com', 'female', '28'),
(7, 'Nora', 'Nora Castillo', 'nora.castillo59@example.com', 'female', '30'),
(12, 'Mr.Vivek', 'Vengala', 'vivek@codingcyber.com', 'male', '28'),
(13, 'Mr.Vivek', 'Vengala', 'vivek@codingcyber.com', 'male', '28'),
(14, 'Mr.Vivek', 'Vengala', 'vivek@codingcyber.com', 'male', '28'),
(16, 'Vivek', 'Vengala', 'vivek1@codingcyber.com', 'male', '24'),
(18, 'Mr.Vivek', 'Vengala', 'vivek@codingcyber.com', 'male', '29'),
(19, 'Yvonne', 'Ramos', 'yvonne.ramos34@example.com', 'female', '23'),
(20, 'Brandon', 'Craig', 'brandon.craig42@example.com', 'male', '25');
 
--
-- Indexes for dumped tables
--
 
--
-- Indexes for table `crud`
--
ALTER TABLE `crud`
  ADD PRIMARY KEY (`id`);
 
--
-- AUTO_INCREMENT for dumped tables
--
 
--
-- AUTO_INCREMENT for table `crud`
--
ALTER TABLE `crud`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

2. Creating Simple HTML Form

Here I’ve created a simple bootstrap form with these fields. Firstname, lastname, email, age, gender. It’s simple form HTML.

PHP
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
<form method="post" class="form-horizontal col-md-6 col-md-offset-3">
<h2>Create Operation in CRUD Application with PHP PDO</h2>
<div class="form-group">
    <label for="input1" class="col-sm-2 control-label">First Name</label>
    <div class="col-sm-10">
      <input type="text" name="fname"  class="form-control" id="input1" placeholder="First Name" />
    </div>
</div>
 
<div class="form-group">
    <label for="input1" class="col-sm-2 control-label">Last Name</label>
    <div class="col-sm-10">
      <input type="text" name="lname"  class="form-control" id="input1" placeholder="Last Name" />
    </div>
</div>
 
<div class="form-group">
    <label for="input1" class="col-sm-2 control-label">E-Mail</label>
    <div class="col-sm-10">
      <input type="email" name="email"  class="form-control" id="input1" placeholder="E-Mail" />
    </div>
</div>
 
<div class="form-group" class="radio">
<label for="input1" class="col-sm-2 control-label">Gender</label>
<div class="col-sm-10">
  <label>
    <input type="radio" name="gender" id="optionsRadios1" value="male" checked> Male
  </label>
     <label>
    <input type="radio" name="gender" id="optionsRadios1" value="female"> Female
  </label>
</div>
</div>
 
<div class="form-group">
<label for="input1" class="col-sm-2 control-label">Age</label>
<div class="col-sm-10">
<select name="age" class="form-control">
<option>Select Your Age</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
</select>
</div>
</div>
<input type="submit" class="btn btn-primary col-md-2 col-md-offset-10" value="submit" />
</form>

3. Connecting to Database with PHP PDO

In PHP PDO, we can connect to the database by creating new PDO object and we have to pass database type, hostname, database name, database username, database password.

Here I’ve created a DSN variable with database type, here I’m using the mysql database. And the hostname is localhost, use your hostname here. Next value is database name.

I’m creating database object by instantiating PDO with dsn variable and database username, password.

Here is the code you can use. Save it as the connect.php file.

PHP
1
2
$dsn = 'mysql:host=localhost;dbname=demo';
$db = new PDO($dsn, 'root', '');

4. Inserting Records with PHP PDO

Before going on to insert the record, we should include connect.php file in insert operations PHP file. Here I’m going to insert the record from index.php file. Below is the code to include the file.

PHP
1
require_once('connect.php');

After that we should catch the errors for that I’ll use a try-catch block. We can catch the errors in two ways that is using setAttribute method and with the errorInfo method.

Here I’m using the setAttribute method. Inside try block I’m using require_once PHP function to load connect.php file and after that setting error mode with the setAttribute method.

In catch method, I’m assigning the exception to $e, then catching the errors with exception getMessage method.

Here is the code

PHP
1
2
3
4
5
6
try{
require_once('connect.php');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(Exception $e){
$error = $e->getMessage();
}

To display error messages use this code

PHP
1
if(isset($error)){ echo $error; }

I’ll use the above form with bootstrap styles.

After form submission, I’m checking post superglobal is set and not empty with if condition. Here is the code

PHP
1
2
3
if(isset($_POST) & !empty($_POST)){
//insert the record with prepared statements
}

Here I’m using prepared statements to insert submitted data into the database with named placeholders. There are other methods like named array placeholders.

Here is the SQL query with named placeholders

PHP
1
$sql = "INSERT INTO crud (firstname, lastname, email, gender, age) VALUES(:firstname, :lastname, :email, :gender, :age)";

After that we will prepare the statements, Then we will execute it with values linking with named placeholders. Until execute method, records won’t be inserted in the database.

Here is the code to prepare and execute methods.

PHP
1
2
3
4
5
6
7
8
9
$result = $db->prepare($sql);
$res = $result->execute(
array(
':firstname' => $_POST['fname'],
':lastname' => $_POST['lname'],
'email' => $_POST['email'],
'gender' => $_POST['gender'],
'age' => $_POST['age']
));

After that, I’ll check whether $res is true or false. If it’s true we will display success message or else we will display an error message.

PHP
1
2
3
4
5
if($res){
echo "Successfully inserted data";
}else{
echo "failed to insert data";
}

5. Fetching Records with PHP PDO

This is Read operation, Here I’m fetching records with query method and displaying these records in the table. And the code of the HTML table, you can find below.

Use this table HTML code and save the file as view.php

PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<table class="table">
<tr>
<th>#</th>
<th>Full Name</th>
<th>E-Mail</th>
<th>Gender</th>
<th>Age</th>
<th>Extras</th>
</tr>
<tr>
<td>id</td>
<td>firstname with lastname</td>
<td>email</td>
<td>gender</td>
<td>age</td>
<td><a href="#">Edit</a> <a href="#">Delete</a></td>
</tr>
</table>

We will display the fetched data in the second tr with a while loop.

First of all, we will see SELECT SQL query with query method. Here is the code.

PHP
1
2
$sql = "SELECT * FROM crud";
$result = $db->query($sql);

For connecting to the database and also for catching errors I’m using the same code as above.

To get records from the result I’m using PDO fetch method. PDO fetch method returns two types of records that are numeric array and the associative array. To get only associative array I’m using PDO style inside PDO fetch method.

And the code looks like this…

PHP
1
$result->fetch(PDO::FETCH_ASSOC)

PDO fetch method returns only one result, to get multiple records with fetch method we should use while loop. And the code for PDO fetch method with while loop is

PHP
1
2
3
while($r = $result->fetch(PDO::FETCH_ASSOC)){
// echo the values inside table data cells
}

Now we can echo the information fetched from the database.

PHP
1
2
3
4
5
6
7
8
9
10
11
12
<?php
while($r = $result->fetch(PDO::FETCH_ASSOC)){
?>
<tr>
<td><?php echo $r['id']; ?></td>
<td><?php echo $r['firstname'] . " " . $r['lastname']; ?></td>
<td><?php echo $r['email']; ?></td>
<td><?php echo $r['gender']; ?></td>
<td><?php echo $r['age']; ?></td>
<td><a href="#">Edit</a> <a href="#">Delete</a></td>
</tr>
<?php } ?>

6. Updating Records with PHP PDO

In update operation, we have to perform two operations that are Read operation and Update operation.

And the file name is update.php

Read operation is for fetching data from the database and displaying it in form. After that when users submit the form, this information will be updated in the database associated with that id.

In the view.php file, we are fetching all the records available in the database table. Here we will fetch only 1 record with that id. Here I’m using prepared statements for select query also because we are sending id from URL that is with get method.

Here I’m using anonymous placeholders to pass the id. Because we are just passing only one value that is id.

Here is the code for SQL query, prepare statement and execute method

PHP
1
2
3
$selsql = "SELECT * FROM `crud` WHERE id=?";
$selresult = $db->prepare($selsql);
$selres = $selresult->execute(array($_GET['id']));

Here in update.php file, I’m using the same form as in index.php and the only difference is here I’m passing the values in the form. To display the values in the form.

PHP
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
<form method="post" class="form-horizontal col-md-6 col-md-offset-3">
<h2>Update Operation in CRUD Application</h2>
<div class="form-group">
    <label for="input1" class="col-sm-2 control-label">First Name</label>
    <div class="col-sm-10">
      <input type="text" name="fname"  class="form-control" id="input1" value="<?php echo $r['firstname'] ?>" placeholder="First Name" />
    </div>
</div>
 
<div class="form-group">
    <label for="input1" class="col-sm-2 control-label">Last Name</label>
    <div class="col-sm-10">
      <input type="text" name="lname"  class="form-control" id="input1" value="<?php echo $r['lastname'] ?>" placeholder="Last Name" />
    </div>
</div>
 
<div class="form-group">
    <label for="input1" class="col-sm-2 control-label">E-Mail</label>
    <div class="col-sm-10">
      <input type="email" name="email"  class="form-control" id="input1" value="<?php echo $r['email'] ?>" placeholder="E-Mail" />
    </div>
</div>
 
<div class="form-group" class="radio">
<label for="input1" class="col-sm-2 control-label">Gender</label>
<div class="col-sm-10">
  <label>
    <input type="radio" name="gender" id="optionsRadios1" value="male" <?php if($r['gender'] == 'male'){ echo "checked";} ?>> Male
  </label>
     <label>
    <input type="radio" name="gender" id="optionsRadios1" value="female" <?php if($r['gender'] == 'female'){ echo "checked";} ?>> Female
  </label>
</div>
</div>
 
<div class="form-group">
<label for="input1" class="col-sm-2 control-label">Age</label>
<div class="col-sm-10">
<select name="age" class="form-control">
<option>Select Your Age</option>
<option value="20" <?php if($r['age'] == '20'){ echo "selected";} ?>>20</option>
<option value="21" <?php if($r['age'] == '21'){ echo "selected";} ?>>21</option>
<option value="22" <?php if($r['age'] == '22'){ echo "selected";} ?>>22</option>
<option value="23" <?php if($r['age'] == '23'){ echo "selected";} ?> >23</option>
<option value="24" <?php if($r['age'] == '24'){ echo "selected";} ?>>24</option>
<option value="25" <?php if($r['age'] == '25'){ echo "selected";} ?>>25</option>
</select>
</div>
</div>
<input type="submit" class="btn btn-primary col-md-2 col-md-offset-10" value="Update" />
</form>

Displaying information in the form is completed, next thing is updating the record after form submission.

This update operation is same as the insert operation and the only difference is here we will use only update SQL query instead of insert SQL query everything is similar.

You can go through the code the only difference is SQL query.

PHP
1
2
3
4
5
6
7
8
9
10
11
$sql = "UPDATE crud SET firstname=:firstname, lastname=:lastname, email=:email, gender=:gender, age=:age WHERE id=:id";
$result = $db->prepare($sql);
$res = $result->execute(
array(
':firstname' => $_POST['fname'],
':lastname' => $_POST['lname'],
'email' => $_POST['email'],
'gender' => $_POST['gender'],
'age' => $_POST['age'],
'id' => $_GET['id']
));

You can display success or failure message same as the index page. Here is the code

PHP
1
2
3
4
5
if($res){
echo "Successfully updated data";
}else{
echo "failed to update data";
}

7. Deleting Records with PHP PDO

Delete Operation is same as it suggests, it deletes the record from the database table. We will pass the id in the URL, record will be deleted. Here also for the delete operation, I’ll use prepared statements because id is passed through the URL.

This delete query is also same as the select query in update.php file. Instead of select SQL query, here I’ll be using delete SQL query with anonymous placeholders.

And the final code of delete.php file is

PHP
1
2
3
$DelSql = "DELETE FROM `crud` WHERE id=?";
$result = $db->prepare($DelSql);
$res = $result->execute(array($_GET['id']));

If the delete query is successful, we will redirect the user to view.php or else we will display an error message. Here is the code to redirect and display an error message.

PHP
1
2
3
4
5
if($res){
header('location: view.php');
}else{
echo "Failed to Delete Record";
}

Complete Source Code of PHP PDO CRUD Application

In case if you have any problem with arranging above pieces of code, you can use this complete code.

Conclusion

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.

PHP PDO CRUD Application

Download PHP PDO CRUD Application for Free

Just Enter Your Correct Name & E-Mail
You will receive it through E-Mail

Related Posts

Complete User Authentication System in CodeIgniter – 9 Steps

Do you want to Develop Content Management System in PHP?

8 Simple Steps to Create CRUD Application in OOP PHP

Develop Secure Applications with PHP PDO – Ultimate Guide

Learn How to Improve PHP Security with these 6 Steps?

Do you Know How to Secure PHP Forms with CSRF Tokens?

8 Simple Steps to Create (Pretty) Clean URL’s with PHP & HTACCESS

6 Steps to Create a File Upload System Script in CodeIgniter

Vivek Vengala
 

Vivek Vengala is a Online Entrepreneur, Web Developer from Hyderabad India.

Click Here to Leave a Comment Below 4 comments
kelechi

You did not fulfill your promise to ME after imputing my name and email for free script code and there was no link for download.

Reply
    Vivek Vengala

    Hi Kelechi,

    I just checked you are receiving the emails & opened few of them. In case if you are not able to see the emails in the inbox, check in other tabs in your email.

    Reply
joy

hi! would like to ask if there is a youtube demo for this code? Thanks =)

Reply
    Vivek Vengala

    No demo is available right now, I’ll upload demo video soon…

    Reply

Leave a Reply: