Preserving Data Integrity6:33 with Alena Holligan
Deleting information can spell disaster for our database. We can easily lose things we need to keep track of. First we'll keep items from being so easy to delete, by asking the user to verify that they actually want to delete the item. Second, we're going to prevent users from deleting information we don't want them to delete.
Deleting Projects with Tasks
Be cautious when deleting information from ANY data source. This video covers the concepts of asking the user to be sure they want to delete and also not allowing projects to be deleted if they have any tasks assigned. There are many other options we could choose for handling data integrating.
A few more things to consider:
- Don't show delete if project has tasks
- Hide project instead of delete
- Reassign tasks to another project
We saw in the last video how easy it can be to delete things, 0:00 maybe a little too easy to delete things by mistake. 0:04 For our task, this isn't a huge problem. 0:08 It's pretty easy to add another task. 0:10 You just need to remember what it is that you need to add. 0:13 Deleting projects, on the other hand, can get a bit more complicated. 0:16 Since my tasks rely on projects, if I delete projects that already have 0:20 tasks assigned, I would lose track of those tasks assigned to these projects. 0:24 Because the tasks no longer have a project assigned, 0:30 they will no longer be displayed anywhere, making those tasks essentially useless. 0:33 There are two parts to this solution. 0:39 First, we're going to ask the user to verify that they actually want to delete, 0:42 both on the tasks and on the project. 0:47 Second, we're only going to allow deletion of projects 0:50 that do not have any tasks assigned. 0:54 Let's jump into Workspaces and get started. 0:57 We'll start by updating the task list to ask for 1:01 confirmation before deleting a task. 1:04 To our form, we add onsubmit 1:07 equal return confirm, 1:12 Are you sure you want to delete this task? 1:16 Let's check out the confirmation in the browser. 1:28 When I choose Delete, 1:33 I now receive an alert box asking if I'm sure I want to delete this task. 1:35 If I cancel, the form never gets, submitted, so the task is not deleted. 1:41 If I choose OK, the form submission continues and the task is deleted. 1:47 Let's add the delete functionality to our projects as well. 1:52 Back in Workspaces, let's copy the $_POST conditional from the task list. 1:57 Then let's rename task to project. 2:09 Project_list. 2:21 And project. 2:30 We need to accept the $_GET mesage, so 2:33 let's copy that conditional from the task list as well. 2:35 Then we need to display the message, so let's copy the display conditional. 2:45 And finally, we can copy the code from the delete form and make a few changes. 2:58 We'll rename task to project. 3:18 Are you sure you want to delete this project? 3:24 Project_id. 3:30 Let's take a look at this in the browser. 3:32 The project list now shows a delete link, and when we choose Delete. 3:36 We receive a confirmation box. 3:43 If we choose OK, 3:47 we get an error because the delete project function is not set up yet. 3:49 We want to make sure that we only allow projects without tasks to be deleted. 3:55 So let's go back to Workspaces and add this new function. 4:00 Let's duplicate the delete_task function. 4:05 And we'll rename it delete_project. 4:11 We'll change this to $project_id. 4:15 And DELETE FROM projects. 4:27 We want to make sure that projects that have tasks do not get deleted. 4:31 So we'll add another clause to our WHERE. 4:35 Make sure that you're in the delete_project function and 4:42 not delete_tasks. 4:46 We'll use the keyword AND. 4:47 And then we want to make sure that our project_id is not in our task table. 4:51 We can use an inner select for this. 4:58 SELECT project_id FROM tasks. 5:01 This will select all project_ids from the task table and 5:09 keep the delete statement from deleting the projects that match a task. 5:13 Now we have to change our return to check if any rows were affected by the delete. 5:18 If $results->rowCount is greater than 0, 5:27 then we want to return true, 5:34 else, we return false. 5:38 Let's give it a try back in the browser. 5:46 Let's try deleting a project we know has tasks. 5:49 Choose OK, and we get the error Unable to Delete Project. 5:56 Let's now try deleting the extra project that doesn't have any tasks. 6:02 The project was deleted, but it looks like my message was a little messed up. 6:11 Let's check that out. 6:15 Project deleted. 6:29 Great work. 6:32
You need to sign up for Treehouse in order to download course files.Sign up