A simple todo list application that takes tasks submitted by user in a form and saves them in a MySQL database. The tasks are also retrieved from the database and displayed on the web page with a delete button beside each task. When the delete button is clicked, the task is deleted from the database.
That's what we will be building in this tutorial.
As usual, let's create our database. Create a database called todo and in it, create a table called tasks. Tasks table has only two fields namely:
- id - int(10)
- task - varchar(255)
Now create two files:
- index.php
- style.css
Open them up in a text editor and paste the following code inside index.php file:
<!DOCTYPE html>
<html>
<head>
<title>ToDo List Application PHP and MySQL</title>
</head>
<body>
<div class="heading">
<h2 style="font-style: 'Hervetica';">ToDo List Application PHP and MySQL database</h2>
</div>
<form method="post" action="index.php" class="input_form">
<input type="text" name="task" class="task_input">
<button type="submit" name="submit" id="add_btn" class="add_btn">Add Task</button>
</form>
</body>
</html>
If you view your page now in the browser, it looks something like this:
Let's add the styling. Directly under the <title> tag, add this line to load the stylesheet file:
<link rel="stylesheet" type="text/css" href="style.css">
Open the style.css file we had created earlier and paste this styling code in it:
.heading{
width: 50%;
margin: 30px auto;
text-align: center;
color: #6B8E23;
background: #FFF8DC;
border: 2px solid #6B8E23;
border-radius: 20px;
}
form {
width: 50%;
margin: 30px auto;
border-radius: 5px;
padding: 10px;
background: #FFF8DC;
border: 1px solid #6B8E23;
}
form p {
color: red;
margin: 0px;
}
.task_input {
width: 75%;
height: 15px;
padding: 10px;
border: 2px solid #6B8E23;
}
.add_btn {
height: 39px;
background: #FFF8DC;
color: #6B8E23;
border: 2px solid #6B8E23;
border-radius: 5px;
padding: 5px 20px;
}
table {
width: 50%;
margin: 30px auto;
border-collapse: collapse;
}
tr {
border-bottom: 1px solid #cbcbcb;
}
th {
font-size: 19px;
color: #6B8E23;
}
th, td{
border: none;
height: 30px;
padding: 2px;
}
tr:hover {
background: #E9E9E9;
}
.task {
text-align: left;
}
.delete{
text-align: center;
}
.delete a{
color: white;
background: #a52a2a;
padding: 1px 6px;
border-radius: 3px;
text-decoration: none;
}
vli_ad
If we refresh our browser now, we get this:
Now we write the code to save submitted task to database.
At the very top of the index.php file, before the very first line, add this code:
<?php
// initialize errors variable
$errors = "";
// connect to database
$db = mysqli_connect("localhost", "root", "", "todo");
// insert a quote if submit button is clicked
if (isset($_POST['submit'])) {
if (empty($_POST['task'])) {
$errors = "You must fill in the task";
}else{
$task = $_POST['task'];
$sql = "INSERT INTO tasks (task) VALUES ('$task')";
mysqli_query($db, $sql);
header('location: index.php');
}
}
// ...
What this does is, if the user clicks the submit button, the task is saved in the database. However if no task was filled in the form, the value of the $errors variable is set to 'you must fill in the task'. But that doesn't get displayed. Let's display it.
Let's paste this code inside the form. Directly under the <form> tag. Like this:
<?php if (isset($errors)) { ?>
<p><?php echo $errors; ?></p>
<?php } ?>
If we try to submit the form with an empty value, we get this:
So far our app saves tasks in the database but doesn't display them.
What we need to do is retrieve them from the database and then display them.
Open the index.php file and paste this code immediately after the form's closing </form> tag:
//...
// </form>
<table>
<thead>
<tr>
<th>N</th>
<th>Tasks</th>
<th style="width: 60px;">Action</th>
</tr>
</thead>
<tbody>
<?php
// select all tasks if page is visited or refreshed
$tasks = mysqli_query($db, "SELECT * FROM tasks");
$i = 1; while ($row = mysqli_fetch_array($tasks)) { ?>
<tr>
<td> <?php echo $i; ?> </td>
<td class="task"> <?php echo $row['task']; ?> </td>
<td class="delete">
<a href="index.php?del_task=<?php echo $row['id'] ?>">x</a>
</td>
</tr>
<?php $i++; } ?>
</tbody>
</table>
If we enter a task in the form and hit the submit button, we get this:
Good!
ez_adLet's make our delete button work. At the top of the page, after the if block that saves task in the database, add this code:
// delete task
if (isset($_GET['del_task'])) {
$id = $_GET['del_task'];
mysqli_query($db, "DELETE FROM tasks WHERE id=".$id);
header('location: index.php');
}
?>
That's all. If we click the little 'x' button now against a task, that task is deleted in the database.
Conclusion
Hope this helps you. One feature I'd recommend you add on this app just to further exercise your skills is to add the edit feature where a post can be updated even after it has been created. Hint: Follow my tutorial on CRUD: Create, edit, update and delete posts with MySQL database
Thanks :D
Awa Melvine
