Task List Documention
Table 1 (username) - Foreign Key
| id | username |
|---|---|
| 1 | Peter |
| 2 | Petur |
| 3 | Petah |
Data Model
| username | |
|---|---|
| id | int[4] Auto-increment |
| username | varchar[255] |
Queries
❯ Verify Login/Signup
$sql = "SELECT * FROM username WHERE username = (?)";
❯ Login
$sql = "SELECT * FROM username WHERE username = (?)";
❯ Create Account
$sql = "INSERT INTO username (username) VALUES (?)";
Table 2 (tasks) - Primary Key
| id | userid | tasks | todo | completed |
|---|---|---|---|---|
| 1 | 2 | give Pat 100$ | 1 | |
| 2 | 3 | give Pat 200$ | 0 | |
| 3 | 1 | give Pat 1 Bitcoin | 1 |
Data Model
| tasks | |
|---|---|
| id | int[10] Auto-increment |
| userid | int[4] |
| tasks | varchar[255] |
| todo | datetime - VALUE (CURRENT_TIMESTAMP) - Null: No |
| completed | tinyint(1) |
Queries
❯ Insert Tasks
$sql = "INSERT INTO tasks (username, tasks) VALUES (?, ?)";
❯ Read Tasks
$sql = "SELECT * FROM tasks WHERE userid = ? AND completed = 0";
❯ Mark Completed
$sql = "UPDATE `tasks` SET `todo` = NOW(), `completed` = '1' WHERE `id` = ?";
❯ Undo Completed
$id = $_REQUEST['id'];
$sql= "UPDATE tasks SET completed = '0' WHERE id = '$id'";
❯ Recently Completed
$sql = "SELECT * FROM tasks WHERE userid= (?) and completed = 1 ORDER BY todo DESC LIMIT 10";
Table 3 (shared) - Foreign Keys
| id | userid | taskid |
|---|---|---|
| 1 | 2 | 3 |
| 2 | 4 | 3 |
| 3 | 5 | 8 |
| 4 | 7 | 5 |
Data Model
| Shared | |
|---|---|
| id | int[10] Auto-increment |
| userid | int[4] |
| taskid | int[10] |
Queries
❯ Select User
$sql= "SELECT * FROM username WHERE NOT id= ?";
❯ Insert Share
$sql = "INSERT INTO shared (userid, tasksid) VALUES (?, ?)";
❯ Read I Shared
$sql = "SELECT shared.id, username.username, tasks.task, tasks.userid FROM `shared` INNER JOIN username ON username.id = shared.userid INNER JOIN tasks ON tasks.id = shared.tasksid WHERE tasks.userid = ?"
❯ Read Shared to Me
$sql = "SELECT shared.id, username.username, tasks.task, tasks.userid FROM `shared` INNER JOIN username ON username.id = shared.userid INNER JOIN tasks ON tasks.id = shared.tasksid WHERE shared.userid = ?";