1 00:00:00,150 --> 00:00:01,290 Well, hello, my dear friends. 2 00:00:01,590 --> 00:00:08,580 So in this lesson, we're going to write a query for selecting the history for the leave info for each 3 00:00:08,580 --> 00:00:09,330 employee. 4 00:00:10,080 --> 00:00:11,130 So let's get started with that. 5 00:00:13,300 --> 00:00:17,530 So let's go to execute query in our DBI browser. 6 00:00:18,130 --> 00:00:27,640 Select employee dot I.D. because we want to select the personal I.D., leave date, start and end info, 7 00:00:27,650 --> 00:00:32,620 and then we want to also have the leave period as well as the reason. 8 00:00:33,550 --> 00:00:35,080 So let's do all that. 9 00:00:35,350 --> 00:00:38,170 Select employee I.D. as I.D.. 10 00:00:40,740 --> 00:00:43,740 Employee leave that date as date. 11 00:00:48,310 --> 00:00:51,460 Employee leave dot start as start. 12 00:00:58,270 --> 00:01:01,210 Employee leave, not end as end. 13 00:01:05,140 --> 00:01:07,810 Employee leave that period as period. 14 00:01:10,790 --> 00:01:14,240 Employee leave that excuse as excuse. 15 00:01:17,390 --> 00:01:24,230 And all of these should be selected from the employee and the employee leave pitch. 16 00:01:25,250 --> 00:01:32,990 On the condition that the employee ID is equal to employee, we've got employee I.D.. 17 00:01:33,380 --> 00:01:34,460 All right, so let's run this. 18 00:01:35,890 --> 00:01:42,820 And here I've added manually the sleeve info, OK, so when we've run this query, we're going to say 19 00:01:42,820 --> 00:01:47,980 that we're employee ID is equal to all, and we didn't set it. 20 00:01:49,300 --> 00:01:51,310 But we can try it. 21 00:01:51,520 --> 00:01:55,960 So employee is equal to one thousand one. 22 00:02:00,030 --> 00:02:01,200 OK, so there's a resume. 23 00:02:01,650 --> 00:02:08,190 So we only get one row, and this actually belongs to our employee number one thousand one. 24 00:02:09,300 --> 00:02:16,680 So what we'll do is go ahead and open up our database file and then we can create a method. 25 00:02:22,310 --> 00:02:25,400 Def get employee leave info, self ID. 26 00:02:29,590 --> 00:02:34,260 Because that would be the I.D. for which we want to have the leveland for. 27 00:02:34,780 --> 00:02:37,930 And we're going to create a query equal. 28 00:02:38,320 --> 00:02:40,840 Q School query. 29 00:02:47,310 --> 00:02:49,320 Query string equals. 30 00:02:55,100 --> 00:02:56,580 Let's copy our school here. 31 00:03:13,530 --> 00:03:18,870 Now I'm going to show you is what you can do in order to create a prepared statement. 32 00:03:20,720 --> 00:03:26,900 So you know that when we got the full employee information, all we really did was that query string 33 00:03:26,900 --> 00:03:32,030 and then we executed a query that executed for that specific query string. 34 00:03:33,180 --> 00:03:41,610 But now what we want is to pass an I.D. to our query and in order to avoid an SQL injection. 35 00:03:42,420 --> 00:03:48,710 And if you don't know what that is, just search around and you'll get a good idea about it pretty fast. 36 00:03:48,720 --> 00:03:55,890 Probably if you if you Google that one anyway, is basically inserting a SQL query into the existing 37 00:03:55,890 --> 00:03:57,470 query in order to change it. 38 00:03:57,840 --> 00:03:58,140 All right. 39 00:03:58,680 --> 00:04:04,890 So now in order to create a prepared statement, we'll need to pass something like this ID equal. 40 00:04:05,840 --> 00:04:06,930 Colon Idi. 41 00:04:07,850 --> 00:04:08,210 OK. 42 00:04:09,470 --> 00:04:15,080 But you're also going to want to make sure that the employee ID is equal to employee leave dot ID. 43 00:04:17,380 --> 00:04:20,740 And employee ID is equal to Idie. 44 00:04:22,420 --> 00:04:30,250 So now we can prepare the statement, so we'll just type query dot, prepare the query string. 45 00:04:33,340 --> 00:04:43,180 And then we can come in here, say, yes, execute the query string by typing, so query execute. 46 00:04:45,390 --> 00:04:48,720 And you can see that we've created a string, a query string. 47 00:04:48,780 --> 00:04:49,680 We prepared it. 48 00:04:50,800 --> 00:04:52,390 And we will need to bind it. 49 00:04:53,650 --> 00:04:59,890 And then you have to execute it in order to bind the idea, which is this variable. 50 00:05:00,340 --> 00:05:03,730 So you've got to type query, not bind value. 51 00:05:05,620 --> 00:05:10,180 And then pass the string, which you inserted there. 52 00:05:10,660 --> 00:05:12,060 So it would be Idie. 53 00:05:13,190 --> 00:05:20,360 And to the actual value that should be replaced for this I.D., and that will be our parameter and we 54 00:05:20,360 --> 00:05:20,930 are done. 55 00:05:23,450 --> 00:05:28,250 So now I think we can copy and paste all of this code because it's the exact same thing. 56 00:05:37,970 --> 00:05:45,230 So the record is clear that the record, the column no, we feel the header list and the result, this 57 00:05:45,230 --> 00:05:46,610 should be exactly the same. 58 00:05:47,300 --> 00:05:52,400 So we'll save all this and test it out before implementing it into our table widget. 59 00:05:53,240 --> 00:05:56,210 So let's go to Maine, create a database instance. 60 00:05:57,110 --> 00:05:59,840 Database is equal to database and. 61 00:06:03,700 --> 00:06:08,260 Print database, get employee leave info 1001 spAe zero. 62 00:06:09,280 --> 00:06:17,260 Print database, get employee leave info, one thousand one space, one print database, get employee 63 00:06:17,260 --> 00:06:20,200 leave info 1000 space zero. 64 00:06:21,650 --> 00:06:26,030 Print database, get employee leave info 1000 to space one. 65 00:06:37,340 --> 00:06:38,000 So let's run it. 66 00:06:38,750 --> 00:06:39,980 OK, so there's your resume. 67 00:06:43,250 --> 00:06:48,540 So that is it, my friends, we've written a query for selecting the history of this. 68 00:06:48,560 --> 00:06:50,870 Leave info for our employees. 69 00:06:51,650 --> 00:06:53,540 I do want to see you in the next lesson. 70 00:06:53,870 --> 00:06:55,340 So we'll continue on then. 71 00:06:55,520 --> 00:06:55,850 All right. 72 00:06:55,880 --> 00:06:56,990 Until then, have a great day.