MySQL makes it easy to create and improve navigation as you're working on a project. Up until this point, your navigation has just been a list of subjects, but now you can add the individual pages that correspond to each subject. This online video describes the process of adding pages to your navigation list.
- View Offline
In this chapter we're going to focus on creating and improving the navigation that we've started. So far navigation is just a simple list of subjects that we're iterating through in a simple loop. What we want to do now is add pages below each one of those subjects, that is to display the pages that belong to that subject below each one. We're going to do that in managecontent.php. Go ahead and open up that file. So what we want to do here, we're finding all of the subjects. And then right here we've got a loop where we're looping through all the subjects. We've got an li tag that displays each one of the subjects.
And what we want to do is right below that, display another list of pages. What we're going to do is put it inside these li tags. What I want is to have ul class pages/ul and then what I want is to list out a list items of all of the pages in here below each one. Its inside the li tag for the menu name. What we want is now a list of pages that belong to this item. So let's start by doing just a little bit of cleanup here. I'm just going to take out a few of these comments we don't need some of these. And I'm also going to go ahead and take this query that I was doing for the subjects.
I'm going to cut that and just drop it down here and do it right before I actually iterate through them. So it's right above it. And I'm also going to go down here and grab where I had the free result. Let's copy that and let's bring it right up here after we're done with it. Right as soon as we're done with the loop then we'll free the result here. So, now, the process that we want to do to iterate through these pages is to make another query. We want to query the database, and find out what pages belong to this subject that we're currently looping through. Well, the way we design the query. So we can just copy all of this up here.
And inside the loop. It can be anywhere inside the loop. But it has to be inside the loop. We're going to do our query. So here it is, but instead of doing select all from subjects, we're going to select all from pages. Where visible equals one order by position ascending. That all works great, however there's one additional thing that we need. This is going to find all pages for all subjects and we don't want to do that. We want to limit it only to the pages that belong to the current subject. So the way we'll do that is we'll add another item to query. And we'll make another condition where visible equals one and where subject id is equal to, and then in curly braces we'll put in our subject id. Now what is our subject id, well it just so happens that it's right up here where you can see it.
You can copy that, you can just drop it right in there. Now there's a problem with this. Be careful about this. When you're constructing these queries, notice I don't have a space after this one. I need a space here, otherwise, when it constructs this query, this subject id, let's say it's number four, will run together with the word order. It'll be four order, which is not valid SQL. So these spaces are very important when we're constructing SQL statements over multiple lines like that. You know we could also do it by concatenating strings, instead of by dropping it in right here. But even if we did that, you still want to make sure that you have that space right at the end. You could also put the space at the beginning of this line, and it would work just as well.
It's very important. Now let's think about what we've done here for a second. We've got, query, here that's a variable that we're reusing. We use it up here, we're using it again down here. But that's okay, because what we did was we assembled this string, and assigned it to the variable, query, and then we used it, and then we were done with it. That's not a problem. At this point we've used it for query and we are done with it, we don't need it any more. So we can reuse that. However, notice that we're also reusing result. That we're not done with. Here you see we're still looping through result, so we don't want to assign something else to that value, or that's going to mess up our loop. We want to keep that so we can keep working with it, so we can't reuse it. We have to give it a different name.
So let's call it page set. That'll make it clear that it's a result set. And then we can actually change this one up here to also be subject set, if you like. Just make sure that you change it in all places where we're using it, even down here where we free up the result. Okay, so now we have our query and we're using good variable names. Query comes back, we confirmed it, now we're ready to loop through it. And we know how to loop through because we've got it right here. You can go ahead and copy the li tag as well, and actually, I'll copy where it has subject name. We'll reuse all of that. I'll paste that in.
Now instead of subject, of course, we're going to be going through while page equals an item that we fetch from the page set. Page menu name, and we'll take away the id. We don't need that at this point. And then we've got our LI tag here, lets go ahead and just add an ending LI tag and I'll take away some of this extra space that's all on one line. So we'll loop through each one. We have the beginning of our while loop, with our curly brace but we don't have the ending, we need to have that ending curly brace as well. Let's line those up. So there we go.
While we have a page, go through and output that page's name. Then we'll loop back over the top and we'll go to the next one. Once we're done with all of those, we'll close out the ul, we'll close out the li for the subject. And then we'll be ready to loop again on the subjects and go back to the next subject. And it'll do the same process over again. The one thing we forgot was to free up the result, so let's do that as well. Right here after we're done with the page set, let's free that up. Now that result set is free. In truth it probably would have gotten rid of it when it reassigned it here, but it's always good idea just to make sure. Okay, so now that we've got all this let's try it out. Let's save it, see if our loop worked.
We're going to go to Firefox, and it's going to be a local host... For me, it's Kevin Scoland widget corp public manage content.php. And there it is. Got each of our subjects and then below we've got each of our pages. And I have a style in my style sheet that tells it to put the square in front of each of these pages. So there we go. Let's go ahead while we're at it, let's take away the ID for the subject. We don't need that anymore as well. That was good while we were doing some debugging. Let's save it and reload the page one more time and that's a little cleaner.
So now we have a navigation that displays both of our subjects and our pages. Make sure that you understand the process that we're going through and especially how those loops within loops work. In the next movie we're going to look at how we can improve this further by doing something called refactoring.
- What is PHP?
- Installing and configuring PHP and MySQL
- Exploring data types
- Controlling code with logical expressions and loops
- Using PHP's built-in functions
- Writing custom functions
- Building dynamic webpages
- Working with forms and form data
- Using cookies and sessions to store data
- Connecting to MySQL with PHP
- Creating and editing database records
- Building a content management system
- Adding user authentication