Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
PHP is a popular, reliable programming language at the foundation of many smart, data-driven websites. This comprehensive course from Kevin Skoglund helps developers learn the basics of PHP (including variables, logical expressions, loops, and functions), understand how to connect PHP to a MySQL database, and gain experience developing a complete web application with site navigation, form validation, and a password-protected admin area. Kevin also covers the basic CRUD routines for updating a database, debugging techniques, and usable user interfaces. Along the way, he provides practical advice, offers examples of best practices, and demonstrates refactoring techniques to improve existing code.
Now that we have a working navigation for our staff area, which sends an ID for the current subject of the current page. We're ready to use that ID to pull up the content that corresponds to it. Now, this is our first step in adding CRUD to our application. It's the read, in create, read, update and delete. So we're still going to be working with our manage content.php page. Essentially what we want to do is right here in this manage content area. We want to display the subject if the subject is selected, or the page, if the page is selected.
We just want to read back that data. So let's first of all, let's just put in a little PHP here. And let's just do a real quick, PHP if Let's check and see if the selected subject ID has been set. Then we know that we're going to display a subject. Alright. And let me just make another PHP block here. So that will go like this. Else.
If we have a deleted page id then we'll know that we are working with a page. Otherwise we'll know that we don't have either one, nothing has been selected... (SOUND). Okay. So there, go ahead and get that in place. And right now, it's just going to display these for us. That's all it's going to do is just echo it or echo it. Or, let's have it say please select a subject or a page. So we'll bring that up, and we'll reload it in our browser. And you'll see if we don't have anything selected, Please select a subject or a page.
And now it just displays one of those, depending on whether we have a subject selected or a page selected. Okay, so now, to this block. Let's, let's work on this. How are we going to go about finding the subject that we want? Now, we could do this in two ways. First, we could go through our subject results, the ones that we used to make a subject list in the navigation. We could go back through that results set and look for the subject that matches the current subject id. That would work. However, when you're developing with CRUD, it's not always going to be the case that your list of subjects is visible on the same page as the subject details are.
It's actually more common to switch to a new page and then click a link that says back to list or something similar to that to return to the list of subjects. We saw this when we were looking at the manage admin section in our demo of the finished project. So, I'm going to take the more generic approach that will work all the time. We'll take the subject ID, and we'll make another SQL query. We want to be smart about making our queries. We don't want to make them unnecessarily, but also don't be timid about it. My SQL queries happen very quickly, especially when the data set is small.
In fact, depending on the data, it can actually be faster to query again than it is to loop through a result set looking for what you need. One is just asking PHP to do the search. And the other is asking MySQL to do the search. And in general, because of it's indexes, MySQL is going to be the faster of the two searchers. So let's write our query. But instead of doing it in manage content.php and then refactoring it to a function like we've been doing, let's go directly to our functions and write it there. So I'm just going to move this out of the way.
Let's go find functions.php and open that up and I've got all of these out of my way. I'm going to make a new function here. Function find_subject_by_id and then I'll provide it a subject ID. So this is where I'm going to write the function that I need. Now we can get some clues as to how this is going to work by looking at find all subjects. Find all subjects is finding all subjects for us, and notice that we use global connection, we find all subjects and we return the subject set at the end.
So let's just grab all of that and copy it. And let's paste it over here in subject by id. Now we're going to want to make a few changes to it. We do still want to have our connection. Our query's going to be a little bit different. We could just limit it to the visible ones, but because we're in the admin area, we actually are going to want to have all of them, not just the visible ones. So we're gona need to change that. In fact, it occurs to me that probably we should remove that up here from find all subjects. We want to see the invisible subjects in our list as well. I'm not going to delete it for now I'm just going to comment that line out.
So it's just going to find all subjects ordered by their position. Okay, so now let's come back to this one where we have the visible equals one instead we want to say where ID is equal to and then subject ID. So that will now construct SQL that will find it. We don't need to order it by position anymore, because we're only going to be finding one. Right, there's only going to be one that's going to come back to us. And in fact, it's a good practice to go ahead and put limit one here as well. Because it's a unique id, we should only be getting one, but what if we were finding by menu name? Right, there might be two things that have the same menu name.
So it's always a good practice just to have limit one here. And its been a reminder to you that this is only going to return one thing. Now it will go through. It will make the query. It will come back with the subject set. Confirm the subject set worked and it will return that subject set. Once it gets returned, what are we going to do? Were going to all mysql_fetch_association to get the first row. But there's only one row. So if there's only one row, why do we need to do that step. Why not build that in here, save ourselves that additional step.
So let's do that. Let's do subject equals mysqli_fetch_association from the subject set. So now we're going to return the row itself. We just saved ourselves that step. When we're doing spine by ID, let's go ahead and just get that associative array for the subject. And return it instead. Now there's the possibility that it won't find anything. Let's think about what happens in that case. mysqli fetch associate returns false if it's not there. I'm actually just going to change it a little bit.
I'm going to put an if statement around this whole thing. So, if it returns true then in that case we want to return the subject. But if it returns false, now we have the option to do something else. And you could have it still return false, that would have the same behavior, but I'm going to instead have it return null. So it returns nothing if it doesn't find one. Now there's one more thing that we need to be mindful of here. We're going to be getting subject id from our url string. Right it's part of the get request, it's going to be in the query portion of the url string. That means that it's subject to being tampered with. So we have to think back to what we learned about being mistrustful of those values.
We could very easily end up with something that's an SQL injection because we're taking that value. Whatever the user gives us, we're just dropping it into the SQL string. That's a major, major security concern. So we need to escape that value, and we saw how to do that before. You could just do it right here. You could catenate a value using my SQL real escape string on it. I think it's actually better to go ahead and put it up here. And I'm going to create something called safe subject ID equals mysqli real escape string.
It's first argument is the connection. And it's second argument is whatever we want to escape, which is going to be the subject ID. So now I have something called safe subject ID, that I can put here instead, and I know that it's safe. I know that it's okay to drop it in, I'm no longer subject to an SQL injection. Now should we do the same thing up here? We could. There's no reason why we can't. Let's go ahead and just do it. It makes a little more secure. These values up here were coming from the database. So the chances are less that we would have an SQL injection but it's still not a bad idea. And find all subjects is not pulling in any values. Our navigation is going to be passing in an ID, but it's passing in to find pages for subject.
And we just added the escape to find pages for subject. So it will only get escape. We only have to do it once. And so it's a good idea, I think, to do it right before you actually do the query. Okay? So now we have it built. The last thing is to use it. So let's just copy this. And let's shoot back over here. And instead of echoing Select Subject ID, right above it. Let's do PHP, current subject is going to be equal to find subject ID and pass in Selected Subject ID.
Alright. That's the name of it here. Selected subject ID. We'll pass that in. This will now return an associative array to us. So now, we can echo the current subject. And let's just echo its menu name. And in front of it, we'll put menu name. Put a br tag at the end. So now, these is what'll do. If we have a subject selected, it'll find that subject in the database and assign the associative array to current subject and then it'll echo the result. Let's try it out. Let's go to Firefox. See if we have any bugs in our code.
Menu name about Widget Corp. Now for the pages we still just get numbers. We go to products we get products. We go to services it says services. We're now reading data from the database. We're performing that CRUD. Now, we were doing something similar when we created the navigation, but I think this has more of the feel of create, read, update and delete. We're looking at the subject services right now. We're reading that. Now we still haven't done anything about the pages. And so I'd like you to try this on your own. I think you're up for it. It's the exact same steps that we did for find subject id, but we're working with pages instead.
Give it a shot, and in the next movie, I'll walk you through the solution that I came up with.
Find answers to the most frequently asked questions about PHP with MySQL Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.