In the last lesson, we're working on our travel tracker application, and we learned about how to use
select in PostgreSQL.
And we completed the first part of our application by applying the things that we learned.
So now let's do the second part of the application.
And in order to do that, we need to learn how to use insert in our PostgreSQL and node back end.
Again we're going to use the data from our rice and wheat production.
If at this point of the course you still don't know that I'm just obsessed with food, then I'm glad
I've hidden it well.
And by the way, how great is rice?
Just good rice.
And if you're not into food, a really good ball of sushi rice is surely going to change that.
In fact, sometimes I just snack on rice itself from the rice cooker.
But anyways, continuing with programming, we have our table of data from various countries and their
rice and wheat production, and we're going to use that table to learn to add data into the table.
The key word here is the insert command.
And you've seen this previously when we looked at SQL in the last module.
But essentially what I want you to do is to take that table that you've already got, which is called
world Food.
And I want you to insert a new row or a new record of data.
And the data is for Italy.
So that's the country name.
And then 1.46 is the number of million tons of rice production that happens in Italy, which is actually
surprisingly large.
So slightly obscure fact if you're based in Europe, most of the sushi rice that you're eating, produced
by all of the Japanese sushi rice companies, is actually produced in Italy.
They seem to be some sort of rice specialists of Europe.
And then this is the amount of million tons of wheat production they produce.
So we're going to insert that into our table.
And the pattern looks like this.
So we have insert into and then the name of the table.
And then after some parentheses the columns that we want to insert to.
And then finally we can add the values.
And the values are separated by commas.
And the columns are also separated by commas.
Before I reveal the solution I want you to have a go trying this out.
So pause the video and see if you can complete this.
All right, so let's go into our world food and let's view all our rows.
You can see it goes up to the ninth country United States.
And now we want to use our query tool to insert our data.
The keyword is insert into.
And then it's the name of our table which is world food.
And then we have a parentheses that sets which columns we want to insert into.
So we're going to leave the ID because that can be auto generated.
But we need to add the country the rice production and the wheat production.
The order here needs to match the order that you're going to add the values in.
The next stage is to add the values.
And again we need to set a parentheses.
And we're going to add in the values in order.
First is the name of the country which is Italy.
And remember to insert strings in SQL we need a single quote.
And then it's the number of million tonnes of rice production and wheat production, which is 1.46 and
7.3 respectively.
Now we've got our values, we've got our columns.
We can now go ahead and cap off this command with a semicolon.
So now let's hit run.
And hopefully everything went all right.
And we can now go ahead and view our data.
And you can see that we've managed to programmatically using SQL commands to insert that final row in
there.
Previously I showed you how we can use Pgadmin to insert rows like this.
But now we need to know how to do it using SQL because we're going to need to insert new data whenever
a user adds a new country they've been to.
And we're going to have to do that using programmatic methods in order to be able to continue and complete
our travel tracker application, we're going to create a new table that allows us to reference the country
name that a user types in with the country code of that country.
So for most users, they're not going to have the two letter country code memorized by heart.
So we should do that heavy lifting for them in the back end so that if somebody types in Angola, then
we should look up in this table to see what is the two letter code for Angola.
And it's a and then we can go ahead and save that code into the visited countries table.
That's the goal.
And the way we're going to do this is we're going to create a new table by importing some data into
it.
So if you take a look at the current lesson, you should be able to download a file that contains countries
dot CSV.
And that's the data that we're going to import into this table.
The table that you're going to create is called countries.
And you're going to create it inside your world database.
And you're going to create the columns looking at this header here.
So we've got our usual primary key ID.
And then we're going to create a country code which is just going to be a char two because there's only
ever going to be two characters fixed.
And then we have our country name which will be a Varchar 100 just to allow for very long country names
like the Democratic Republic of Congo, etcetera.
The CSV contains the ID, the country code and the country name, and you're going to create the table
using SQL.
And then you're going to go ahead and import the data from countries dot CSV.
Pause the video and complete this challenge.
All right, let's go ahead and do this together in Pgadmin.
I'm going to go ahead and go to my query tool.
And I'm going to write the create table.
Table name is countries.
And then I'm going to add three columns.
So the ID which is a serial primary key.
The next one is going to be the country code.
And this is going to be a fixed length char.
So just two characters.
And then we've got the country name which is going to be a variable character length varchar.
And we're going to set it to 100.
Now let's go ahead and run this code to create our table.
And then once we've got that table then we can go ahead and import some data into it.
And if you look inside your downloads or wherever you saved your countries dot CSV, let's go ahead
and use that as the import.
And then making sure that header is checked.
And all the columns this time have data.
So we're going to import all of them.
Let's click okay.
And once the process has completed we can take a look at all the countries we've got which is actually
quite a lot.
So we've got all the countries in the world listed in here.
And their corresponding country codes are matched up in the table.
Now let's go ahead and use this to create the final part of our application, which you can see a demo
of.
If you go ahead and use Nodemon to run solution two dot JS.
Now if we go to localhost 3000 we can type in a country.
So a country I've been to Japan is one of them.
So now if I click add that country will light up and get added to my travel tracker.
And this is pretty cool.
Recently I went to Canada and we can just keep adding these countries and you'll see the total count
go up, tracking all the places that you've been and keeping a record of it.
That's the goal.
But before I release you into the wild writing code, I just wanted to draw your attention to one thing,
which is that when we write SQL queries and we use the insert into command, this is how we insert values.
And the reason is because our values are hard coded, we already know what the values should be, and
we're putting it in a string, a number, etcetera.
But when we do this in our node back end, we're going to be using db query that comes from the pg npm
package.
And the way that this is structured is it allows us to insert two parameters.
So the first one is the SQL command.
And the second one are any values that we want to insert into the SQL command.
And what this allows us to do is to insert not just hard coded values like these strings and numbers,
but also maybe variables or constants or even a programming expression that you want to calculate on
the fly, like five times six.
And the way that we do this is we write our SQL command as you know it, but then we add in the dollar
placeholders for the values that we want to insert.
So in this case instead of values and then all the values I've got my values dollar one, dollar two
and dollar three.
The next part after the comma here is an array.
And inside the array we can add the corresponding expressions or variables that is going to be used
and replaced in these positions dollar $1 $2 three.
In the order that we put into the array.
So this will become dollar one.
This will become dollar two and this will be inserted in position of dollar three.
So this is just something to be aware of.
But once you're ready go ahead head into your index.js and continue writing the code that you have in
order to be able to handle the form entry that comes in here.
So remember, you can check in the index.js to see what action the form hits up and what method it uses
it, and then catch it inside your server and handle that request.
Remember, we've already got body parser installed and enabled, so you can get hold of the data that
comes in from the input, and then you're going to check the name of the country that the user typed
in against the countries table, so that you can get hold of the corresponding country code, and then
you're going to save that country code in.
Into the visited countries table, and then you can figure out how you're going to use that data in
the table in order to update the world map.
There's several programming challenges or conundrums, and if you like a bit of a puzzle, then this
one is definitely right for you.
Pause the video and give this a good attempt.
Again, I will say this is going to involve some thinking, some debugging, some dead ends, and some
trial and error.
But I believe in you, so give it a go.
All right, let's go through the solution together.
So if you take a look inside solution two, you'll see that we've made some modifications to the code
as we last saw it.
The first thing I did was to create a new route App.post.
And that's because inside index.js we've got a form that is going to post to our forward slash ad route.
We can catch that in here post and we address it to forward slash ad.
And then we've got our usual req res.
And then here we get hold of the input which is what the user typed in to the input in the form that
has a name equal to country.
This means we can basically just get hold of whatever they typed into that box.
Once we've gotten hold of whatever the user typed into the input, let's say for example, France,
then we want to use that input to make a query to our database.
So we use db query and we use the select keyword to look for a country code from our table of countries
where the country name is equal to whatever this placeholder is.
And that placeholder is of course going to be what's in the input.
This allows us to put an expression and insert it into our SQL in a dynamic way.
Now it's going to search our countries table to look for France, find the country code and then store
it inside.
Result.
You might remember from before, or if not, just do a console log and you'll see that result contains
a bunch of stuff.
But the only useful stuff that we care about is inside result dot rows.
And hopefully if all going well, we should get an array with only a single value, which is the code
for France.
However, if the result rows is actually completely empty, then that means we got a no match from this
query.
And if the length is not equal to zero, so there is some sort of result inside result dot rows, then
we continue and we grab hold of the data at the first position of the row, and we get the country code
that is inside that piece of data.
Now how could you figure out all of this?
You can either use documentation which is a little bit arduous, or just simply use a bunch of console
logs to look at the data each time and check to make sure that your assumptions matches reality, which,
you know, sometimes it does and sometimes it doesn't.
We can't predict everything correctly.
Once you've gotten hold of the country code that matches the country the user typed in, then we make
another query and this query allows us to use the insert into keyword that we looked at in this lesson.
And we're inserting into the visited countries table into the country code column.
And the value is going to be a dollar one replaced by the country code from this line of code here.
And then finally we go ahead and do a redirect.
Go back to the home page, which if you remember is going to query our database of visited countries.
And go ahead and look for all of the country codes from there to display inside our index.js, passing
it through here and here.
You'll notice that I've split off this function to check which countries have been visited, and then
use the output back into the app.get.
This is optional at this point.
This is something that's going to set us up for the future as we continue refining this app.
But if you have it all inside App.get, that's perfectly fine.
That's exactly how I would expect.
Now.
If we test our app then it should work to a large extent.
So let's enter a country name.
Let's try Germany.
You can see Germany light up right there and if we match our country names then it should always work.
So let's put in another place.
I've been to Colombia, which is a really beautiful place, and you can see it works exactly as we expected.
Step two is pretty much complete.