1 00:00:00,150 --> 00:00:00,720 Okay. 2 00:00:00,750 --> 00:00:02,550 Welcome to this big step. 3 00:00:02,790 --> 00:00:07,290 What we're doing next is integrating my SQL into a web app. 4 00:00:07,290 --> 00:00:12,990 So we saw again how to integrate my SQL with Node in a single file that has nothing to do with the Internet 5 00:00:12,990 --> 00:00:18,220 or web applications or servers or routes where we just inserted 500 users at once. 6 00:00:18,240 --> 00:00:23,610 Now, what we're going to do is take our web app that we've created, super simple unstyled. 7 00:00:23,610 --> 00:00:28,140 But something like this, when we go to the home page here right now, it says, you've reached the 8 00:00:28,140 --> 00:00:28,800 home page. 9 00:00:28,800 --> 00:00:31,350 What we're going to try and do is take it to this. 10 00:00:31,650 --> 00:00:36,780 We have 519 users and that 519 is not hardcoded in. 11 00:00:36,780 --> 00:00:42,360 That's coming from our MySQL database with the 500 something that we inserted. 12 00:00:42,360 --> 00:00:47,670 So this will change depending on how many users are in our database and we'll see that that works. 13 00:00:48,060 --> 00:00:53,790 So to do that, we're basically I have another diagram that took ages to put together. 14 00:00:54,210 --> 00:00:59,400 I apologize if it's the colors aren't perfect or there's not nice typography. 15 00:00:59,400 --> 00:01:04,980 I wish I was better at that stuff, but here's our trusty red rectangle that represents a client computer. 16 00:01:05,340 --> 00:01:08,640 Here's our express app, this mint green. 17 00:01:09,910 --> 00:01:13,450 This time we're requesting the home page slash the root root. 18 00:01:13,480 --> 00:01:17,980 We ask the server for it by going to this URL hitting enter. 19 00:01:18,940 --> 00:01:20,650 The server says, Oh, hang on. 20 00:01:20,890 --> 00:01:24,700 It says here that I need to ask my SQL for a user account. 21 00:01:26,650 --> 00:01:29,110 So my SQL is now involved. 22 00:01:29,140 --> 00:01:33,370 This arrow represents the connection between Express and my SQL. 23 00:01:33,400 --> 00:01:37,630 We've already seen how to do that, how to connect my SQL to a JavaScript file. 24 00:01:37,870 --> 00:01:42,460 We ask basically with a select statement, select count from users. 25 00:01:43,250 --> 00:01:44,920 We get a response from my SQL. 26 00:01:44,930 --> 00:01:46,610 Hopefully there's not an error. 27 00:01:46,610 --> 00:01:53,870 We get a response 519 Then our Express app can take that and say, okay, we're all set now. 28 00:01:53,870 --> 00:01:55,340 Here is your final result. 29 00:01:55,340 --> 00:01:56,510 Here's the page we get. 30 00:01:56,510 --> 00:01:59,000 We have 519 users. 31 00:01:59,660 --> 00:02:00,350 All right. 32 00:02:00,350 --> 00:02:02,930 So there's a couple of steps involved that we need to do. 33 00:02:03,170 --> 00:02:06,710 Basically, it boils down to we've already seen how to make a simple route. 34 00:02:06,710 --> 00:02:10,580 And in the last section we saw how to connect my SQL to a file. 35 00:02:10,580 --> 00:02:12,950 Now we just need to combine them together. 36 00:02:13,190 --> 00:02:20,180 So the first thing that we'll do is go over to Cloud nine and we need to connect our app, this file 37 00:02:20,210 --> 00:02:22,490 to our MySQL database. 38 00:02:22,730 --> 00:02:29,230 And what we can do is just go to our previous app JS and just copy this code for connection equals my 39 00:02:29,270 --> 00:02:30,920 SQL Create Connection. 40 00:02:32,290 --> 00:02:34,930 So I won't make you watch me type this whole thing again. 41 00:02:35,140 --> 00:02:40,540 But remember, this is using the MySQL package and we already installed that earlier. 42 00:02:40,540 --> 00:02:43,960 We ran NPM install dash dash, save my SQL. 43 00:02:43,990 --> 00:02:48,970 If you didn't do that yet, go ahead and do it so that you see it here and that inside of our Join US 44 00:02:48,970 --> 00:02:52,480 folder instead of node modules, we have my SQL. 45 00:02:52,510 --> 00:03:00,850 We need that, but we're still missing something which is we need to require it for my SQL equals require 46 00:03:01,330 --> 00:03:02,140 my SQL. 47 00:03:02,140 --> 00:03:03,280 So this is all review. 48 00:03:03,310 --> 00:03:08,200 We did it earlier when we made our seed data that inserted 500 users. 49 00:03:08,680 --> 00:03:12,580 So we've got this and let's just make sure that that works without problem. 50 00:03:12,580 --> 00:03:13,750 So we'll just start Node. 51 00:03:14,110 --> 00:03:16,120 JS Everything looks okay. 52 00:03:16,750 --> 00:03:21,340 All right, so now we can focus on interacting with the database now that we've connected to it. 53 00:03:21,340 --> 00:03:27,490 But just to reiterate, this database name needs to be the name of the database inside of your MySQL 54 00:03:27,490 --> 00:03:30,400 server that you created with the user's table. 55 00:03:30,760 --> 00:03:33,010 So it's the same as what we were doing earlier. 56 00:03:33,010 --> 00:03:36,520 If you've been following along, if you named it something else, this has to match. 57 00:03:37,120 --> 00:03:46,390 So what we're going to do is instead of you've reached the home page, we want to find count of users 58 00:03:46,390 --> 00:03:50,830 in database and then we want to respond. 59 00:03:52,640 --> 00:03:54,830 With that count. 60 00:03:56,060 --> 00:03:58,250 So we've got to resend. 61 00:03:59,240 --> 00:04:06,200 What we're hoping to do is have something like we have plus count. 62 00:04:08,240 --> 00:04:13,360 Plus users in our database or something stupid like that. 63 00:04:13,370 --> 00:04:14,780 That's what we're hoping to do. 64 00:04:14,810 --> 00:04:19,640 Of course, right now we don't have this account variable, so that's what we need to aim to get to. 65 00:04:19,820 --> 00:04:22,250 So we'll start by commenting that result send out. 66 00:04:22,400 --> 00:04:31,700 So to find the count of our users in our database, we need to do a connection query, which we've done 67 00:04:31,700 --> 00:04:32,220 before. 68 00:04:32,240 --> 00:04:34,970 Remember, it's all the stuff we were doing over here. 69 00:04:36,190 --> 00:04:37,030 Connection. 70 00:04:37,270 --> 00:04:39,460 Query connection. 71 00:04:39,610 --> 00:04:40,390 Query. 72 00:04:40,690 --> 00:04:41,140 Connection. 73 00:04:41,320 --> 00:04:41,890 Query. 74 00:04:41,890 --> 00:04:43,360 And what is the query? 75 00:04:43,360 --> 00:04:44,260 We want to write. 76 00:04:44,290 --> 00:04:47,230 Let's do it in a separate variable so far. 77 00:04:47,260 --> 00:04:48,670 I'll just do Q again. 78 00:04:48,760 --> 00:04:52,840 Equals a string select count. 79 00:04:54,130 --> 00:04:59,110 Star from users just like that. 80 00:04:59,920 --> 00:05:07,070 If we were to just copy it, go over to our MySQL, we get 500 OC. 81 00:05:07,090 --> 00:05:08,650 So that's what we're hoping to get. 82 00:05:09,070 --> 00:05:14,160 But of course through JavaScript, so we've got our query written as a string. 83 00:05:14,170 --> 00:05:16,990 Now we just pass it in connection query. 84 00:05:17,140 --> 00:05:24,250 Then we add in that fun callback or we have error and let's call it results. 85 00:05:26,720 --> 00:05:27,410 Okay. 86 00:05:27,860 --> 00:05:32,360 So whatever code is in here will only run when two things happen. 87 00:05:32,360 --> 00:05:35,240 First, when somebody requests slash. 88 00:05:35,600 --> 00:05:41,540 Then we have to wait for this connection to run the query, which is select account from users. 89 00:05:41,540 --> 00:05:47,630 So then when that is done, whether it worked or not, this code runs and that will either be an error 90 00:05:47,630 --> 00:05:54,530 so we can add our if error throw error, which is not actually a great thing to do in a web application. 91 00:05:54,530 --> 00:05:58,370 What would probably be better is to say if there's an error, what do we want to do? 92 00:05:59,060 --> 00:06:04,580 Redirect the user to a different page or send them a different response, which we can see how to do 93 00:06:04,580 --> 00:06:05,270 in a little bit. 94 00:06:05,270 --> 00:06:07,490 But we're just going to leave it as that to keep it short. 95 00:06:07,760 --> 00:06:17,750 And then otherwise let's do a console.log results and just see oh, just see what we get. 96 00:06:19,250 --> 00:06:22,640 So again, to go over this, we connected to our database. 97 00:06:23,030 --> 00:06:29,180 Then only when somebody requests slash are we trying to select count from users. 98 00:06:29,180 --> 00:06:32,360 We could do it inside of joke if we wanted to or random num. 99 00:06:32,360 --> 00:06:38,960 But the way that our app is going to work the home route, when we go to the home page, we see welcome 100 00:06:39,080 --> 00:06:44,960 or join us, join the 519 users or whatever it is, and then there's a form at the bottom. 101 00:06:44,960 --> 00:06:46,940 So we're just going to do it on the home route. 102 00:06:47,330 --> 00:06:49,340 Let's see what happens if I try running Node app. 103 00:06:49,340 --> 00:06:56,570 JS Looks like we have a syntax error and that is because we're missing parentheses around error there. 104 00:06:58,170 --> 00:06:58,650 Okay. 105 00:06:58,680 --> 00:06:59,940 Now we're good to go. 106 00:07:00,670 --> 00:07:05,200 Now if I try and just go to the home root, not joke. 107 00:07:05,200 --> 00:07:06,670 Well, joke will work just fine. 108 00:07:06,670 --> 00:07:10,060 But if I try and go to the home root, what do you think will happen? 109 00:07:11,720 --> 00:07:15,280 And the answer is that we're not responding with anything. 110 00:07:15,290 --> 00:07:20,660 Assuming we don't get an error, we're not actually responding with any resend, so we're not going 111 00:07:20,660 --> 00:07:21,530 to see anything over here. 112 00:07:21,530 --> 00:07:23,870 It just waits and waits and waits until it gives up. 113 00:07:23,990 --> 00:07:25,310 So I'll stop it here. 114 00:07:25,310 --> 00:07:31,100 But notice we get this row data packet, a single array with one item in it. 115 00:07:31,100 --> 00:07:33,350 So we're going to want to do result zero. 116 00:07:33,650 --> 00:07:42,140 And then what is nicer is if we give it that as alias, so we'll say select count as count just like 117 00:07:42,140 --> 00:07:42,590 that. 118 00:07:42,950 --> 00:07:45,470 So now if I start the server up again. 119 00:07:47,320 --> 00:07:49,330 And I refresh the page. 120 00:07:50,560 --> 00:07:54,630 This time we get row data packet count is equal to 500. 121 00:07:54,640 --> 00:07:55,600 It's inside of an object. 122 00:07:55,600 --> 00:07:58,090 So all I need to do is do dot count. 123 00:07:58,390 --> 00:08:00,820 So this will print 500 now. 124 00:08:00,820 --> 00:08:05,650 So let's save it to a variable var and we'll just call it count as well. 125 00:08:07,490 --> 00:08:09,800 So now we have a variable with the continent. 126 00:08:10,220 --> 00:08:14,660 So then the last step, and I'm going to get rid of this pain here and just work over here. 127 00:08:15,110 --> 00:08:21,680 But now that we have our data coming back from the query, we're going to want to do a resend. 128 00:08:22,070 --> 00:08:27,740 Now, something that tricks people up, trips people up is when you're working with callbacks like this, 129 00:08:28,700 --> 00:08:33,919 it might seem like this code is going to run after this, but in fact, we have no guarantee of that. 130 00:08:34,130 --> 00:08:38,210 What we want to do instead is only run an inside of this callback. 131 00:08:38,600 --> 00:08:44,240 So this is a very common pattern in JavaScript and in Node in particular is that you have these nested 132 00:08:44,240 --> 00:08:47,990 callbacks and it can get out of control sometimes, or something called callback hell. 133 00:08:47,990 --> 00:08:49,130 But this isn't too bad. 134 00:08:49,130 --> 00:08:49,940 It's not bad at all. 135 00:08:50,240 --> 00:08:52,460 So we have our root target. 136 00:08:52,460 --> 00:08:58,370 When this root is hit, run all of this code inside of there, we're defining a query and we're doing 137 00:08:58,370 --> 00:08:59,630 that query and it takes time. 138 00:08:59,630 --> 00:09:04,100 It might take half a second, although that would be pretty long, but it might take a 10th of a second 139 00:09:04,100 --> 00:09:04,850 or something. 140 00:09:05,120 --> 00:09:10,700 And so what we want to do is wait until it comes back and run this code. 141 00:09:10,700 --> 00:09:12,260 If it's an error, throw the error. 142 00:09:12,290 --> 00:09:16,730 Otherwise select or isolate the count from the results. 143 00:09:16,730 --> 00:09:18,560 Then we do a red send. 144 00:09:18,560 --> 00:09:23,390 We have space count space users in our database. 145 00:09:23,660 --> 00:09:24,680 Let's try it. 146 00:09:27,870 --> 00:09:30,480 And then we go, we have 500 users in our database. 147 00:09:31,050 --> 00:09:34,320 And to prove to you that it's working, let's go over here. 148 00:09:34,590 --> 00:09:44,490 Let's do an insert into users and we'll just do an email and we'll do what's something we haven't done 149 00:09:44,910 --> 00:09:46,520 so unoriginal here. 150 00:09:48,060 --> 00:09:55,590 Austin at gmail.com is still struggling with the originality there, so we'll enter that single user. 151 00:09:56,770 --> 00:09:57,360 Okay. 152 00:09:57,370 --> 00:10:00,220 So there now should be 501. 153 00:10:00,220 --> 00:10:01,660 And we can double check. 154 00:10:02,900 --> 00:10:03,770 And it works. 155 00:10:04,340 --> 00:10:08,570 Note that we didn't have to restart the server or anything that would kind of defeat the purpose if 156 00:10:08,870 --> 00:10:13,670 our data was only refreshed whenever the server restarted because we're going to have people interacting 157 00:10:13,670 --> 00:10:17,220 with this when we set this app up and we finish it up. 158 00:10:17,240 --> 00:10:22,100 It'll just be left running, you know, in perpetuity until, I don't know, we have to fix something 159 00:10:22,100 --> 00:10:28,340 or I don't know, we launch our start up app, so it will be waiting, just running constantly. 160 00:10:28,340 --> 00:10:33,200 So our database will be changing all the time and it has a connection to that database. 161 00:10:33,560 --> 00:10:37,520 It can kind of check in with the database, see how it's working, what's going on, and get the results 162 00:10:37,520 --> 00:10:39,440 back and do something with them. 163 00:10:40,220 --> 00:10:43,130 So still very simple, but it's a start. 164 00:10:43,250 --> 00:10:45,020 So we're connecting with our database. 165 00:10:45,590 --> 00:10:50,030 The next thing that we'll need to focus on, which will take us a bit to get there, because we have 166 00:10:50,030 --> 00:10:54,320 to create the form and talk about HTML and a thing called Aegis. 167 00:10:54,320 --> 00:11:00,290 But our next goal is to also run the insert query that will insert a new user in. 168 00:11:00,650 --> 00:11:05,750 But like I said, to do that we have to set up the root that will insert and we have to set up the form 169 00:11:05,750 --> 00:11:07,310 to actually do the insert for us. 170 00:11:07,310 --> 00:11:08,450 But that's where we're heading.