HTML CSS JavaScript jQuery PHP Python MySQL

CRUD Application with PHP, PDO, and MySQL

Updated on by David Adams

CRUD Application with PHP, PDO, and MySQL

In this tutorial we'll be creating a complete Create, Read, Update, and Delete application with PHP, PDO, and MySQL. We'll be creating the app completely from scratch, no frameworks required (such as Laravel, etc).

A CRUD app is often used in conjunction with a database, interacting with records in a table. We'll be using MySQL as our database management system in our app.

We'll create a database with a contacts table, these contacts we can interact within our CRUD app, the contacts table will contain names, emails, phone numbers, etc.

1. Getting Started

Before we jump into programming our CRUD app we need to install our web server and set-up our app.

1.1. What You Will Learn in this Tutorial

  • Create MySQL Records — Insert new records into the Contacts table.
  • Read MySQL Records — Reading MySQL records and display them in an HTML table.
  • Update MySQL Records — Update existing MySQL records in the Contacts table.
  • Delete MySQL Records — Confirm and delete records from the Contacts table.
  • GET and POST Requests — Send data to our app from an HTML form and URL parameters.
  • Prepared Statements — Secure our SQL statements with prepared statements.

1.2. Requirements

  • Web Server — I recommend you download and install XAMPP on your local computer system, this server package includes MySQL, PHP, and the PDO extension.
  • PHP — I recommend you use the latest version of PHP, but older versions should work just fine (skip if you installed XAMPP).
  • PDO Extension — Should be enabled by default if you're using XAMPP, but if it's not you'll need to enable/install it.

1.3. File Structure & Setup

Navigate to C:\xampp\htdocs (XAMPP) and create the below folders and files.

File Structure

\-- phpcrud
  |-- index.php
  |-- create.php
  |-- read.php
  |-- update.php
  |-- delete.php
  |-- functions.php
  |-- style.css

Explanation of each file:

  • index.php — Home page for our CRUD app.
  • create.php — Create new records with an HTML form and send data via a POST request.
  • read.php — Display records from our database table and navigate with pagination.
  • update.php — Update existing records with an HTML form and send data via a POST request.
  • delete.php — Confirm and delete records by ID, using a GET request to get the ID.
  • functions.php — Basic templating functions and MySQL connection function (so we don't have to repeat code in every file).
  • style.css — The stylesheet for our app, this will change the appearance of our app.

2. Creating the Database and setting-up Tables

The MySQL database we'll use to store contacts and retrieve them with PHP. If you're using XAMPP follow the below instructions.

  • Navigate to http://localhost/phpmyadmin/
  • Click Databases at the top
  • Under Create database input phpcrud and select utf8_general_ci as the collation
  • Click Create
  • Select the newly created database
  • Click the SQL tab and execute the below SQL:
CREATE TABLE IF NOT EXISTS `contacts` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
  	`name` varchar(255) NOT NULL,
  	`email` varchar(255) NOT NULL,
  	`phone` varchar(255) NOT NULL,
  	`title` varchar(255) NOT NULL,
  	`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `contacts` (`id`, `name`, `email`, `phone`, `title`, `created`) VALUES
(1, 'John Doe', 'johndoe@example.com', '2026550143', 'Lawyer', '2019-05-08 17:32:00'),
(2, 'David Deacon', 'daviddeacon@example.com', '2025550121', 'Employee', '2019-05-08 17:28:44'),
(3, 'Sam White', 'samwhite@example.com', '2004550121', 'Employee', '2019-05-08 17:29:27'),
(4, 'Colin Chaplin', 'colinchaplin@example.com', '2022550178', 'Supervisor', '2019-05-08 17:29:27'),
(5, 'Ricky Waltz', 'rickywaltz@example.com', '7862342390', '', '2019-05-09 19:16:00'),
(6, 'Arnold Hall', 'arnoldhall@example.com', '5089573579', 'Manager', '2019-05-09 19:17:00'),
(7, 'Toni Adams', 'alvah1981@example.com', '2603668738', '', '2019-05-09 19:19:00'),
(8, 'Donald Perry', 'donald1983@example.com', '7019007916', 'Employee', '2019-05-09 19:20:00'),
(9, 'Joe McKinney', 'nadia.doole0@example.com', '6153353674', 'Employee', '2019-05-09 19:20:00'),
(10, 'Angela Horst', 'angela1977@example.com', '3094234980', 'Assistant', '2019-05-09 19:21:00'),
(11, 'James Jameson', 'james1965@example.com', '4002349823', 'Assistant', '2019-05-09 19:32:00'),
(12, 'Daniel Deacon', 'danieldeacon@example.com', '5003423549', 'Manager', '2019-05-09 19:33:00');

The above SQL will create the table: contacts, we'll be using this table in our app, included in the SQL is sample data, this is so we can see what our CRUD app will look like.

There are 6 columns in the contacts table (id, name, email, phone, title, and created), the title column is basically the role of each contact, you can change this to anything you wish, the sample data will use work roles as an example.

In phpMyAdmin, the database should look like the following:

phpMyAdmin CRUD Table

3. Creating the Stylesheet (CSS3)

The stylesheet will change the appearance of our app, edit style.css and add the following code:

* {
  	box-sizing: border-box;
  	font-family: -apple-system, BlinkMacSystemFont, "segoe ui", roboto, oxygen, ubuntu, cantarell, "fira sans", "droid sans", "helvetica neue", Arial, sans-serif;
  	font-size: 16px;
  	-webkit-font-smoothing: antialiased;
  	-moz-osx-font-smoothing: grayscale;
}
body {
  	background-color: #FFFFFF;
  	margin: 0;
}
.navtop {
  	background-color: #3f69a8;
  	height: 60px;
  	width: 100%;
  	border: 0;
}
.navtop div {
  	display: flex;
  	margin: 0 auto;
  	width: 1000px;
  	height: 100%;
}
.navtop div h1, .navtop div a {
  	display: inline-flex;
  	align-items: center;
}
.navtop div h1 {
  	flex: 1;
  	font-size: 24px;
  	padding: 0;
  	margin: 0;
  	color: #ecf0f6;
  	font-weight: normal;
}
.navtop div a {
  	padding: 0 20px;
  	text-decoration: none;
  	color: #c5d2e5;
  	font-weight: bold;
}
.navtop div a i {
  	padding: 2px 8px 0 0;
}
.navtop div a:hover {
  	color: #ecf0f6;
}
.content {
  	width: 1000px;
  	margin: 0 auto;
}
.content h2 {
  	margin: 0;
  	padding: 25px 0;
  	font-size: 22px;
  	border-bottom: 1px solid #ebebeb;
  	color: #666666;
}
.read .create-contact {
  	display: inline-block;
  	text-decoration: none;
  	background-color: #38b673;
  	font-weight: bold;
  	font-size: 14px;
  	color: #FFFFFF;
  	padding: 10px 15px;
  	margin: 15px 0;
}
.read .create-contact:hover {
  	background-color: #32a367;
}
.read .pagination {
  	display: flex;
  	justify-content: flex-end;
}
.read .pagination a {
  	display: inline-block;
  	text-decoration: none;
  	background-color: #a5a7a9;
  	font-weight: bold;
  	color: #FFFFFF;
  	padding: 5px 10px;
  	margin: 15px 0 15px 5px;
}
.read .pagination a:hover {
  	background-color: #999b9d;
}
.read table {
  	width: 100%;
  	padding-top: 30px;
  	border-collapse: collapse;
}
.read table thead {
  	background-color: #ebeef1;
  	border-bottom: 1px solid #d3dae0;
}
.read table thead td {
  	padding: 10px;
  	font-weight: bold;
  	color: #767779;
  	font-size: 14px;
}
.read table tbody tr {
  	border-bottom: 1px solid #d3dae0;
}
.read table tbody tr:nth-child(even) {
  	background-color: #fbfcfc;
}
.read table tbody tr:hover {
  	background-color: #376ab7;
}
.read table tbody tr:hover td {
  	color: #FFFFFF;
}
.read table tbody tr:hover td:nth-child(1) {
  	color: #FFFFFF;
}
.read table tbody tr td {
  	padding: 10px;
}
.read table tbody tr td:nth-child(1) {
  	color: #a5a7a9;
}
.read table tbody tr td.actions {
  	padding: 8px;
  	text-align: right;
}
.read table tbody tr td.actions .edit, .read table tbody tr td.actions .trash {
  	display: inline-flex;
  	text-align: right;
  	text-decoration: none;
  	color: #FFFFFF;
  	padding: 10px 12px;
}
.read table tbody tr td.actions .trash {
  	background-color: #b73737;
}
.read table tbody tr td.actions .trash:hover {
  	background-color: #a33131;
}
.read table tbody tr td.actions .edit {
  	background-color: #37afb7;
}
.read table tbody tr td.actions .edit:hover {
  	background-color: #319ca3;
}
.update form {
  	padding: 15px 0;
  	display: flex;
  	flex-flow: wrap;
}
.update form label {
  	display: inline-flex;
  	width: 400px;
  	padding: 10px 0;
  	margin-right: 25px;
}
.update form input {
  	padding: 10px;
  	width: 400px;
  	margin-right: 25px;
  	margin-bottom: 15px;
  	border: 1px solid #cccccc;
}
.update form input[type="submit"] {
  	display: block;
  	background-color: #38b673;
  	border: 0;
  	font-weight: bold;
  	font-size: 14px;
  	color: #FFFFFF;
  	cursor: pointer;
  	width: 200px;
	margin-top: 15px;
}
.update form input[type="submit"]:hover {
  	background-color: #32a367;
}
.delete .yesno {
  	display: flex;
}
.delete .yesno a {
  	display: inline-block;
  	text-decoration: none;
  	background-color: #38b673;
  	font-weight: bold;
  	color: #FFFFFF;
  	padding: 10px 15px;
  	margin: 15px 10px 15px 0;
}
.delete .yesno a:hover {
  	background-color: #32a367;
}

Feel free to change the style, this is something I quickly put together to make the CRUD app more appealing.

4. Creating the CRUD App

We can finally start to code the CRUD app with PHP. Before we get started, make sure you followed the previous steps and have the MySQL database ready.

4.1. Creating the Functions

This file will contain functions that we can execute in all our PHP files, this is so we don't have to write the same code in every PHP file, shorter the code, the better, right? We'll create 3 functions, 1 function will connect to the database, the other 2 will be the templates for the header and footer that will appear on every page we create.

Edit functions.php and add the following code:

<?php
function pdo_connect_mysql() {
    $DATABASE_HOST = 'localhost';
    $DATABASE_USER = 'root';
    $DATABASE_PASS = '';
    $DATABASE_NAME = 'phpcrud';
    try {
    	return new PDO('mysql:host=' . $DATABASE_HOST . ';dbname=' . $DATABASE_NAME . ';charset=utf8', $DATABASE_USER, $DATABASE_PASS);
    } catch (PDOException $exception) {
    	// If there is an error with the connection, stop the script and display the error.
    	die ('Failed to connect to database!');
    }
}
function template_header($title) {
echo <<<EOT
<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title>$title</title>
		<link href="style.css" rel="stylesheet" type="text/css">
		<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.1/css/all.css">
	</head>
	<body>
    <nav class="navtop">
    	<div>
    		<h1>Website Title</h1>
            <a href="index.php"><i class="fas fa-home"></i>Home</a>
    		<a href="read.php"><i class="fas fa-address-book"></i>Contacts</a>
    	</div>
    </nav>
EOT;
}
function template_footer() {
echo <<<EOT
    </body>
</html>
EOT;
}
?>

Make sure to change the MySQL connection details to your details, we're using PDO to connect to MySQL, PDO will make it easier for us to interact with our MySQL database.

4.2. Creating the Home Page

The home page is the first thing our users will see, by default when you navigate to http://localhost/phpcrud/ it will serve the index.php file.

Edit index.php and add the following code:

<?php
include 'functions.php';
// Your PHP code here.

// Home Page template below.
?>

<?=template_header('Home')?>

<div class="content">
	<h2>Home</h2>
	<p>Welcome to the home page!</p>
</div>

<?=template_footer()?>

This will create a basic home page, we can use this page to navigate to the other pages. As you can see we include the functions.php file and execute the template functions that we created, remember that these functions will add the header and footer code to our home page.

Now if we navigate to http://localhost/phpcrud/ we'll see the following:

http://localhost/phpcrud/index.php
CRUD Home Page

That's basically it for the home page, feel free to add your own content, this page is just so we can navigate to the other pages.

4.3. Creating the Read Page

This page will display records from our contacts table in an HTML table.

Edit read.php and add the following code:

<?php
include 'functions.php';
// Connect to MySQL database
$pdo = pdo_connect_mysql();
// Get the page via GET request (URL param: page), if non exists default the page to 1
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? (int)$_GET['page'] : 1;
// Number of records to show on each page
$records_per_page = 5;

Once again we include the functions file, but this time we connect to our MySQL database by calling the function: pdo_connect_mysql, if the connection is successful we can use the $pdo variable to execute queries.

We also create 2 more variables, the $page variable will determine the page that the user is on, the $records_per_page will be used to limit the number of records to display on each page, for example, if we limit the number of records to 5 and we have 10 records in our contacts table, then there will only be 2 pages and 5 records on each page, the user will be able to navigate between pages.

Add the following code to read.php:

// Prepare the SQL statement and get records from our contacts table, LIMIT will determine the page
$stmt = $pdo->prepare('SELECT * FROM contacts ORDER BY id LIMIT :current_page, :record_per_page');
$stmt->bindValue(':current_page', ($page-1)*$records_per_page, PDO::PARAM_INT);
$stmt->bindValue(':record_per_page', $records_per_page, PDO::PARAM_INT);
$stmt->execute();
// Fetch the records so we can display them in our template.
$contacts = $stmt->fetchAll(PDO::FETCH_ASSOC);

The above code will select records from the contacts table, this is determined by the current page the user is on, the records will be ordered by the id column, we can easily change the column if we wanted to, for example, we could change it to created, this will sort the records by the create date.

We're also using a prepared statement for the above query, this will make sure our query is secure (escape user input data).

Add the following code to read.php:

// Get the total number of contacts, this is so we can determine whether there should be a next and previous button
$num_contacts = $pdo->query('SELECT COUNT(*) FROM contacts')->fetchColumn();
?>

The above SQL query will get the total number of records in the contacts table, we don't need to use a prepared statement here because the query doesn't include user input variables.

Add the following code to read.php:

<?=template_header('Read')?>

<div class="content read">
	<h2>Read Contacts</h2>
	<a href="create.php" class="create-contact">Create Contact</a>
	<table>
        <thead>
            <tr>
                <td>#</td>
                <td>Name</td>
                <td>Email</td>
                <td>Phone</td>
                <td>Title</td>
                <td>Created</td>
                <td></td>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($contacts as $contact): ?>
            <tr>
                <td><?=$contact['id']?></td>
                <td><?=$contact['name']?></td>
                <td><?=$contact['email']?></td>
                <td><?=$contact['phone']?></td>
                <td><?=$contact['title']?></td>
                <td><?=$contact['created']?></td>
                <td class="actions">
                    <a href="update.php?id=<?=$contact['id']?>" class="edit"><i class="fas fa-pen fa-xs"></i></a>
                    <a href="delete.php?id=<?=$contact['id']?>" class="trash"><i class="fas fa-trash fa-xs"></i></a>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
	<div class="pagination">
		<?php if ($page > 1): ?>
		<a href="read.php?page=<?=$page-1?>"><i class="fas fa-angle-double-left fa-sm"></i></a>
		<?php endif; ?>
		<?php if ($page*$records_per_page < $num_contacts): ?>
		<a href="read.php?page=<?=$page+1?>"><i class="fas fa-angle-double-right fa-sm"></i></a>
		<?php endif; ?>
	</div>
</div>

<?=template_footer()?>

This is the template for the read page, the code iterates the contacts and adds them to the HTML table, we'll be able to read the records in a table format when we navigate to the read page.

Pagination is added so we can navigate between pages on the read page (page 1, page 2, etc).

For the icons we're using Font Awesome, make sure that it's included in the header template function or the icons will not appear.

And now if we navigate to http://localhost/phpcrud/read.php we'll see the following:

http://localhost/projects/phpcrud/read.php
CRUD Read

So now we know how to display records from our MySQL database, take note that the buttons that appear in the table (create, edit, and delete) will not work, that's because we haven't created those pages yet.

You can also click the Contacts link in the header bar, this will navigate to the read page.

4.4. Creating the Create Page

The create page will be used to create new records and insert them into our Contacts table.

Edit create.php and add:

<?php
include 'functions.php';
$pdo = pdo_connect_mysql();
$msg = '';
// Check if POST data is not empty
if (!empty($_POST)) {
    // Post data not empty insert a new record
    // Set-up the variables that are going to be inserted, we must check if the POST variables exist if not we can default them to blank
    $id = isset($_POST['id']) && !empty($_POST['id']) && $_POST['id'] != 'auto' ? $_POST['id'] : NULL;
    // Check if POST variable "name" exists, if not default the value to blank, basically the same for all variables
    $name = isset($_POST['name']) ? $_POST['name'] : '';
    $email = isset($_POST['email']) ? $_POST['email'] : '';
    $phone = isset($_POST['phone']) ? $_POST['phone'] : '';
    $title = isset($_POST['title']) ? $_POST['title'] : '';
    $created = isset($_POST['created']) ? $_POST['created'] : date('Y-m-d H:i:s');
    // Insert new record into the contacts table
    $stmt = $pdo->prepare('INSERT INTO contacts VALUES (?, ?, ?, ?, ?, ?)');
    $stmt->execute([$id, $name, $email, $phone, $title, $created]);
    // Output message
    $msg = 'Created Successfully!';
}
?>

The above code will check if the POST array is not empty if it's not then it basically means the user has filled out the form and clicked the submit button, this will then insert a new record into our Contacts table.

Add after:

<?=template_header('Create')?>

<div class="content update">
	<h2>Create Contact</h2>
    <form action="create.php" method="post">
        <label for="id">ID</label>
        <label for="name">Name</label>
        <input type="text" name="id" placeholder="26" value="auto" id="id">
        <input type="text" name="name" placeholder="John Doe" id="name">
        <label for="email">Email</label>
        <label for="phone">Phone</label>
        <input type="text" name="email" placeholder="johndoe@example.com" id="email">
        <input type="text" name="phone" placeholder="2025550143" id="phone">
        <label for="title">Title</label>
        <label for="created">Created</label>
        <input type="text" name="title" placeholder="Employee" id="title">
        <input type="datetime-local" name="created" value="<?=date('Y-m-d\TH:i')?>" id="created">
        <input type="submit" value="Create">
    </form>
    <?php if ($msg): ?>
    <p><?=$msg?></p>
    <?php endif; ?>
</div>

<?=template_footer()?>

This is the template for our create page, as you can see we have created a form and named each input field accordingly, the name of the input field is how we'll get the POST variable in our PHP code, for example, if we name an input field "zip_code", we can get the value of that input field with $_POST['zip_code'] in PHP (assuming the form's method is set to post).

And now if we navigate to http://localhost/phpcrud/create.php or click the Create button on the read page we'll see the following:

http://localhost/phpcrud/create.php
CRUD Create

4.5. Creating the Update Page

The update page will be used to update records in our Contacts table, this page is similar to the create page but instead of inserting a new record we will be updating existing records that will be determined by the ID from a GET request.

Edit update.php and add:

<?php
include 'functions.php';
$pdo = pdo_connect_mysql();
$msg = '';
// Check if the contact id exists, for example update.php?id=1 will get the contact with the id of 1
if (isset($_GET['id'])) {
    if (!empty($_POST)) {
        // This part is similar to the create.php, but instead we update a record and not insert
        $id = isset($_POST['id']) ? $_POST['id'] : NULL;
        $name = isset($_POST['name']) ? $_POST['name'] : '';
        $email = isset($_POST['email']) ? $_POST['email'] : '';
        $phone = isset($_POST['phone']) ? $_POST['phone'] : '';
        $title = isset($_POST['title']) ? $_POST['title'] : '';
        $created = isset($_POST['created']) ? $_POST['created'] : date('Y-m-d H:i:s');
        // Update the record
        $stmt = $pdo->prepare('UPDATE contacts SET id = ?, name = ?, email = ?, phone = ?, title = ?, created = ? WHERE id = ?');
        $stmt->execute([$id, $name, $email, $phone, $title, $created, $_GET['id']]);
        $msg = 'Updated Successfully!';
    }
    // Get the contact from the contacts table
    $stmt = $pdo->prepare('SELECT * FROM contacts WHERE id = ?');
    $stmt->execute([$_GET['id']]);
    $contact = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$contact) {
        die ('Contact doesn\'t exist with that ID!');
    }
} else {
    die ('No ID specified!');
}
?>

The above code will check for the contact ID, the ID will be a parameter in the URL, for example, http://localhost/phpcrud/update.php?id=1 will get the contact with the ID of 1, we then handle this with a GET request and a MySQL query that will get the contact by ID.

Add after:

<?=template_header('Read')?>

<div class="content update">
	<h2>Update Contact #<?=$contact['id']?></h2>
    <form action="update.php?id=<?=$contact['id']?>" method="post">
        <label for="id">ID</label>
        <label for="name">Name</label>
        <input type="text" name="id" placeholder="1" value="<?=$contact['id']?>" id="id">
        <input type="text" name="name" placeholder="John Doe" value="<?=$contact['name']?>" id="name">
        <label for="email">Email</label>
        <label for="phone">Phone</label>
        <input type="text" name="email" placeholder="johndoe@example.com" value="<?=$contact['email']?>" id="email">
        <input type="text" name="phone" placeholder="2025550143" value="<?=$contact['phone']?>" id="phone">
        <label for="title">Title</label>
        <label for="created">Created</label>
        <input type="text" name="title" placeholder="Employee" value="<?=$contact['title']?>" id="title">
        <input type="datetime-local" name="created" value="<?=date('Y-m-d\TH:i', strtotime($contact['created']))?>" id="created">
        <input type="submit" value="Update">
    </form>
    <?php if ($msg): ?>
    <p><?=$msg?></p>
    <?php endif; ?>
</div>

<?=template_footer()?>

This is the template for the update page, the input values are already specified with the contact columns, the MySQL query we created previously will get those values.

On the read page (Contacts), we should be able to click the update icon next to a record and update it, like the following:

http://localhost/phpcrud/update.php?id=1
CRUD Update

4.6. Creating the Delete Page

The delete page will be used to delete records from the Contacts table. Before a user can delete a record they will need to confirm it, this will prevent accidental deletion.

Edit delete.php and add:

<?php
include 'functions.php';
$pdo = pdo_connect_mysql();
$msg = '';
// Check that the contact ID exists
if (isset($_GET['id'])) {
    // Select the record that is going to be deleted
    $stmt = $pdo->prepare('SELECT * FROM contacts WHERE id = ?');
    $stmt->execute([$_GET['id']]);
    $contact = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$contact) {
        die ('Contact doesn\'t exist with that ID!');
    }
    // Make sure the user confirms beore deletion
    if (isset($_GET['confirm'])) {
        if ($_GET['confirm'] == 'yes') {
            // User clicked the "Yes" button, delete record
            $stmt = $pdo->prepare('DELETE FROM contacts WHERE id = ?');
            $stmt->execute([$_GET['id']]);
            $msg = 'You have deleted the contact!';
        } else {
            // User clicked the "No" button, redirect them back to the read page
            header('Location: read.php');
            exit;
        }
    }
} else {
    die ('No ID specified!');
}
?>

To delete a record the code will check if the GET request variable "id" exists, if it does then check if the record exists in the Contacts table and confirm the user if they would like to delete the contact or not, a simple GET request will determine which button the user clicked.

On the read page (Contacts) click the delete button on one of the records, you should see something like the following:

http://localhost/phpcrud/delete.php?id=1
CRUD Delete

Conclusion

Congratulations! You have successfully created a CRUD app with PHP & MySQL, what next? Consider adding your own columns to the Contacts table and adding those columns to the code.

If you enjoyed this tutorial don't forget to share using the social links below and check out our many more tutorials on our website.