1
00:00:00,480 --> 00:00:01,050
Okay.

2
00:00:01,050 --> 00:00:03,960
So then that brings us to inserting data.

3
00:00:04,380 --> 00:00:09,600
But if I wanted to instead insert a new user, so I'm going to copy this.

4
00:00:09,630 --> 00:00:10,860
Well, first, I'm going to comment that out.

5
00:00:10,860 --> 00:00:16,410
So we have a record of it and I'll make a note selecting data.

6
00:00:17,940 --> 00:00:18,510
Okay.

7
00:00:18,870 --> 00:00:21,360
Now we're going to try inserting data.

8
00:00:22,800 --> 00:00:32,189
And you would think that all we needed to do is something like insert into users, specify, let's say

9
00:00:32,189 --> 00:00:39,960
email values, and then all we want is we'll do one here and we have to pay attention to quotes because

10
00:00:39,960 --> 00:00:41,640
we're working inside of a string.

11
00:00:41,640 --> 00:00:43,680
So we need to use double quotes here.

12
00:00:44,130 --> 00:00:47,520
And let's say we just make up an email, we'll call it Why it?

13
00:00:49,640 --> 00:00:52,730
The dog at gmail.com.

14
00:00:52,730 --> 00:00:54,170
It's one of my family dogs.

15
00:00:54,350 --> 00:01:01,640
So if we do that and we insert that, so if we kept everything else the same, let's just console.log

16
00:01:01,640 --> 00:01:04,090
results just like that.

17
00:01:04,099 --> 00:01:05,880
And if there's an error, throw the error.

18
00:01:05,900 --> 00:01:07,920
So this is regular SQL, right?

19
00:01:07,970 --> 00:01:15,770
If we ran this and we could verify it, of course, if we ran this, right now it works.

20
00:01:16,790 --> 00: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

21
00:01:20,900 --> 00:01:21,770
else is valid.

22
00:01:21,770 --> 00:01:28,070
So let's do that, run it connection, not query this query function, blah, blah, blah, blah, blah.

23
00:01:28,100 --> 00:01:29,990
If there's an error, let us know.

24
00:01:29,990 --> 00:01:32,330
Otherwise print the results.

25
00:01:32,690 --> 00:01:33,800
Let's see what happens.

26
00:01:35,010 --> 00:01:35,530
Okay.

27
00:01:35,730 --> 00:01:37,230
So look at what we get back.

28
00:01:37,320 --> 00:01:42,570
First of all, it says, okay, packet, we get field count, zero affected rows.

29
00:01:42,570 --> 00:01:44,310
Affected rows is one.

30
00:01:44,430 --> 00:01:45,870
There's no message.

31
00:01:45,870 --> 00:01:47,190
Everything looks okay.

32
00:01:47,580 --> 00:01:54,330
If we went over here and did a select star from users, you'd see that Rusty, the dog is now in here.

33
00:01:54,330 --> 00:01:58,110
So all of that is looking good and that's correct.

34
00:01:58,110 --> 00:02:03,480
This is working how it should work, which is good, but this is not something that really is helpful

35
00:02:03,480 --> 00:02:10,440
to us inserting one item at a time that we're hard coding in where I'm saying, Rusty, the dog, that's

36
00:02:10,440 --> 00:02:11,820
just not great.

37
00:02:11,820 --> 00:02:13,710
Like this is SQL writing.

38
00:02:13,710 --> 00:02:16,020
We could if we wanted to insert Rusty the dog.

39
00:02:16,020 --> 00:02:16,710
Exactly.

40
00:02:16,710 --> 00:02:19,500
We could just copy it and do what we did and paste it here.

41
00:02:19,650 --> 00:02:22,740
What we want is to be able to do dynamic data.

42
00:02:23,070 --> 00:02:29,340
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,

43
00:02:29,340 --> 00:02:35,430
dot internet, dot email, which is going to give me a random email address.

44
00:02:35,430 --> 00:02:37,470
And I can't just stick that in here.

45
00:02:37,500 --> 00:02:42,660
That's not going to work because it's going to just try and insert an email address called fake Internet

46
00:02:42,930 --> 00:02:43,500
email.

47
00:02:43,890 --> 00:02:45,960
So there's another syntax we use.

48
00:02:46,650 --> 00:02:52,440
In truth, you'll probably never use this for inserting where you're actually hard coding in the data.

49
00:02:52,440 --> 00:02:56,130
You're trying to insert what you'll actually do, look something like this.

50
00:02:56,730 --> 00:02:59,310
So we have a slightly different syntax.

51
00:02:59,370 --> 00:03:05,850
The first thing you'll notice is that I'm creating a variable that is storing the person or the user

52
00:03:05,850 --> 00:03:11,940
we're inserting, and it's a JavaScript object where I have a key email and then a value.

53
00:03:12,060 --> 00:03:14,250
Jenny Four, six, seven at gmail.com.

54
00:03:14,520 --> 00:03:15,660
We'll come back to that.

55
00:03:16,260 --> 00:03:21,240
Then our connection query looks the same, but our query is different.

56
00:03:21,240 --> 00:03:26,580
We're doing insert into users and then this weird set question mark thing, we've never seen that.

57
00:03:26,610 --> 00:03:29,430
Is that valid SQL is is something else.

58
00:03:29,430 --> 00:03:35,880
And then also we have a new argument we're passing person in as well and then our regular function with

59
00:03:35,880 --> 00:03:36,930
error and result.

60
00:03:37,200 --> 00:03:39,390
So what I'm going to do is copy this over.

61
00:03:40,300 --> 00:03:49,440
I'm going to comment this one out as well and I'll call this inserting data, take two and paste this.

62
00:03:51,120 --> 00:03:53,280
So a couple of things to go over.

63
00:03:53,310 --> 00:03:55,650
One, does it work?

64
00:03:55,680 --> 00:03:56,970
We'll try that in just a moment.

65
00:03:56,970 --> 00:03:59,220
And two, how does it work?

66
00:03:59,220 --> 00:04:01,200
So we've got this insert into users.

67
00:04:01,200 --> 00:04:02,760
That's the same, right?

68
00:04:02,760 --> 00:04:05,100
That's what we had up here, insert into users.

69
00:04:05,100 --> 00:04:07,800
But then we had to specify exactly what we're inserting.

70
00:04:07,950 --> 00:04:14,130
So we'd say email and if we had ten columns, we would have to say email, comma, X, Y, whatever

71
00:04:14,130 --> 00:04:16,079
they are, right, as we're used to.

72
00:04:16,589 --> 00:04:17,940
But there's another way.

73
00:04:18,300 --> 00:04:24,030
What we can do instead is make a separate entity in JavaScript and object, where I could come in here

74
00:04:24,030 --> 00:04:25,950
and do something like email as Jenny.

75
00:04:25,980 --> 00:04:30,450
We don't have name, but we could have if we had it in our database.

76
00:04:30,450 --> 00:04:31,680
We could have name as Jenny.

77
00:04:31,680 --> 00:04:33,330
We could have 20 different things.

78
00:04:33,510 --> 00:04:39,540
And to insert it all correctly, all I have to do is connection, query, insert into users and I add

79
00:04:39,540 --> 00:04:40,740
set question mark.

80
00:04:40,740 --> 00:04:47,250
And then the second argument is this object and what will happen is this my SQL Fancy library?

81
00:04:47,250 --> 00:04:52,560
This package will basically take this object and then we'll figure out, okay, there's something called

82
00:04:52,560 --> 00:04:53,220
email.

83
00:04:53,250 --> 00:05:03,210
Well, then I'm going to turn this query into insert into user's email values.

84
00:05:04,500 --> 00:05:09,930
Jenny or Jenny, four, six, seven at gmail.com.

85
00:05:09,930 --> 00:05:11,460
We have a quote issue again.

86
00:05:11,460 --> 00:05:16,380
So I'll make this double quotes so it will turn it into this.

87
00:05:16,650 --> 00:05:22,410
But if I had something like email and let me indent this a little bit better.

88
00:05:25,010 --> 00:05:35,570
Let's say we had email comma and let's say birthday is 2001, comma, 11 or slash 11.

89
00:05:35,960 --> 00:05:39,140
We'd actually need dashes for MySQL, but let's say we have that.

90
00:05:39,560 --> 00:05:43,310
Well, by just passing it in here, I never have to mention birthday.

91
00:05:43,700 --> 00:05:50,450
It will know when inserting it to then have birthday equal to this.

92
00:05:51,680 --> 00:05:53,330
So I won't go and type the whole thing.

93
00:05:53,330 --> 00:06:00,290
But the point is that it can dynamically do that for us, and that doesn't seem that helpful when we're

94
00:06:00,290 --> 00:06:01,820
inserting one thing like this.

95
00:06:02,000 --> 00:06:06,550
It's the same problem that we or it wasn't a problem with the same thing we ran into here.

96
00:06:06,560 --> 00:06:11,990
Like if you're just inserting this one hard coded email, why does it matter?

97
00:06:11,990 --> 00:06:17,120
And the answer is down here, well, it matters because now we don't have to hard code it.

98
00:06:17,570 --> 00:06:24,650
What we can do is change this string because we're no longer part of a peer.

99
00:06:24,680 --> 00:06:27,200
Whatever we're typing is inside this text.

100
00:06:27,650 --> 00:06:29,210
But down here, it's not.

101
00:06:29,210 --> 00:06:30,080
It's separate.

102
00:06:30,080 --> 00:06:34,880
So we can replace this with something dynamic, like using faker.

103
00:06:35,210 --> 00:06:38,720
But let's start by just making sure that Jenny works just like that.

104
00:06:39,140 --> 00:06:41,230
Jenny four, six, seven at gmail.com.

105
00:06:41,240 --> 00:06:44,060
Let's save and let's see what happens now.

106
00:06:44,060 --> 00:06:46,580
Insert it into user set person is this.

107
00:06:47,350 --> 00:06:47,980
OC.

108
00:06:50,270 --> 00:06:55,740
And it looks like we got an error this time and it tells us, Oh, och, well, this is a silly one.

109
00:06:55,760 --> 00:06:59,270
This is my fault with the code, but it's something it's good to talk about.

110
00:06:59,690 --> 00:07:03,650
If you look at what the error is, it's telling me error is not defined.

111
00:07:04,280 --> 00:07:06,740
So it's saying this thing error is not defined.

112
00:07:06,740 --> 00:07:10,160
And actually I was ignoring these, but I'm sure Cloud9 is trying to tell me.

113
00:07:10,160 --> 00:07:11,030
Yes, it is.

114
00:07:11,240 --> 00:07:15,230
So it's error is not defined and that's because up here I called it error.

115
00:07:15,260 --> 00:07:16,880
You can call it whatever you want.

116
00:07:17,930 --> 00:07:21,050
So I could call this mistake in all caps.

117
00:07:21,050 --> 00:07:21,950
It's a bad idea.

118
00:07:21,950 --> 00:07:26,240
It would be a mistake to use all caps, but as long as they match.

119
00:07:26,240 --> 00:07:27,860
So I'll go with error, though.

120
00:07:27,860 --> 00:07:29,720
That's a standard thing.

121
00:07:30,290 --> 00:07:31,220
Just like that.

122
00:07:32,360 --> 00:07:33,080
Now.

123
00:07:34,920 --> 00:07:37,370
Now we get a different error this time.

124
00:07:37,380 --> 00:07:38,390
Oh, my goodness.

125
00:07:38,400 --> 00:07:39,480
It's the same one.

126
00:07:39,840 --> 00:07:40,400
Geez.

127
00:07:41,520 --> 00:07:43,040
Now we get a different error.

128
00:07:43,050 --> 00:07:46,590
This time it's saying duplicate entry.

129
00:07:47,010 --> 00:07:47,560
Great.

130
00:07:47,580 --> 00:07:48,690
This is good to know.

131
00:07:48,930 --> 00:07:51,420
Now it's telling us we already have someone named Jenny.

132
00:07:51,420 --> 00:07:53,250
Four, six, seven at gmail.com.

133
00:07:53,640 --> 00:07:57,810
And the reason we're getting this error is that we already have a Jenny in there.

134
00:07:57,810 --> 00:08:01,110
So this is interesting, and this is why I added that primary key.

135
00:08:01,110 --> 00:08:07,980
So we just like 1/2 ago inserted Jenny four, six, seven at Gmail and we hit this problem with the

136
00:08:07,980 --> 00:08:12,660
error coming back where we had a syntax error, but it was still inserted.

137
00:08:12,750 --> 00:08:16,830
Remember, this code only runs when the insert or the query is done.

138
00:08:16,830 --> 00:08:18,900
So we inserted Jenny for six seven.

139
00:08:18,900 --> 00:08:22,380
Then I screwed up our code and so it broke right here.

140
00:08:22,590 --> 00:08:23,940
But Jenny was already added.

141
00:08:23,940 --> 00:08:31,140
So now we just added another Jenny, or we tried to, and this time it came back saying there's a problem.

142
00:08:31,140 --> 00:08:36,600
And if we look at the error that it threw says duplicate entry for key primary.

143
00:08:37,020 --> 00:08:39,480
So you can see now how JavaScript is talking.

144
00:08:39,480 --> 00:08:41,850
This is the kind of remember those arrows.

145
00:08:41,850 --> 00:08:47,520
I won't go back to the slides, the two way arrows where we tentatively tried to insert from Node.

146
00:08:47,550 --> 00:08:49,680
Hey, can we insert Jenny four, six, seven.

147
00:08:49,740 --> 00:08:51,510
Waited until it went to the database.

148
00:08:51,510 --> 00:08:52,320
The database head.

149
00:08:52,500 --> 00:08:53,190
Let me think.

150
00:08:53,190 --> 00:08:54,360
No, sorry.

151
00:08:54,360 --> 00:08:55,380
We already have Jenny.

152
00:08:55,380 --> 00:08:56,070
Four, six, seven.

153
00:08:56,070 --> 00:08:57,840
And there's a primary key constraint.

154
00:08:57,870 --> 00:08:58,740
Don't you remember?

155
00:08:58,740 --> 00:08:59,700
You made the schema.

156
00:09:00,000 --> 00:09:01,350
Well, that's a problem.

157
00:09:01,350 --> 00:09:06,990
I'm going to have to refuse your request and send you back an error, which then is passed into this.

158
00:09:07,840 --> 00:09:10,570
And this part runs if error throw the error.

159
00:09:11,520 --> 00:09:12,460
Okay, cool.

160
00:09:12,910 --> 00:09:13,690
So that's good to know.

161
00:09:13,690 --> 00:09:14,380
It's working.

162
00:09:14,770 --> 00:09:19,450
So rather than doing Jenny four, six, seven, let's change this to something more interesting.

163
00:09:19,450 --> 00:09:22,270
Anyways, let's go back to using Faker.

164
00:09:22,720 --> 00:09:28,090
So we need to require faker again, require faker.

165
00:09:29,230 --> 00:09:31,840
And all we'll do is insert someone with a new email.

166
00:09:31,840 --> 00:09:38,890
So it's just faker, dot internet, dot email like that.

167
00:09:39,840 --> 00:09:45,390
So what this will do is instead of having us hardcoded it, every time we run this file, it will insert

168
00:09:45,480 --> 00:09:48,480
a new email that it just generates.

169
00:09:49,590 --> 00:09:51,570
So let's try it, see what happens.

170
00:09:53,550 --> 00:09:54,010
Okay.

171
00:09:54,150 --> 00:09:55,050
It looks good.

172
00:09:55,230 --> 00:09:56,340
Something happened.

173
00:09:58,430 --> 00:10:02,210
Now you can see we got Velma 94 at gmail.com.

174
00:10:03,530 --> 00:10:05,240
And let's do.

175
00:10:05,270 --> 00:10:08,610
How would we only identify the last one that was most recently added?

176
00:10:08,630 --> 00:10:09,980
Quick review.

177
00:10:10,070 --> 00:10:15,410
Well, we could do an order by created at if we did it that way.

178
00:10:15,440 --> 00:10:18,170
You can see the last one is Velma.

179
00:10:18,170 --> 00:10:23,480
We're going in the wrong order so we can do descending and we could throw in limit one.

180
00:10:23,630 --> 00:10:25,280
Now we get the most recent one.

181
00:10:25,940 --> 00:10:28,640
So if we go back to Bash, try running it again.

182
00:10:29,620 --> 00:10:30,610
And again.

183
00:10:33,500 --> 00:10:35,930
We've got Sanford, Gaylord, 61.

184
00:10:36,020 --> 00:10:41,360
And actually if I change limit to two because we just inserted two, we also have Travis 60 at gmail.com.

185
00:10:42,100 --> 00:10:42,880
Sweet.

186
00:10:43,030 --> 00:10:44,530
So I'll end the video here.

187
00:10:44,530 --> 00:10:50,230
But we saw now how we can insert things dynamically and why this syntax is preferable.

188
00:10:50,230 --> 00:10:54,730
Preferable to this one up here where we're hard coding it as a string, as text.

189
00:10:55,550 --> 00:10:57,860
This works, but it's limited, right?

190
00:10:57,860 --> 00:10:59,710
We can only insert whatever we type.

191
00:10:59,720 --> 00:11:06,340
This allows us to be dynamic and shortly in our web application we won't have fake internet email.

192
00:11:06,350 --> 00:11:13,580
We'll have this is pseudocode whatever the user entered into the form.

193
00:11:13,970 --> 00:11:18,800
So when we have that form, the user enters their email, whatever they type in there, we're going

194
00:11:18,800 --> 00:11:21,020
to stick here and we don't know it, right?

195
00:11:21,020 --> 00:11:22,370
We can't type it out exactly.

196
00:11:22,370 --> 00:11:26,060
We don't know what the email is itself, but we can save it in a variable.

197
00:11:27,020 --> 00:11:30,890
So we'll go back to fake or Internet email and we'll leave it there for now.