WEBVTT 00:00.470 --> 00:01.010 OK. 00:01.230 --> 00:04.470 So then that brings us to inserting data. 00:04.470 --> 00:09.500 But if I wanted to instead insert a new user so I'm going to copy this. 00:09.630 --> 00:16.210 Well first I'm going to comment that up so we have a record of it and I'll make a note selecting data 00:17.900 --> 00:18.660 OK. 00:19.030 --> 00:27.840 Now we're going to try inserting data and you would think that all we needed to do something like insert 00:28.080 --> 00:38.220 into users specify that say e-mail values and then all we want is we'll do one here and we have to pay 00:38.220 --> 00:41.730 attention to quotes because we're working inside of a string. 00:41.790 --> 00:47.250 So we need to use double quotes here and let's say we just make up an email or call it why it 00:50.690 --> 00:52.740 dog at gmail dot com. 00:52.760 --> 00:54.210 It's one of my family dogs. 00:54.500 --> 00:57.830 So if we do that and we insert that. 00:57.910 --> 01:04.190 So we kept everything else the same let's just cancel that log results just like that. 01:04.270 --> 01:06.080 And if there's an error throw the error. 01:06.080 --> 01:07.960 So this is regular sequel right. 01:07.970 --> 01:11.530 If we ran this and we could verify it. 01:11.530 --> 01:16.760 Of course if we ran this right now it works. 01:16.820 --> 01:20.900 So now let's let's change this a little bit so that we don't end up with two of them but everything 01:20.900 --> 01:21.730 else is valid. 01:21.800 --> 01:24.200 So let's do that run it. 01:24.290 --> 01:28.220 Connection that query this query function blah blah blah blah blah. 01:28.220 --> 01:29.950 If there's an error let us know. 01:30.020 --> 01:33.750 Otherwise print the results but see what happens. 01:34.960 --> 01:35.600 OK. 01:35.910 --> 01:37.450 So look at what we get back. 01:37.470 --> 01:43.230 First of all it says OK packet we get field count zero effective rows affected. 01:43.260 --> 01:44.490 Roads is one. 01:44.550 --> 01:45.820 There's no message. 01:46.020 --> 01:47.220 Everything looks OK. 01:47.700 --> 01:51.290 We went over here and did a select star from users. 01:51.390 --> 01:54.300 You'd see that Rusty the dog is now in here. 01:54.510 --> 01:56.610 So all of that is looking good. 01:56.610 --> 01:58.110 And that's correct. 01:58.110 --> 02:03.540 This is working how it should work which is good but this is not something that really is helpful to 02:03.540 --> 02:04.100 us. 02:04.200 --> 02:11.070 Inserting one item at a time that were hard coding in where I'm saying rescue the dog that's just not 02:11.340 --> 02:12.230 great right. 02:12.390 --> 02:15.990 This is sequel writing we could if we wanted to insert Rusty the dog. 02:15.990 --> 02:16.740 Exactly. 02:16.740 --> 02:19.510 We could just copy it and do what we did and paste it here. 02:19.820 --> 02:22.850 What we want is to be able to do dynamic data. 02:23.190 --> 02:30.540 So I want to be able to say which we don't have fake or set up but I want to be able to say faker Internet 02:31.230 --> 02:37.680 e-mail which is going to give me a random e-mail address and I can't just stick that in here. 02:37.680 --> 02:42.020 That's not going to work because it's going to just try and insert an e-mail address called fake or 02:42.210 --> 02:43.610 Internet e-mail. 02:43.950 --> 02:46.330 So there's another syntax we use. 02:46.420 --> 02:52.140 But in truth you'll probably never use this for inserting where you're actually hard coding and the 02:52.140 --> 02:56.800 data you're trying to insert what you actually do look something like this. 02:56.820 --> 02:59.240 So we have a slightly different syntax. 02:59.430 --> 03:05.940 First thing you'll notice is that I'm creating a variable that is storing the person or the user we're 03:05.940 --> 03:07.290 inserting. 03:07.290 --> 03:14.370 And it's a javascript object where a have a key email and then avowed you Jenny 4 6 7 at gmail dot com. 03:14.640 --> 03:15.920 We'll come back to that. 03:16.380 --> 03:21.380 Then our connection at queery looks the same but our query is different. 03:21.390 --> 03:27.120 We're doing insert into users and then this weird set question mark thing we've never seen that is that 03:27.120 --> 03:28.280 valid sequel. 03:28.290 --> 03:29.460 This is something else. 03:29.640 --> 03:34.530 And then also we have a new argument we're passing person as well. 03:34.530 --> 03:37.050 And then our regular function with error and result. 03:37.380 --> 03:44.160 So what I'm going to do is copy this over and we're going to comment this one out as well and I'll call 03:44.160 --> 03:45.920 this inserting data. 03:46.260 --> 03:49.870 Take two and paste this. 03:50.450 --> 03:50.990 OK. 03:51.300 --> 03:53.700 So a couple of things to go over. 03:53.950 --> 03:55.690 One doesn't work. 03:55.740 --> 03:57.950 We'll try that just a moment two. 03:58.170 --> 03:59.370 How does it work. 03:59.370 --> 04:02.750 So we've got this insert into users that's the same right. 04:02.760 --> 04:03.860 That's what we had up here. 04:04.010 --> 04:07.840 Entered into users but then we had to specify exactly what we're inserting. 04:08.100 --> 04:14.100 So we'd say you know email and if we had 10 columns we'd have say email comma X have a y come whatever 04:14.100 --> 04:14.440 they are. 04:14.460 --> 04:16.110 Right as we're used to. 04:16.650 --> 04:22.920 But there's another way what we can do instead is make a separate entity in javascript and object where 04:22.920 --> 04:24.790 I could come in here and do something like. 04:24.810 --> 04:25.970 Email is Gennie. 04:26.130 --> 04:31.870 We don't have a name but we could have if we had it in our database we could have name is Gennie we 04:31.910 --> 04:37.890 could have 20 different things and to insert it all correctly all I have to do is connection not query 04:38.100 --> 04:43.690 insert into users and I add set question mark and then the second argument is this object. 04:43.710 --> 04:50.190 And what will happen is this Majeski will fancy library this package will basically take this object 04:50.740 --> 04:53.190 and it will figure out OK there's something called e-mail. 04:53.430 --> 05:03.170 Well then I'm going to turn this query into insert into user's e-mail values. 05:04.590 --> 05:12.990 Jenny Jenny for 7 at gmail dot com we have a quote issue again so I'll make those double quotes. 05:13.170 --> 05:13.470 OK. 05:13.470 --> 05:16.520 So it will turn it into this. 05:16.710 --> 05:22.160 But if I had something like e-mail and it did this a little bit better 05:25.020 --> 05:36.370 I'd say we had e-mail comma and that's a birthday is two thousand and one comma 11 comma slash 11 and 05:36.420 --> 05:37.800 you dashes from my school. 05:37.890 --> 05:39.260 Well let's see if we have that. 05:39.670 --> 05:43.490 Well by just passing it in here I never have to mention birthday. 05:43.830 --> 05:53.000 It will know when inserting it to then have birthday equal to this so I won't go and type the whole 05:53.000 --> 05:53.320 thing. 05:53.330 --> 06:00.230 But the point is that it can dynamically do that for us and that doesn't seem that helpful when we're 06:00.230 --> 06:02.090 inserting one thing like this. 06:02.180 --> 06:03.860 It's the same problem that we are. 06:03.870 --> 06:04.570 It wasn't a problem. 06:04.580 --> 06:06.520 The same thing we ran into here. 06:06.520 --> 06:12.140 Like if you're just inserting this one hard coded e-mail why does it matter. 06:12.160 --> 06:13.700 And the answer is down here. 06:13.880 --> 06:21.680 Well it matters because now we don't have to hard coded what we can do is change this string because 06:21.680 --> 06:27.370 we're no longer part of a peer whatever we're typing is inside this text. 06:27.770 --> 06:30.100 But down here it's not it's separate. 06:30.230 --> 06:35.020 So we can replace this with something dynamic like using faker. 06:35.360 --> 06:38.820 But let's start by just making sure that Gennie works just like that. 06:39.180 --> 06:43.890 Jenny 4 6 7 and she mailboat come save and let's see what happens now. 06:43.980 --> 06:48.740 Insert into user set person is this OK. 06:50.310 --> 06:55.820 And it looks like we got an error this time and it tells us oh OK well this is a silly one. 06:55.860 --> 06:58.050 This is my fault with the code but it's something. 06:58.080 --> 06:59.380 It's good to talk about. 06:59.730 --> 07:03.830 If you look at what the error is telling me error is not defined. 07:04.410 --> 07:06.840 So it's saying this thing error is not defined in. 07:06.840 --> 07:10.080 Actually I was ignoring these but I'm sure Cloud 9 is trying to tell me. 07:10.260 --> 07:12.320 Yes it is says error. 07:12.360 --> 07:14.510 It's not defined and that's because appear I called it. 07:14.600 --> 07:17.680 E.R. you can call it whatever you want. 07:18.000 --> 07:21.930 So I could call this mistake in all caps it's a bad idea. 07:21.960 --> 07:23.870 It would be a mistake to use all caps. 07:23.880 --> 07:26.260 But as long as they match. 07:26.310 --> 07:31.330 So I'll go with E-R though that's a standard thing just like that. 07:31.670 --> 07:32.260 OK. 07:32.550 --> 07:37.380 Now now we get a different error this time. 07:37.390 --> 07:38.430 Oh my goodness. 07:38.560 --> 07:39.510 It's the same one. 07:39.820 --> 07:41.130 Geez. 07:41.650 --> 07:43.030 Now we get a different error. 07:43.210 --> 07:46.950 This time it's saying duplicate entry. 07:47.030 --> 07:47.640 Great. 07:47.650 --> 07:49.060 This is good to know. 07:49.090 --> 07:53.640 Now it's telling us we already have someone named Jenny for six seven at gmail dot com. 07:53.740 --> 07:57.800 And the reason we're getting this error is that we already have a genie in there. 07:57.910 --> 08:01.150 So this is interesting and this is why I did that primary key. 08:01.200 --> 08:07.900 So we just like one second ago inserted Jenny for six seven at gmail and we hit this problem with the 08:07.900 --> 08:12.690 error coming back where we had a syntax error but it was still inserted. 08:12.790 --> 08:16.810 Remember this code only runs when the insert or the query is done. 08:16.990 --> 08:20.730 So we inserted Jenny for 6:7 Then I screwed up our code. 08:20.820 --> 08:22.390 And so it broke right here. 08:22.690 --> 08:24.040 But genuine already added. 08:24.070 --> 08:25.920 So now we just added another Gennie. 08:25.910 --> 08:32.510 Here we tried to and this time it came back saying there's a problem and if we look at the error that 08:32.530 --> 08:36.850 it through it's duplicate entry for key primary. 08:37.150 --> 08:39.470 So you can see now how javascript is talking. 08:39.490 --> 08:45.280 This is the kind of remember those arrows I won't go back to the slides the two way arrows where we 08:45.280 --> 08:47.480 tentatively tried to insert from node. 08:47.560 --> 08:51.510 Hey can we insert Jenny for 6:7 waited until it went to the database. 08:51.550 --> 08:57.870 The database had me think no sorry we already have Jenny for 6:7 and there's a primary key constraint. 08:57.970 --> 08:59.860 Don't you remember you made the schema. 09:00.160 --> 09:01.330 Well that's a problem. 09:01.420 --> 09:08.100 I'm going to have to refuse your request and send you back an error which then is passed into this and 09:08.100 --> 09:10.930 this part runs if error throw the error. 09:11.510 --> 09:12.640 Ok cool. 09:12.990 --> 09:14.870 So that's good to know it's working. 09:14.880 --> 09:19.440 So rather than doing Gennie for 6:7 let's change this to something more interesting. 09:19.440 --> 09:22.770 Anyways let's go back to using faker. 09:22.860 --> 09:31.950 So we need to require F.A. again require faker and all we'll do is insert someone with a new e-mail. 09:31.980 --> 09:39.770 So it's just faker dot Internet dot email like that. 09:39.960 --> 09:46.470 So this will do is instead of having hardcoded it every time we run this file it will insert a new e-mail 09:47.170 --> 09:49.640 that it just generates. 09:49.650 --> 09:51.500 So let's try and see what happens. 09:53.470 --> 09:55.250 OK it looks good. 09:55.350 --> 09:56.260 Something happened. 09:58.350 --> 10:07.100 Now you can see we got Velma 94 at gmail dot com and let's do Howard we only identified the last one 10:07.110 --> 10:08.720 that was most recently added. 10:08.730 --> 10:09.930 Quick review. 10:10.170 --> 10:15.430 Well we could do an order by created that if we did it that way. 10:15.540 --> 10:22.080 Can See The last one is vellum and we're going the wrong order so we can do descending and we could 10:22.080 --> 10:23.740 throw in limit one. 10:23.850 --> 10:25.520 Now we get the most recent one. 10:26.040 --> 10:30.340 So if we go back to bash try running it again and again 10:33.500 --> 10:35.930 we've got Sanford Gaylord's 61. 10:36.200 --> 10:38.940 And actually if I change limit to 2 because we just inserted 2. 10:38.960 --> 10:42.910 We also have Travis 60 gmail dot com suite. 10:43.160 --> 10:49.520 So I'll end the video here but we saw now how we can insert things dynamically and why this syntax is 10:49.520 --> 10:55.590 preferrable preferable to this one up here where we are hard coding it as a string as text. 10:55.610 --> 10:57.860 This works but it's limited right. 10:57.860 --> 10:59.880 We can only insert whatever we type. 10:59.900 --> 11:06.240 This allows us to be dynamic and shortly in our web application we won't have fake or internet or email 11:06.440 --> 11:07.320 will have. 11:07.370 --> 11:14.120 This is pseudo code whatever the user entered into the form. 11:14.120 --> 11:19.100 So when we have that form the user enters their email whatever they type in there we're going to stick 11:19.100 --> 11:20.070 here and work. 11:20.110 --> 11:20.980 We don't know it right. 11:20.990 --> 11:22.330 We can't type it out exactly. 11:22.330 --> 11:26.750 We don't know what the e-mail is itself but we can save it in a variable. 11:26.750 --> 11:26.980 OK. 11:27.000 --> 11:30.910 So we'll go back to fake or dumb Internet e-mail and we'll leave it there for now.