1 00:00:02,260 --> 00:00:05,670 Now that we get this list off authors here, 2 00:00:05,670 --> 00:00:09,710 we can now work on actually making this form submittable 3 00:00:09,710 --> 00:00:13,770 and for this back in the create post EJS file. 4 00:00:13,770 --> 00:00:17,010 If we scroll up a little bit here, 5 00:00:17,010 --> 00:00:19,460 we've got our form element and there, 6 00:00:19,460 --> 00:00:22,800 the action and the method is missing. 7 00:00:22,800 --> 00:00:24,930 Now, the method should be post or get. 8 00:00:24,930 --> 00:00:26,610 And typically it's post, 9 00:00:26,610 --> 00:00:31,100 especially if we are dealing with user created data 10 00:00:31,100 --> 00:00:35,090 that will be stored in a file or in the database, 11 00:00:35,090 --> 00:00:36,690 which should be the case here. 12 00:00:36,690 --> 00:00:39,880 We want to store to post data in a database. 13 00:00:39,880 --> 00:00:42,710 Hence, a post request is what we want to use. 14 00:00:42,710 --> 00:00:47,410 A get request could be used if it's more about loading 15 00:00:47,410 --> 00:00:52,410 some page with some query parameters, for example. 16 00:00:52,820 --> 00:00:55,090 But here we want to submit some data and store it 17 00:00:55,090 --> 00:00:58,810 in a database, so a post request is great. 18 00:00:58,810 --> 00:01:02,360 Action then should be the path off the page 19 00:01:02,360 --> 00:01:06,200 or of the route that should handle the form submission. 20 00:01:06,200 --> 00:01:09,200 And it's up to you, which path you want to use here, 21 00:01:09,200 --> 00:01:14,200 I'll go for posts so that a post request to slash posts 22 00:01:15,490 --> 00:01:19,240 will actually store a new blog post. 23 00:01:19,240 --> 00:01:22,200 And by the way, don't be confused by all those posts. 24 00:01:22,200 --> 00:01:24,990 Of course, the post HTTP request 25 00:01:24,990 --> 00:01:27,663 has nothing to do with our blog posts. 26 00:01:29,010 --> 00:01:31,180 So now we need to handle 27 00:01:31,180 --> 00:01:36,180 posts requests to slash posts and hands here in blog JS, 28 00:01:36,440 --> 00:01:41,170 we can register a new route with router and now post, 29 00:01:41,170 --> 00:01:44,930 because now we want to listen to post requests. 30 00:01:44,930 --> 00:01:46,430 So it's not router get, 31 00:01:46,430 --> 00:01:50,430 but router post and the path is slash posts 32 00:01:50,430 --> 00:01:52,770 because that's the path we just defined 33 00:01:52,770 --> 00:01:55,470 as an action on our form. 34 00:01:55,470 --> 00:01:58,340 Then we got our functioning here with request 35 00:01:58,340 --> 00:02:00,040 and response objects 36 00:02:00,040 --> 00:02:02,220 and in this function, 37 00:02:02,220 --> 00:02:07,220 we now want to gather the data that we need for creating a 38 00:02:07,670 --> 00:02:11,100 new post and then run this insert command, 39 00:02:11,100 --> 00:02:14,203 send disinterred query to our database. 40 00:02:15,640 --> 00:02:20,640 And for this, I'll start by writing DB query again, 41 00:02:20,930 --> 00:02:21,900 and the query, 42 00:02:21,900 --> 00:02:24,700 which I will now write will be a little bit longer 43 00:02:24,700 --> 00:02:26,660 than the query I had before. 44 00:02:26,660 --> 00:02:28,850 Though it still will be quite manageable 45 00:02:28,850 --> 00:02:30,980 and not too long. 46 00:02:30,980 --> 00:02:33,910 To be precise, the query here will be, 47 00:02:33,910 --> 00:02:37,570 insert into, then our table name, 48 00:02:37,570 --> 00:02:41,890 which is posts, that's the table we created before. 49 00:02:41,890 --> 00:02:45,850 And then as you learned in the previous course section, 50 00:02:45,850 --> 00:02:50,500 we simply list all the columns which we want to populate. 51 00:02:50,500 --> 00:02:54,930 And in our case here, that will be the title column, 52 00:02:54,930 --> 00:02:58,820 the summary column, the body column, 53 00:02:58,820 --> 00:03:01,000 and the author ID column. 54 00:03:01,000 --> 00:03:03,963 These are the four columns I want to populate. 55 00:03:05,600 --> 00:03:09,030 We don't need to populate the ID or date columns, 56 00:03:09,030 --> 00:03:11,723 because those will be set automatically. 57 00:03:13,630 --> 00:03:18,610 Now as a next step, we add the values key word 58 00:03:18,610 --> 00:03:21,740 and then after in brackets and parentheses, 59 00:03:21,740 --> 00:03:25,610 the different values for the four columns. 60 00:03:25,610 --> 00:03:29,140 But of course these values are now received 61 00:03:29,140 --> 00:03:31,800 as part of the incoming request. 62 00:03:31,800 --> 00:03:35,410 Since it's a post request, it will carry a post body. 63 00:03:35,410 --> 00:03:38,020 So it will have some data attached. 64 00:03:38,020 --> 00:03:41,700 The form submission in the front-end will automatically 65 00:03:41,700 --> 00:03:44,930 attach data to that post request. 66 00:03:44,930 --> 00:03:49,160 And because we have that middleware in app JS, 67 00:03:49,160 --> 00:03:51,450 this one here in line 13, 68 00:03:51,450 --> 00:03:55,000 we will also parse that incoming request body 69 00:03:55,000 --> 00:03:57,490 and express JS will make it available 70 00:03:57,490 --> 00:04:01,823 on this body field on the incoming request object. 71 00:04:03,640 --> 00:04:07,583 So here request.body will hold the incoming data. 72 00:04:08,490 --> 00:04:11,390 Now that will be an object, request body, 73 00:04:11,390 --> 00:04:16,140 will be an object where we got different key value pairs 74 00:04:16,140 --> 00:04:19,500 and the keys are determined by the names we assigned 75 00:04:19,500 --> 00:04:24,500 to the inputs in this form here in the create post template. 76 00:04:24,650 --> 00:04:27,040 And you find the name attributes here. 77 00:04:27,040 --> 00:04:29,550 The title field has a title name. 78 00:04:29,550 --> 00:04:32,210 The summary field has a summary name, 79 00:04:32,210 --> 00:04:36,380 the text area where we entered the main content of the post 80 00:04:36,380 --> 00:04:38,810 has a content field name. 81 00:04:38,810 --> 00:04:43,810 And then the author select dropdown has an author field. 82 00:04:44,140 --> 00:04:47,820 Please note that what will be submitted here as a value 83 00:04:47,820 --> 00:04:50,320 will be the ID of an author, 84 00:04:50,320 --> 00:04:53,513 which is great because that's what we need on the backend. 85 00:04:54,850 --> 00:04:59,330 So therefore back in blog JS, we know that request body 86 00:04:59,330 --> 00:05:03,300 will hold an object with title, summary, content, 87 00:05:03,300 --> 00:05:06,660 and the author ID, which is exactly what we need here. 88 00:05:06,660 --> 00:05:10,680 But here we of course need to list our individual values 89 00:05:12,100 --> 00:05:14,120 or do we? 90 00:05:14,120 --> 00:05:16,570 Since we used as MySQL package, 91 00:05:16,570 --> 00:05:20,470 we can use some convenience features it offers to us. 92 00:05:20,470 --> 00:05:23,510 And for example the MySQL package helps us 93 00:05:23,510 --> 00:05:28,510 with injecting dynamic values into SQL queries like this, 94 00:05:28,980 --> 00:05:31,280 instead of manually concatenating 95 00:05:31,280 --> 00:05:35,360 a long string with some fixed and some dynamic values. 96 00:05:35,360 --> 00:05:39,163 We can simply add a question mark here in this query string. 97 00:05:40,970 --> 00:05:45,970 And then the query method takes a second parameter. 98 00:05:46,870 --> 00:05:49,730 So after the query string separated by a comma, 99 00:05:49,730 --> 00:05:51,960 we have the second parameter, 100 00:05:51,960 --> 00:05:54,650 which actually has to be an array. 101 00:05:54,650 --> 00:05:58,910 So we create a new array here with square brackets. 102 00:05:58,910 --> 00:06:00,450 And then here, 103 00:06:00,450 --> 00:06:03,710 MySQL package will automatically take all the values 104 00:06:03,710 --> 00:06:05,320 we add to this array 105 00:06:05,320 --> 00:06:07,710 and replace all the question marks 106 00:06:07,710 --> 00:06:11,563 in our query year with those values. 107 00:06:12,770 --> 00:06:15,470 Now here, we only have one question mark. 108 00:06:15,470 --> 00:06:19,550 We could always add four question marks for the four values 109 00:06:19,550 --> 00:06:20,760 that are expected, 110 00:06:20,760 --> 00:06:24,360 but the MySQL package has another convenience. 111 00:06:24,360 --> 00:06:29,330 If we add an array as a value in that array, 112 00:06:29,330 --> 00:06:32,000 then this question mark, 113 00:06:32,000 --> 00:06:35,420 which will be replaced by that value will automatically 114 00:06:35,420 --> 00:06:39,380 be replaced with the values we provide in the array, 115 00:06:39,380 --> 00:06:41,510 which we pass here. 116 00:06:41,510 --> 00:06:45,060 Sounds very cryptic, let me show you what I mean. 117 00:06:45,060 --> 00:06:47,690 Let's say we construct a new data array here, 118 00:06:47,690 --> 00:06:50,360 which should hold the four pieces of data 119 00:06:50,360 --> 00:06:52,190 that are expected here. 120 00:06:52,190 --> 00:06:53,863 So title and so on. 121 00:06:55,150 --> 00:06:57,290 To construct disarray, we can simply access 122 00:06:57,290 --> 00:07:01,530 requestbody.title, request body.summary, 123 00:07:01,530 --> 00:07:06,163 request body.content and request body.author. 124 00:07:07,670 --> 00:07:10,330 Now, of course, please note that the keys 125 00:07:10,330 --> 00:07:13,140 I'm listing here have to be the keys 126 00:07:13,140 --> 00:07:18,140 that are defined as names in our HTML template here. 127 00:07:18,470 --> 00:07:22,360 So these are the names here and that's then are the keys 128 00:07:22,360 --> 00:07:24,283 we can use in our back end code. 129 00:07:25,560 --> 00:07:28,840 The values for those keys then will be the values 130 00:07:28,840 --> 00:07:31,150 entered into these input fields. 131 00:07:31,150 --> 00:07:34,130 So data in the end will be an array full of the values 132 00:07:34,130 --> 00:07:35,603 provided in the form. 133 00:07:36,670 --> 00:07:39,860 Now the order here matters because we can now use this data 134 00:07:39,860 --> 00:07:44,190 array and pass that as a single value in that array, 135 00:07:44,190 --> 00:07:47,900 which we pass as a second parameter to query. 136 00:07:47,900 --> 00:07:50,910 So we have an array inside of an array now. 137 00:07:50,910 --> 00:07:52,630 Now the MySQL package 138 00:07:52,630 --> 00:07:55,870 will then see that this query string here 139 00:07:55,870 --> 00:07:58,920 has one placeholder, one question mark, 140 00:07:58,920 --> 00:08:01,330 and it will then use the first value, 141 00:08:01,330 --> 00:08:02,750 which it finds in this array, 142 00:08:02,750 --> 00:08:05,680 which has passed as a second parameter to query 143 00:08:05,680 --> 00:08:08,680 as a replacement, as a concrete value 144 00:08:08,680 --> 00:08:10,820 for this question mark. 145 00:08:10,820 --> 00:08:12,990 So this question mark will be swapped 146 00:08:12,990 --> 00:08:15,871 for the value stored in data. 147 00:08:15,871 --> 00:08:19,250 Now the values stored in data is a number array, 148 00:08:19,250 --> 00:08:21,740 but that's okay, my SQL, the package, 149 00:08:21,740 --> 00:08:23,270 can deal with that 150 00:08:23,270 --> 00:08:26,760 and it will then automatically split this array 151 00:08:26,760 --> 00:08:29,210 into its individual values 152 00:08:29,210 --> 00:08:32,679 and basically converted to four values, 153 00:08:32,679 --> 00:08:35,653 which are provided as part of this query command. 154 00:08:36,890 --> 00:08:40,679 So behind the scenes, this will simply say values, 155 00:08:40,679 --> 00:08:42,169 and between those brackets, 156 00:08:42,169 --> 00:08:45,280 we'll have a comma separated list of title, summary, 157 00:08:45,280 --> 00:08:47,310 content and author. 158 00:08:47,310 --> 00:08:50,260 That's what the My SQL package will do for us 159 00:08:50,260 --> 00:08:52,480 with help of this placeholder feature 160 00:08:52,480 --> 00:08:56,043 and as passing an array inside of that array. 161 00:08:57,620 --> 00:09:02,370 Alternatively, we could have added multiple placeholders 162 00:09:02,370 --> 00:09:06,480 and then added our individual values like this, 163 00:09:06,480 --> 00:09:09,453 whoops, like this data one, 164 00:09:10,990 --> 00:09:14,890 data two and data three, 165 00:09:14,890 --> 00:09:16,730 that if I restructure it 166 00:09:16,730 --> 00:09:19,080 would have been the alternative. 167 00:09:19,080 --> 00:09:21,510 We defined four placeholders, 168 00:09:21,510 --> 00:09:23,680 and then in order in this array, 169 00:09:23,680 --> 00:09:25,410 which is our second parameter, 170 00:09:25,410 --> 00:09:28,343 we define the values for those placeholders. 171 00:09:29,350 --> 00:09:30,610 That would work as well, 172 00:09:30,610 --> 00:09:33,750 but it's a little bit cumbersome and unnecessarily long 173 00:09:33,750 --> 00:09:35,990 and therefore I'll just provide one value, 174 00:09:35,990 --> 00:09:38,160 which is an array and one place holder, 175 00:09:38,160 --> 00:09:39,370 which takes this array 176 00:09:39,370 --> 00:09:42,320 and the my SQL package will automatically expand 177 00:09:42,320 --> 00:09:46,233 this array here into separate values, which are provided. 178 00:09:47,190 --> 00:09:49,370 And I'm spending a lot of time on this because 179 00:09:49,370 --> 00:09:51,900 it is important to understand how this 180 00:09:51,900 --> 00:09:54,720 place holder replacement works. 181 00:09:54,720 --> 00:09:57,630 And the whole idea behind this is that you don't have to 182 00:09:57,630 --> 00:10:01,210 type unnecessarily long SQL statements, 183 00:10:01,210 --> 00:10:03,210 but that instead you can focus on the essence 184 00:10:03,210 --> 00:10:07,033 of the SQL statement and let the MySQL package do the rest. 185 00:10:08,220 --> 00:10:10,010 But with that we'll now run a query 186 00:10:10,010 --> 00:10:13,090 that inserts posts into the database. 187 00:10:13,090 --> 00:10:16,640 And therefore this should now work. 188 00:10:16,640 --> 00:10:18,210 Now of course, just as before, 189 00:10:18,210 --> 00:10:20,280 it's an asynchronous operation, 190 00:10:20,280 --> 00:10:22,320 which can take a couple of milliseconds 191 00:10:22,320 --> 00:10:24,280 and therefore we want to wait for it 192 00:10:24,280 --> 00:10:27,780 to complete before we do anything else. 193 00:10:27,780 --> 00:10:28,990 Therefore again, 194 00:10:28,990 --> 00:10:31,980 I'll use a sync here in front of dysfunction 195 00:10:31,980 --> 00:10:34,830 and a wait here in front of the query. 196 00:10:34,830 --> 00:10:37,820 And whilst I'm not interested in the result here, 197 00:10:37,820 --> 00:10:40,720 I still want to wait for this operation to finish 198 00:10:40,720 --> 00:10:44,340 before then in the next line, we continue. 199 00:10:44,340 --> 00:10:46,980 And here, since we're inside of a post route, 200 00:10:46,980 --> 00:10:50,080 I don't want to directly return a template, 201 00:10:50,080 --> 00:10:53,370 but instead I want to redirect to another page 202 00:10:53,370 --> 00:10:56,350 and we can achieve this by using rests, redirect, 203 00:10:56,350 --> 00:10:59,653 and then redirecting to slash posts, for example. 204 00:11:02,010 --> 00:11:05,030 Now we redirect back to the all posts page 205 00:11:05,030 --> 00:11:10,030 whenever a new post was inserted, and that should be all. 206 00:11:10,160 --> 00:11:12,560 It was quite a lot of talking about the process, 207 00:11:12,560 --> 00:11:17,230 but I hope it's now clear how this insert command works. 208 00:11:17,230 --> 00:11:18,900 From a SQL perspective, 209 00:11:18,900 --> 00:11:21,470 it's the same insert statement we saw before. 210 00:11:21,470 --> 00:11:25,610 The only special thing is that we add the values dynamically 211 00:11:25,610 --> 00:11:27,820 with less typing from our side, 212 00:11:27,820 --> 00:11:30,790 since note JS will execute this code with help 213 00:11:30,790 --> 00:11:32,523 of the MySQL package. 214 00:11:33,870 --> 00:11:37,010 And therefore, if you now save all those files, 215 00:11:37,010 --> 00:11:38,930 all the files who worked on, 216 00:11:38,930 --> 00:11:43,260 if you go back and you reload this new post page here, 217 00:11:43,260 --> 00:11:46,410 we can test this and add a first blog post, 218 00:11:46,410 --> 00:11:48,223 or maybe name it, hello world. 219 00:11:49,740 --> 00:11:54,517 This is a first note, MySQL driven blog post. 220 00:11:56,400 --> 00:11:57,840 And then here in the post content, 221 00:11:57,840 --> 00:12:02,320 we can also enter something like, hello world. 222 00:12:02,320 --> 00:12:06,960 This is amazing, it's the first blog post 223 00:12:06,960 --> 00:12:11,183 that will be saved in a database like that. 224 00:12:12,940 --> 00:12:16,630 Then we can pick an author and I'll go with myself here 225 00:12:17,510 --> 00:12:20,050 and click add post. 226 00:12:20,050 --> 00:12:23,040 And now I'm back on the all posts page. 227 00:12:23,040 --> 00:12:26,830 Now we don't see the post here yet because we haven't added 228 00:12:26,830 --> 00:12:29,360 any logic for fetching our posts, 229 00:12:29,360 --> 00:12:31,233 but it should have succeeded. 230 00:12:32,070 --> 00:12:34,850 There are no errors here in the terminal for me. 231 00:12:34,850 --> 00:12:36,870 If you do have some again, 232 00:12:36,870 --> 00:12:40,830 carefully check your code and compare your code to mine. 233 00:12:40,830 --> 00:12:44,200 And to validate that the post really was inserted, 234 00:12:44,200 --> 00:12:49,200 we can go back to MySQL work bench and then go here 235 00:12:49,410 --> 00:12:52,650 to the posts table and click this icon 236 00:12:52,650 --> 00:12:54,700 to run a select from query. 237 00:12:54,700 --> 00:12:58,460 And in there we should see this hello world blog post. 238 00:12:58,460 --> 00:13:00,770 With the date inserted automatically, 239 00:13:00,770 --> 00:13:05,150 the correct author ID inserted and all the texts we added. 240 00:13:05,150 --> 00:13:06,403 So that worked. 241 00:13:07,800 --> 00:13:10,150 And therefore now, as a next step, 242 00:13:10,150 --> 00:13:12,610 let's make sure that we can also display 243 00:13:12,610 --> 00:13:17,610 a list of blog posts here on this slash posts page. 244 00:13:17,620 --> 00:13:21,730 And I guess this is a great challenge and exercise for you. 245 00:13:21,730 --> 00:13:24,440 So before moving on to the next lecture 246 00:13:24,440 --> 00:13:27,630 where we will add dysfunctionality together, 247 00:13:27,630 --> 00:13:29,810 definitely try it on your own 248 00:13:29,810 --> 00:13:32,430 and try fetching all the posts, 249 00:13:32,430 --> 00:13:36,260 try passing them to the template and then try 250 00:13:36,260 --> 00:13:38,730 outputting a list of blog posts there. 251 00:13:38,730 --> 00:13:41,910 You can use my post item included for that, 252 00:13:41,910 --> 00:13:43,730 but if you get stuck with that, 253 00:13:43,730 --> 00:13:46,830 simply output any list of blog posts data, 254 00:13:46,830 --> 00:13:49,970 maybe just a list of all the blog posts titles. 255 00:13:49,970 --> 00:13:52,600 Just try to implement this fetching 256 00:13:52,600 --> 00:13:55,730 and bring something from that list of blog posts 257 00:13:55,730 --> 00:13:57,670 onto the screen. 258 00:13:57,670 --> 00:13:59,770 In the next lecture, we'll do it together.