To-Do List Application Using PHP, MySQL and AJAX
To-Do lists are a great way to keep track of your daily tasks. In this tutorial, we will build our own to-do list using PHP, MySQL, and AJAX. This tutorial assumes that you have a basic understanding of HTML, PHP, MySQL, and jQuery. We will cover this tutorial in easy steps with a live demo of the To-Do List Application Using PHP & MySQL.
So let’s implement To-Do List Application Using PHP & MySQL. Look following folder and file structure:
- to-do-list-application-using-php-and-mysql
- assets
- css
- style.css
- js
- todo.js
- css
- templates
- header.php
- footer.php
- class
- DBConnection.php
- ToDoList.php
- index.php
- action.php
- assets
Step 1: Create MySQL Database Table
First, we will create database with the following table.
CREATE TABLE `todo` (
`id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`status` int(1) NOT NULL,
`created_date` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `todo`
ADD PRIMARY KEY (`id`);
ALTER TABLE `todo`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
Step 3: Database Connection class
Create a class file named
DBConnection.php inside “class/” folder.
_conn = new PDO("mysql:host=$this->_dbHostName;dbname=$this->_dbName", $this->_dbUserName, $this->_dbPassword, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
// set the PDO error mode to exception
$this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Error connecting to MySQL server: " . $e->getMessage();
}
}
// return Connection
public function returnConnection()
{
return $this->_conn;
}
}
?>
Step 4: Create a class file named
ToDoList.php inside “class/” folder.Manage CRUD Operations
- CREATE – INSERT record in database.
- READ – read records from database.
- UPDATE – Executes an UPDATE record in database
- DELETE – delete specified records from databse.
_todoID = $todoID;
}
public function setTitle($title)
{
$this->_title = $title;
}
public function setDescription($description)
{
$this->_description = $description;
}
public function setStatus($status)
{
$this->_status = $status;
}
// __construct
public function __construct()
{
$this->db = new DBConnection();
$this->db = $this->db->returnConnection();
}
// create ToDo List
public function createToDo()
{
try {
$sql = 'INSERT INTO todo (`title`, `status`) VALUES (:title, :status)';
$data = [
'title' => $this->_title,
'status' => $this->_status,
];
$stmt = $this->db->prepare($sql);
$stmt->execute($data);
$lastID = $this->db->lastInsertId();
return $lastID;
} catch (Exception $err) {
die("Error description: " . $err);
}
}
// update ToDo List
public function updateToDo()
{
try {
$sql = "UPDATE todo SET status=:status WHERE id=:todo_id";
$data = [
'status' => $this->_status,
'todo_id' => $this->_todoID
];
$stmt = $this->db->prepare($sql);
$stmt->execute($data);
$status = $stmt->rowCount();
return $status;
} catch (Exception $err) {
die("Error description: " . $err);
}
}
// getAll ToDo List
public function getAllToDo()
{
try {
$sql = "SELECT id, title, status FROM todo WHERE status != :status";
$stmt = $this->db->prepare($sql);
$data = [
'status' => $this->_status
];
$stmt->execute($data);
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
return $result;
} catch (Exception $err) {
die("Error description: " . $err);
}
}
// delete ToDo List
public function deleteToDo()
{
try {
$sql = "DELETE FROM todo WHERE id=:todo_id";
$stmt = $this->db->prepare($sql);
$data = [
'todo_id' => $this->_todoID
];
$stmt->execute($data);
$status = $stmt->rowCount();
return $status;
} catch (Exception $err) {
die("Error description: " . $err);
}
}
}
?>
Step 5: Create action file named action.php
Manage request and return as JSON data
setTitle($_postData['title']);
$todo->setStatus(0);
$status = $todo->createToDo();
if (!empty($status)) {
$json['msg'] = 'success';
$json['todo_id'] = $status;
} else {
$json['msg'] = 'failed';
$json['todo_id'] = '';
}
header('Content-Type: application/json');
echo json_encode($json);
}
// update todo record in the database
if (!empty($_postData['action']) && $_postData['action'] == "update") {
$todo->setToDoID($_postData['todo_id']);
$todo->setStatus($_postData['status']);
$status = $todo->updateToDo();
if (!empty($status)) {
$json['msg'] = 'success';
} else {
$json['msg'] = 'failed';
}
header('Content-Type: application/json');
echo json_encode($json);
}
// delete todo list from the database
if (!empty($_postData['action']) && $_postData['action'] == "delete") {
$todo->setToDoID($_postData['todo_id']);
$status = $todo->deleteToDo();
if (!empty($status)) {
$json['msg'] = 'success';
} else {
$json['msg'] = 'failed';
}
header('Content-Type: application/json');
echo json_encode($json);
}
?>
?>
Step 6: Create action file named index.php
We need to do is lay-out the structure for our main index page index.php
setStatus(2);
$todoInfo = $todo->getAllToDo();
include('templates/header.php');
?>
To-Do List Application Using PHP & MySQL
ToDo List
$element) {
if (!empty($element['status']) && $element['status'] == 1) {
$class = 'class="completed"';
$checked = 'checked="checked"';
} else {
$class = '';
$checked = '';
}
?>
-
data-utodoid="">
Create header.php and footer.php section of the webpage. The Bootstrap library is used to provide a better UI, so, include it in the header and footer section.
header.php
To-Do List Application Using PHP & MySQL | WebHaunt
footer.php