Make sure that the globe_bank database has been created and populated with the correct starting tables.
- [Instructor] Because this course builds on the work that we already did in part one, we need to make sure that we get our database and our database tables in order before we begin. If you're coming to this course directly from part one then you should have everything already in order, but it's a good idea still to make sure. The first thing we need to do is make sure that we have a database already created and we do that with the create database command and then the name of the database we want. In this case globe_bank. Then we want to have a user who has access to that database. By default there's a root user in MySQL who has access to all the databases, but that user has a lot of privileges and we really would rather not use that user with our web application.
So it's a good practice to create a new user, here I'm creating one called web user. So you can see the command is grant all privileges on globe bank and then a period and an asterisk to indicate that it's a wild card for all tables on the globe bank database. And then two and then the name of the user which here is just web user, and where they're connecting from. So web user at local host in this case. Local host could be an IP address or something if the computer were located somewhere else, but because my database and my web application exist on the same computer, local host ensures that only someone can connect locally.
And then the password, identified by and then whatever password you want to use. I'm just using a very insecure password, secretpassword, you should choose something better for yourself. Once we have the database created and we have the user assigned, now from the command line we can interact with MySQL in order to import the data from the end of part one. So MySQL dash U web user, that'll tell it which user to use, dash P will say that I want to provide a password and then I'm going to target the globe bank database.
Then you can see I've got a less than sign followed by the path to the SQL that I want to import. That basically says, read this file and send it in to MySQL, send it in to the globe bank database. And that'll make sure it creates all the tables and the data that we need to be in sync. Let's walk through it together. So to interact with MySQL from the command line I need to open up a command line application. On Windows that's command, prompt, on a Mac that's Terminal. So here I am inside Terminal and I'm just going to type MySQL dash U and I'm going to use my root user to start with, dash P.
Now my root user has a password, I'll type that in. And now I'm logged into MySQL as the root user and I can say, show databases and you'll see that there's just a few basic databases that are there by default in MySQL. It does not have our database that we want. Now if you took part one you probably already have the database and you don't need to do this next step. So create the database globe_bank with a semicolon at the end. And now the database is there.
Show databases and you can see that it does exist now. Now we want to make sure that we have that user created. So grant all privileges on globe bank with a dot and an asterisk to the name of our user which is going to be web user at and then local host, local host identified by secretpassword. Again, you should pick a password that's better than that.
So now I have my database and I have a user that has access to that database. So I'm going to quit out of MySQL to be back at the command line again and this time I'm going to go back to MySQL, but this time I'm going to do it as that new user. Web user with the password that I just created, that's going to be the dash P option. I'm going to target the globe bank database because it now exists. I'll use the less than and then I need to provide the path to the SQL file that we want to import. Now you can put the file someplace that you recognize and then you can type in the path to it.
You may be able to also just drag the file into your Terminal application and have it do a quick shortcut for you for the location of that file. It's going to be different for everyone, so wherever you've got the file located, if it's in your exercise files or if you've moved it to your C drive or to your desktop, locate it there and type in the full path to get to that file. Once you do that you hit return, they'll ask for your password and now it's performed the import. We can confirm that it worked by typing again MySQL dash U web user dash P globe bank and this will just put us into MySQL already using the globe bank database.
Type my password and now if I say show tables you'll see that we have both subjects and pages. We can expect each one of these with show fields from subjects and we can see the structure of the database, we can see what the different columns are, what the types are for each of those columns and then we can also take a look at what's inside each one. Select all, that's what the asterisk means, from subjects, semicolon at the end.
And we can see the data that's in there already. So you can see I've got four different records in my subjects table, they have ID numbers one, two, three and five and you can see that they have positions and they're marked visible, et cetera. Let's take a look at the pages table. Show fields from pages. You can see what that looks like. And then let's select all from pages to see some of the data that's in there. So you get an idea of what's there. The content you can see is blank. We're going to be coming back and working with that content a little later, but for now it's blank.
Now if you have something in there, that's fine too. The most important thing is just to make sure that you have a good starting set of data for the subjects and the pages. There's one more thing that I want to draw your attention to which is that one of the columns in the pages table is subject_ID. If you'll recall from part one, that was an important aspect of how we make relational databases work. Because we have one subject that has many different pages underneath it and that's a one to many relationship. And the way that we make those one to many relationships is by having that subject_ID.
It's called a foreign key and it allows us to match up the foreign key of each page with the primary key of each subject and that's how we know which page belongs to which subject. So that gives you a quick overview of some of the work that we did in part one and also gets everything in place. And at the end we can just quit out of MySQL because we're actually going to be using PHP to access the database throughout most of this course.
- Creating a content management system
- Building dynamic content navigation
- Using context and function options for conditional behavior
- Hiding content from the public view
- Insecure direct object reference
- Allowing HTML in dynamic content
- Previewing content in the public context
- Working with cookies and sessions
- Storing status messages in the session
- Secure user authentication (login/logout)
- Regulating page access
- Nesting related resources
- Managing an ordered list automatically