

Finding a subject in the database

From: PHP with MySQL Essential Training

Video: Finding a subject in the database

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.

Finding a subject in the database


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.

Show transcript

This video is part of

Image for PHP with MySQL Essential Training
PHP with MySQL Essential Training

131 video lessons · 39362 viewers

Kevin Skoglund

Expand all | Collapse all
  1. 4m 8s
    1. Welcome
      1m 0s
    2. Using the exercise files
      3m 8s
  2. 15m 6s
    1. What is PHP?
      3m 52s
    2. The history of PHP
      2m 51s
    3. Why choose PHP?
      4m 10s
    4. Installation overview
      4m 13s
  3. 54m 53s
    1. Overview
      2m 33s
    2. Working with Apache Web Server
      6m 56s
    3. Changing the document root
      7m 24s
    4. Enabling PHP
      6m 16s
    5. Upgrading PHP
      3m 30s
    6. Configuring PHP
      10m 3s
    7. Installing MySQL
      5m 46s
    8. Configuring MySQL
      7m 24s
    9. Text editor
      5m 1s
  4. 31m 25s
    1. Overview
      3m 27s
    2. Installing WampServer
      5m 46s
    3. Finding the document root
      2m 24s
    4. Configuring PHP
      8m 12s
    5. Configuring MySQL
      5m 45s
    6. Text editor
      5m 51s
  5. 19m 12s
    1. Embedding PHP code on a page
      6m 43s
    2. Outputting dynamic text
      5m 55s
    3. The operational trail
      2m 27s
    4. Inserting code comments
      4m 7s
  6. 1h 18m
    1. Variables
      7m 50s
    2. Strings
      4m 38s
    3. String functions
      8m 54s
    4. Numbers part one: Integers
      6m 27s
    5. Numbers part two: Floating points
      5m 25s
    6. Arrays
      10m 0s
    7. Associative arrays
      6m 37s
    8. Array functions
      6m 33s
    9. Booleans
      3m 50s
    10. NULL and empty
      5m 15s
    11. Type juggling and casting
      8m 27s
    12. Constants
      4m 43s
  7. 27m 37s
    1. If statements
      6m 0s
    2. Else and elseif statements
      4m 16s
    3. Logical operators
      7m 30s
    4. Switch statements
      9m 51s
  8. 42m 15s
    1. While loops
      8m 41s
    2. For loops
      5m 59s
    3. Foreach loops
      8m 16s
    4. Continue
      8m 28s
    5. Break
      4m 8s
    6. Understanding array pointers
      6m 43s
  9. 37m 25s
    1. Defining functions
      8m 25s
    2. Function arguments
      5m 32s
    3. Returning values from a function
      7m 33s
    4. Multiple return values
      4m 53s
    5. Scope and global variables
      6m 2s
    6. Setting default argument values
      5m 0s
  10. 20m 18s
    1. Common problems
      3m 47s
    2. Warnings and errors
      8m 36s
    3. Debugging and troubleshooting
      7m 55s
  11. 57m 57s
    1. Links and URLs
      5m 33s
    2. Using GET values
      5m 35s
    3. Encoding GET values
      8m 41s
    4. Encoding for HTML
      9m 26s
    5. Including and requiring files
      7m 40s
    6. Modifying headers
      6m 45s
    7. Page redirection
      6m 43s
    8. Output buffering
      7m 34s
  12. 1h 3m
    1. Building forms
      7m 28s
    2. Detecting form submissions
      5m 59s
    3. Single-page form processing
      7m 57s
    4. Validating form values
      10m 40s
    5. Problems with validation logic
      9m 54s
    6. Displaying validation errors
      7m 23s
    7. Custom validation functions
      6m 28s
    8. Single-page form with validations
      7m 25s
  13. 28m 5s
    1. Working with cookies
      2m 49s
    2. Setting cookie values
      5m 55s
    3. Reading cookie values
      6m 1s
    4. Unsetting cookie values
      4m 51s
    5. Working with sessions
      8m 29s
  14. 48m 39s
    1. MySQL introduction
      6m 43s
    2. Creating a database
      7m 41s
    3. Creating a database table
      7m 42s
    4. CRUD in MySQL
      5m 48s
    5. Populating a MySQL database
      7m 32s
    6. Relational database tables
      6m 40s
    7. Populating the relational table
      6m 33s
  15. 56m 4s
    1. Database APIs in PHP
      4m 51s
    2. Connecting to MySQL with PHP
      7m 45s
    3. Retrieving data from MySQL
      8m 47s
    4. Working with retrieved data
      6m 12s
    5. Creating records with PHP
      6m 58s
    6. Updating and deleting records with PHP
      9m 6s
    7. SQL injection
      3m 5s
    8. Escaping strings for MySQL
      6m 45s
    9. Introducing prepared statements
      2m 35s
  16. 35m 58s
    1. Blueprinting the application
      7m 19s
    2. Building the CMS database
      5m 14s
    3. Establishing your work area
      4m 38s
    4. Creating and styling the first page
      4m 22s
    5. Making page assets reusable
      6m 36s
    6. Connecting the application to the database
      7m 49s
  17. 32m 49s
    1. Adding pages to the navigation subjects
      5m 58s
    2. Refactoring the navigation
      6m 7s
    3. Selecting pages from the navigation
      6m 2s
    4. Highlighting the current page
      5m 26s
    5. Moving the navigation to a function
      9m 16s
  18. 1h 45m
    1. Finding a subject in the database
      9m 48s
    2. Refactoring the page selection
      10m 52s
    3. Creating a new subject form
      6m 55s
    4. Processing form values and adding subjects
      11m 20s
    5. Passing data in the session
      9m 16s
    6. Validating form values
      9m 40s
    7. Creating an edit subject form
      8m 30s
    8. Using single-page submission
      7m 44s
    9. Deleting a subject
      9m 44s
    10. Cleaning up
      10m 37s
    11. Assignment: Pages CRUD
      4m 30s
    12. Assignment results: Pages CRUD
      6m 10s
  19. 39m 26s
    1. The public appearance
      8m 52s
    2. Using a context for conditional code
      11m 37s
    3. Adding a default subject behavior
      6m 9s
    4. The public content area
      5m 51s
    5. Protecting page visibility
      6m 57s
  20. 1h 3m
    1. User authentication overview
      4m 3s
    2. Admin CRUD
      8m 41s
    3. Encrypting passwords
      7m 26s
    4. Salting passwords
      5m 42s
    5. Adding password encryption to CMS
      11m 54s
    6. New PHP password functions
      3m 13s
    7. Creating a login system
      11m 28s
    8. Checking for authorization
      5m 48s
    9. Creating a logout page
      5m 40s
  21. 2m 4s
    1. Next steps
      2m 4s

Start learning today


Become a member
Share a link to this course

What are exercise files?


Can I take this course without the exercise files?


Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.


Exercise files

Exercise files video

How to use exercise files.





Mark all as unwatched Cancel


You have completed PHP with MySQL Essential Training.


Become a member to add this course to a playlist


Get started

Already a member ?

Become a member to like this course.


Get started

Already a member?

Exercise files


Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.


Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched


Control your viewing experience


Interactive transcripts


Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.



Thanks for signing up.

We’ll send you a confirmation email shortly.






submit Lightbox submit clicked
Terms and conditions of use

Review and accept our updated terms of service.