Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
We've learned how to connect to MySQL using PHP and how to access the results that it returns. In this movie we're going to look a little closer at some of the options we have for working with those results sets. There are four ways to retrieve data from the query result, and the first of these we've already seen, it's mysqli_fetch_row. And it brings back a row of data and assigns it to a standard array. The keys for each one of those columns are going to be integers. So if we want to get the menu name, we ask for what's in column one. That's the index position in the array one.
We have another option though. We can use mysqli_fetch_assoc in its place, use it exactly the same way. The only difference is that the results are going to be returned in an associative array. That's nice because now the keys are going to be the column names. If we want to get menu name for our subject, well then we ask for the key menu name. It's nice and easy, it is a touch slower, because it does have to make an extra query to MySQL to find out the column names. So it can use them when it's constructing that associative array.
But you won't notice a speed difference. Then we have a third option, which is mysqli_fetch_array. Again, used in exactly the same way, but this time, the results are returned in either a standard array, or an associative array, or both. Which is essentially an associative array that indexes it both by integers and by the column names. Now by default, it's going to do both, that's configurable. The last argument that you pass in can be a constant that will tell it either to return a number index, an associative array with the column index or both. By default it's going to do both, which is going to make your data set and your memory a lot larger.
So I think that the best one to use, the one that's easiest and most convenient is the ASSOC version. If you want it indexed by number, or if you want the one that's just absolutely as fast as possible, you'll want to use fetch row. Mysqli_fetch_array, you should really only use if you need the convenience of being able to switch back and forth between the integer version. And the column name version, and you don't mind the extra memory that it takes up and the extra speed. Now, when I say that it's slower, I mean, you won't really notice a difference until you're doing something like 100,000 queries per second.
Like, you really aren't going to notice the difference until you start doing a lot and lot of queries. But once you get to that point, it does make sense to optimize and pick the one that's fastest and still meets your needs. Now, I said there were 4, there's a fourth one, which is mysqli_fetch_object. And that's going to be when we're working with object oriented programming. We can have it fetch back the data and go ahead and populate an object with it. That's going to be even slower but its going to really depend on the size of your object as to how slow it is. We're not going to be using that one but let's take a look at the other three. So let's just open back up our databases.php file. And you can see we were doing mysqli_fetch_row first. Go back and just remind ourselves what that looks like, we'll hit Reload. You can see that all of these are indexed by an integer, 0, 1, 2, 3. Now let's try the other version, we simply change this to be assoc, Save it, come back and reload the page.
Look at that, now the index is id, menu name, position, and visible. Much easier to access it that way. And then last of all, let's just try array, there we go. We'll reload it, and you'll see that we actually get an associative array that has both. See, everything is repeated twice, so we have it both indexed by the number 1 and by menu name, so you can see why this takes up more memory. And also takes more time for it to construct. Now they said, you can use mysql_fetch_array and just pass in another argument here, MYSQL_ASSOC. And now we come back over here and reload it and it basically does the same thing as the assoc function does.
It has the same behavior. So you can do it one way or the other. I tend to just stick with assoc and that's what we're doing for the rest of the string. We'll be doing it that way so that we can refer to it by it's column name. Let's try that here, instead of just dumping each one out, let's actually modify this. Take this out, and let's echo back row, it's a associated array, so then we can just ask it for it's id. And then I'll just put a br tag at the end and then let's just Copy that line. I'm going to paste it, there we go, a few more times. And then, I can just change it so that I'm asking for each one of those keys. Menu name, position, and visible.
But let's just go back over here and reload our page, and there we go. Now we're seeing our actual values, we're not just doing a var dump anymore. We're accessing the values in the associative array that it returned, that it assigned to a row. It's just a regular PHP associative array at this point and we're accessing it by using these keys. Now this still is probably a little rough from what you would want from an actual use case. Let's try something a little different, let's put in ul tags here at the top and bottom, ul tags and for each one of these items here, let's stop our PHP for a second.
Let's put in an li tag, and then let's just grab this echo menu name and let's put that here inside php tags with a semicolon after it. We'll get rid of all of this except that we need to turn back on our php tags here. Okay, see how that works? I'm breaking my loop, I'm stopping doing php briefly here, this is a php block. Then I stop in the middle of the loop, do some html, switch back into php, and then I do need to switch back into php just to get that closing brace in for my loop. Now I'm going to have an unordered list that outputs. So let's try this.
Let's just go back real quick, reload the page, and there we go. That's the kind of thing that you'd potentially want to use on your website. And that's exactly what we're going to be doing a little later. The one last thing I want to mention is that I just called it row. It's just generic that it's a row and you can certainly use that, but I think it's even better to assign it something that's useful. So if you're working with a customer, you're using a customer table, well then call it customer. If you're bringing back orders, call it order. So in this case, we're working with subjects, so I'm going to say that this is a subject. So there we go, it's a little cleaner. So while I'm getting back a subject, the subject's menu name is what I want to display.
Think that makes it nice and easy, and it's a lot easier to read the code and understand what's going on than using something just generic like row. So now that we've seen how to read data from the database, let's look at the other parts of CRUD, creating, updating, and deleting.
Get unlimited access to all courses for just $25/month.Become a member