Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In the last movie, we created a new pages tables and learned how to relate it to our subjects table. We can't test out that relationship until it has some data in it, so let's add some. So here I am in MySQL. You can see that I've already created my pages table and you can see the columns from the pages table right there in front of me. That's great and easy to see. So I'm going to leave it there, and let's just type in our first insert INSERT INTO pages. And then, remember, we want to list the columns that we want to insert into it.
I'm going to leave off the ID column, because that's an auto incremented column. I'll let MySQL manage that for us. But I do want to pass in the subject ID. Menu, name, position, visible, and content. And then I'll hit Return. And now, I'm going to put into it the values, and so the first value is going to be subject_id. So this is going to be a page that will be related to the first subject_id. So it's id, the primary key of it will be 1.
And then the menu_name is going to be, Our mission. And then the position will be 1, it will be visible. And then let's give it some content, Our mission has always been ... That'll be sufficient for a placeholder. Okay, so we'll out our ending parentheses and a semicolon, hit Return, one row effected, query okay. So let's do Select All from pages now. And you can see, there it is. It created that page for us. Let's create a few more.
So I'm hitting the up arrow to go back. And I'll just make changes right here. And this time. It'll also be for subject one, but the name of it that is going to be our history, the position will be 2. It's going to be the second page, it'll be visible. And then let's just change our text here to some different placeholder text. We'll say, Founded in 1898 by two enterprising engineers. Hit Return. Now, I have two pages in there.
All right, let's add a third page, but let's make this one belong to a different subject. You just bring up our subjects again so we can see them all. Select all from subjects. The first ones are both related to ID 1. We're going to make this one related to ID number two. So I'll hit my up arrow. The first thing I need to do is shoot back over here and change the 1 to be a 2. So now, it's going to have a subject ID of 2 in order to match this ID right here. And then let's change it from our history, this products, so let's say Large Widgets. This is Widget Corp, they make widgets.
So Large Widgets. Now, it's position is going to be 1. Now, you may be thinking, wait a minute. We already had position 1 and 2. But the positions are going to be relative to the subject. So we can have 1, 2, 3 for subject one, and then we can have 1, 2, 3 for subject two, and so on. We're never going to be displaying all of the pages in a single list. They're always going to be in the context of pulling up the pages that belong to a certain subject. It'll also be visible, and then let's just shoot down here real quick and erase all this text. We'll make it say, Our large widgets have to be seen to be believed. Okay.
Let's make another one real quick. I'll erase this while I'm going back there. This one is going to be also for id 2. It'll be Small Widgets. Its position will be 2. It will also be visible and then here lets say they say big things come in small packages. We have two pages that belong with this subject and two pages that belong with this one. Let's now add two pages for the services. So let's just shoot back over here, erase all this copy.
Now, this time the subject id is going to be 3, and the copy is going to be for a service, let's call it Retrofitting. It's position will be 1, again, relative to the subject. We love to replace widgets. And then let's do one last one. This is also going to be for subject three. The name of it is going to be Certification. Read position 2. We can certify any widget, not just our own.
All right, so now we have some good placeholder copy for those and we have a set of pages. Can take a look at pages, select all, my mistake, from pages. There we go. So now you can see all the different pages that we have. And we can see all the different subjects that we have. Select all from subjects. So now we have the data in there. Let's take a look at how the relationships work. So let's say that we want to pull up a page. Let's say a user has requested to look at the Our Mission page to start with. So we say Select all from pages where id equals 1.
That's the our mission page. So we're going to display that page to the user. We found that, PHP has requested this page from us, id 1. So then it says, all right, I need the subject, so I'm going to look at my subject id and see what value I have there. And then I can make another request from subjects where id equals 1 also. Now, it's not the same 1 as this one, it's this 1, that's what we're looking at. And that returns about Widget Corp. Now, I know my subject, and I can find out all sorts of information about it. I can pick up its menu name, maybe I want to look at whether it's visible or not.
I can do that by looking up its subject. It also works in reverse. Let's pick a different one. Let's say that instead, I have a subject where the id is 3. And then now, I have 3, let's Select all from pages where subject id is also equal to 3. There they are. There's the pages that belong to this subject... So, that's how relational databases work. We're doing it by actually typing each of these commands each time. When we get to PHP, PHP will be able to pass variables around to handle a lot of this for us.
We will be able to take a variable from the URL that's asking for subject number 3. It'll be able to take that, go look in the Subjects table, return it. And then if we want to get all the pages that belong to it, it can take that same variable and go ask the pages table to return a list of the pages. That should belong to it and should be displayed. So being able to have PHP and variables is going to streamline this process.
Get unlimited access to all courses for just $25/month.Become a member