Simple CRUD Operations in PHP Using MySQL Part 2
1 year ago
admin
PHP
In the second part of this tutorial, we will perform the operations using PHP and PDO first, we will see how to create a task and then we will see how to update and delete a selected task.
Form validation
Before we proceed to perform actions let's create a new folder 'helpers' inside let's add a new file 'form_validate.php' here we validate the form fields before submitting.
<?php
if (!$title) {
$errors[] = "The title field is required.";
}
if (!$body) {
$errors[] = "The body field is required.";
}
Create task
Next, let's create a new folder 'public' inside let's add a new file 'create.php' here we have the form for creating tasks.
<?php require_once('../layouts/header.php'); ?>
<?php
$title = '';
$body = '';
$errors = [];
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$title = $_POST['title'];
$body = $_POST['body'];
require_once('../helpers/form_validate.php');
if (!$errors) {
$sql = 'INSERT INTO tasks(title, body) VALUES(:title, :body)';
$stmt = $conn->prepare($sql);
$stmt->execute([
':title' => $title,
':body' => $body
]);
header('Location:index.php');
}
}
?>
<div class="container">
<div class="row my-4">
<div class="col-md-10 mx-auto">
<?php if ($errors) : ?>
<?php foreach ($errors as $error) : ?>
<div class="alert alert-danger">
<?php echo $error; ?>
</div>
<?php endforeach; ?>
<?php endif; ?>
<div class="card">
<div class="card-header bg-white d-flex justify-content-between align-items-center">
<h3 class="mt-2">Add new task</h3>
<a href="index.php" class="btn btn-secondary">
<i class="fas fa-home"></i> Back
</a>
</div>
<div class="card-body">
<form method="post">
<div class="mb-3">
<input type="text" name="title" placeholder="Title*"
value="<?php echo $title; ?>" class="form-control">
</div>
<div class="mb-3">
<textarea rows="5" cols="30" name="body"
placeholder="Body*" class="form-control"><?php echo $body; ?></textarea>
</div>
<div class="mb-3">
<button type="submit" class="btn btn-primary">
Submit
</button>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
<?php require_once('../layouts/footer.php'); ?>
Update task
Next, inside 'public' let's add a new file 'update.php' here we have the form for updating tasks.
<?php require_once('../layouts/header.php'); ?>
<?php
if (!$_GET['id']) {
header('Location:index.php');
}
$task_id = $_GET['id'];
$stmt = $conn->prepare("SELECT * FROM tasks WHERE id = :task_id");
$stmt->bindParam(':task_id', $task_id, PDO::PARAM_INT);
$stmt->execute();
// set the resulting array to associative
$task = $stmt->fetch(PDO::FETCH_ASSOC);
$title = $task['title'];
$body = $task['body'];
$done = $task['done'];
$errors = [];
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$title = $_POST['title'];
$body = $_POST['body'];
$done = $_POST['done'] ?? 0;
require_once('../helpers/form_validate.php');
if (!$errors) {
$sql = 'UPDATE tasks SET title = :title,body =:body, done =:done
WHERE id = :task_id';
// prepare statement
$statement = $conn->prepare($sql);
// bind params
$statement->bindParam(':task_id', $task_id, PDO::PARAM_INT);
$statement->bindParam(':title', $title);
$statement->bindParam(':body', $body);
$statement->bindParam(':done', $done);
$statement->execute();
header('Location:index.php');
}
}
?>
<div class="container">
<div class="row my-4">
<div class="col-md-10 mx-auto">
<?php if ($errors) : ?>
<?php foreach ($errors as $error) : ?>
<div class="alert alert-danger">
<?php echo $error; ?>
</div>
<?php endforeach; ?>
<?php endif; ?>
<div class="card">
<div class="card-header bg-white d-flex justify-content-between align-items-center">
<h3 class="mt-2">Update task</h3>
<a href="index.php" class="btn btn-secondary">
<i class="fas fa-home"></i> Back
</a>
</div>
<div class="card-body">
<form method="post">
<div class="mb-3">
<input type="text" name="title" placeholder="Title*"
value="<?php echo $title; ?>" class="form-control">
</div>
<div class="mb-3">
<textarea rows="5" cols="30" name="body"
placeholder="Body*" class="form-control"><?php echo $body; ?></textarea>
</div>
<div class="mb-3">
<input type="checkbox" name="done"
<?php if ($done): ?> checked <?php endif; ?>
value="1"
class="form-check-input">
</div>
<div class="mb-3">
<button type="submit" class="btn btn-primary">
Submit
</button>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
<?php require_once('../layouts/footer.php'); ?>
Delete task
Next, inside 'public' let's add a new file 'delete.php' here we have the code for deleting tasks.
<?php
require_once('../database/database.php');
if (!$_GET['id']) {
header('Location:index.php');
}
$task_id = $_GET['id'];
// construct the delete statement
$sql = 'DELETE FROM tasks WHERE id = :task_id';
// prepare the statement for execution
$stmt = $conn->prepare($sql);
$stmt->bindParam(':task_id', $task_id, PDO::PARAM_INT);
// execute the statement
if ($stmt->execute()) {
header('Location:index.php');
}
?>
Display tasks
Next, inside 'public' let's add a new file 'index.php' here we have the code for displaying tasks.
<?php require_once('../layouts/header.php'); ?>
<?php
$stmt = $conn->prepare("SELECT * FROM tasks");
$stmt->execute();
// set the resulting array to associative
$tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="container">
<div class="row my-4">
<div class="col-md-10 mx-auto">
<div class="my-3">
<a href="create.php" class="btn btn-primary">
<i class="fas fa-plus"></i> Create
</a>
</div>
<div class="card">
<div class="card-body">
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Title</th>
<th scope="col">Body</th>
<th scope="col">Done</th>
<th scope="col"></th>
</tr>
</thead>
<tbody>
<?php foreach($tasks as $key => $task) :?>
<tr>
<th scope="row"><?php echo $key += 1; ?></th>
<td><?php echo $task['title']; ?></td>
<td><?php echo $task['body']; ?></td>
<td>
<?php if ($task['done']): ?>
<span class="badge bg-success p-2">
Done
</span>
<?php else: ?>
<span class="badge bg-danger p-2">
Pending...
</span>
<?php endif; ?>
</td>
<td>
<a href="update.php?id=<?php echo $task['id']; ?>" class="btn btn-sm btn-warning">
<i class="fas fa-edit"></i>
</a>
<a href="delete.php?id=<?php echo $task['id']; ?>" class="btn btn-sm btn-danger">
<i class="fas fa-trash"></i>
</a>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
<?php require_once('../layouts/footer.php'); ?>