Introducing PDO Database Access 0:00Okay, so this episode is where we finally get to do some fun stuff, where we actually fetch information from the database and display it on the page. So two episodes ago, you learned about MySQL queries, or at least the basics of it. And then, of course, in the last episode, you learned Classes 101. That'll get you set up for this episode. So why don't we dig in? To interact with our database, we're going to use an instance of the PDO class. And that stands for PHP Data Objects. Kind of fancy, kind of scary. Don't worry about it too much. Building the DSN Connection 0:28Kind of fancy, kind of scary. Don't worry about it too much. Just accept that it offers an interface to connect to your database. And lots of databases, in fact. Now, here is the scary part, though. Through the constructor, we're going to provide what we call the DSN. And it's basically a connection string. So it's where you declare, well, what kind of database are you using? Is it SQLite? Is it MySQL?Is it SQLite? Is it MySQL? Is it something else? Next, what host is it? Well, we talked about this already, right? It's localhost. And localhost has a port of 127.0.0.1. So we can paste that in. Next, it needs to know, OK, I've connected to MySQL, but which database do you want to use? Well, if you remember, we set the DB name equal to mytodo.Next, it needs to know, OK, I've connected to MySQL, but which database do you want to use? Well, if you remember, we set the DB name equal to mytodo. And that should be good to get started. Now, next, it wants to know, well, what is your username to MySQL and what is your password? And this part will be very important that you lock it down and you keep it secure from the outside world. But to start, we don't really have much security. So the username is root and there is no password. So this is fine for local only. Of course, for production, and by the way, production means I've deployed this to the Handling Connection Exceptions 1:35So this is fine for local only. Of course, for production, and by the way, production means I've deployed this to the world and anyone can access my website. Well, in those situations, you always want to lock it down. But you'll get there in time. All right. So we've created a new instance of PDO. However, it's possible that our connection won't work. So something will go wrong trying to connect. So how do we handle these exceptional scenarios where you thought it was going to work, butSo something will go wrong trying to connect. So how do we handle these exceptional scenarios where you thought it was going to work, but something kind of exceptional happens? PHP didn't know how to respond or the library didn't know how to respond. Well, those are called exceptions. And exceptions can be thrown exactly in the way I said. What if, for whatever reason, MySQL wasn't installed on our machine? Well, that means this class, it can't connect to it. So that's an exceptional scenario. So it's going to throw an exception.So that's an exceptional scenario. So it's going to throw an exception. Now, in PHP, we can catch exceptions using this bit of logic here. Try to run this bit of code, but catch any kind of exception. So we could catch an exception or a specific type of exception. And you'll learn more about exceptions in the future. It's a little more advanced than where you currently are, but that's OK. Luckily, this reads pretty well. Try to connect to PDO, but catch any kind of exceptional scenario. And I want to handle it right here.Try to connect to PDO, but catch any kind of exceptional scenario. And I want to handle it right here. So, for example, we could die and just say could not connect. Now, remember, die halts the execution. So this is our way of saying we couldn't connect to our database. We can't do anything here. Let's just give up and die. So why don't we try this out? Oh, by the way, of course, the view should be at the bottom. Anyways, but I do want to test that we catch the exception.Oh, by the way, of course, the view should be at the bottom. Anyways, but I do want to test that we catch the exception. So let's use a database name that doesn't exist. And that should throw an exception, right? It is an exceptional scenario that you're trying to connect to a database that doesn't even exist. So we should catch this exceptional scenario, and in this case, halt execution. Anyways, if we run it, sure enough, could not connect. So this is working. And as a general rule of thumb, when you manually connect to PDO, you always want to wrap it in a try catch.And as a general rule of thumb, when you manually connect to PDO, you always want to wrap it in a try catch. Now, what you'll find, though, is that in real life, a lot of this stuff is done automatically for you behind the scenes using a CMS or using a framework. But you know what? Just like learning how to balance your checkbook or how to do long division, even though you probably use a calculator rather than manually doing it, it's still valuable for you to understand the basic process. So that's why we're still going over this. Okay. Preparing and Executing Queries 4:11So that's why we're still going over this. Okay. So if we fix the database name, we get no exception. So we're good here. Now that we've connected to PDO, we're able to prepare an SQL query. We do that like this. Prepare the query. And remember when we talked about selecting from the database? I said we can select star, which means everything, from a table called TODOS. But now, yeah, if we run this, it doesn't do anything.I said we can select star, which means everything, from a table called TODOS. But now, yeah, if we run this, it doesn't do anything. So we've prepared the statement, but we haven't yet executed it. So you can prepare to go for a drive in the car, but you haven't done it yet, right? So we need to execute the statement. We do it like this. Statement. Execute. Finally, I'm going to VARDUMP statement. And then let's fetch all of the results, and then we'll talk about this a bit more.Finally, I'm going to VARDUMP statement. And then let's fetch all of the results, and then we'll talk about this a bit more. Refresh. And there we go. It works. Let me turn on better highlighting. There we go. So we fetched exactly two rows from the database. And sure enough, if we connect, there's our two rows. Let's add another one.And sure enough, if we connect, there's our two rows. Let's add another one. Clean house. And if I come back now, we have three rows. Pretty fun, right? Now you'll notice, though, that we have a lot of duplicates here. So notice ID, but then index, and then description, but then index, and then completed. So what it's doing here is it's returning the values in both forms. It's returning them as an associative array, but also a regular indexed array. Now here's what we can do. Fetching Results as Objects 5:41It's returning them as an associative array, but also a regular indexed array. Now here's what we can do. We can override how we fetch these results. And we can do that like this. PDO. And let's say fetch object. So now we're saying, how do we want to fetch these results? Well, right here, it's kind of doing both. It's giving us an associative array and an indexed array. But now, because we were explicit that we just want to fetch the results into an object,It's giving us an associative array and an indexed array. But now, because we were explicit that we just want to fetch the results into an object, and you've learned about objects now, right? So this is an instance of just kind of a generic class. It's not a named class. It's just a generic STD class or standard class. Anyways, now we can access these values here. So let's try this. We could say something like this. Fetch all of the results and save it into this variable.We could say something like this. Fetch all of the results and save it into this variable. And then I could var dump results. And notice it's an array. So why don't we grab this description? OK. Give me the first item and then give me the description. Notice this arrow syntax. This is all what we covered in the last episode. So now we should get go to the store.This is all what we covered in the last episode. So now we should get go to the store. And you are successfully interacting with your database. Now, a quick word of caution. If very likely you're learning PHP and you're also learning it from other blogs, that's fine. But a lot of the tutorials are pretty old. So you may see tutorials that show you things like this, MySQL Connect. These functions, they're deprecated. And you don't want to use them. They're not overly flexible.And you don't want to use them. They're not overly flexible. And they can also allow for SQL injection if you're not careful. So the recommended approach, the best practice is to always use PDO to connect to your database. So let's review this once again. Try to connect to our database using this connection string. But catch any exception that might be thrown. And by the way, you can call e getMessage. So remember, this e variable, that is an object. It is an instance of this class.So remember, this e variable, that is an object. It is an instance of this class. And one of the methods, just like task complete from the last episode, one of the methods you can call on it is getMessage. And that will return to you just a description of what went wrong. So if we try to connect to a database that doesn't exist, the message will be unknown database. And it gives you a bit more information. OK. Anyways, continue on.OK. Anyways, continue on. We will prepare the following SQL query. And then we will execute that prepared statement. And that's how we refer to this, prepared statements. Now that we've executed the statement, we want to fetch everything, all of the results in memory. So generally, you want to be a little careful about this. For our example, it makes no difference whatsoever. But imagine that the results of this query returned 50,000 rows.For our example, it makes no difference whatsoever. But imagine that the results of this query returned 50,000 rows. Well, that is a huge number of rows to load up into memory. So that's why you might want to use an alternative approach, maybe just a single call to fetch. But we'll get to all of that stuff as we keep reviewing PDL. For now, fetching everything into memory and then storing each column or each row into an object is a nice way to go. So that means let's do this. We have our results.So that means let's do this. We have our results. Why don't we give a better name? I don't really like results. What exactly did we fetch? We fetched to-dos, or I've been calling them tasks. OK. So now in our view, I can bring back what we had before. This is what we wrote in the last episode. Filter through our array of tasks.This is what we wrote in the last episode. Filter through our array of tasks. And if the task is completed, wrap it within a strike tag. Otherwise, just echo out the description here. And if I give it a refresh, we have exactly what we had in the last episode. But now we're working with a database. Let's do another one. Contribute to open source project. All right. Now we'll get four results.All right. Now we'll get four results. Pretty fun. Let's make this completed. And now we should have a strike. And we do. But now, what about the last episode where we had that task class? Right now, we are just fetching everything into, like I said, just kind of a dummy generic object. But if you want to use your task class, here's what we can do. Mapping Rows to Task Class 9:51just kind of a dummy generic object. But if you want to use your task class, here's what we can do. We will fetch the results into a class called task. So now if we run it, it's going to fail, right? Because you don't have a class called task. Okay. Let's do this now. We could say class task. But you notice how things start to get muddy very quickly? A very huge part of programming is cleanlinessBut you notice how things start to get muddy very quickly? A very huge part of programming is cleanliness and finding a good place and a good spot to store each bit of logic. That is a huge component to writing nice, clean code. So a first step might be to simply require it. Require task.php. And then I will create it. Okay. So let's say class task. And we're going to have, like I said, a public descriptionSo let's say class task. And we're going to have, like I said, a public description and a public completed field. Okay. So back to index.php. We've required our task. We've performed an SQL query. We saved it or fetched it into a task class, which means if we give this a refresh, we get what we had before. But notice now if I go back to task, any methods we have here can be called.which means if we give this a refresh, we get what we had before. But notice now if I go back to task, any methods we have here can be called. So I could say foobar and then say return foobar. You know, just any kind of dummy method. Well, now each of the tasks that are returned in this array can call that method. Tasks, first one, and then foobar. And let's var dump the result. Give it a refresh, and we get foobar. So I'm just showing you that you've saved everything into this task class so you can begin adding any methods or behavior you want into it.So I'm just showing you that you've saved everything into this task class so you can begin adding any methods or behavior you want into it. Okay. So I can get rid of all of that, and this is starting to look pretty good. But now one thing that kind of stinks is this isn't fun to work with. Now, I'll let you know that coding is actually a lot more fun than this. This is really annoying stuff. So in real life, you'll use what we call abstractions or existing libraries that will make this process a lot easier. Because if you think about it, I've been talking a lot about descriptive codethat will make this process a lot easier. Because if you think about it, I've been talking a lot about descriptive code and making your code reflect how you would speak it. So how would we speak this? Well, we're trying to fetch all tasks. But, hmm, I don't know. It doesn't really say that, does it? It'd be cool if we could just say task, give me all of them, right? Or task all. You know, that's really what you want at the end of the day.Or task all. You know, that's really what you want at the end of the day. But right now when you're learning, you're having to do a bunch of this stuff. So just know, once you get a little more advanced, you will learn about ways to not have to write any of this junk and instead leverage tooling that will make the process of interacting with your database a million times easier. Okay? In the meantime, though, we could do a couple things. For example, we could isolate the process of connecting to your database Refactoring into Helper Functions 12:35In the meantime, though, we could do a couple things. For example, we could isolate the process of connecting to your database behind a function, like connect to db, maybe something like that, and paste this in there. Then we could return the PDO instance and do something along those lines. So why don't we grab this, put it into functions.php, like so. Now, if we come back and give this a refresh, everything's going to fail, right? So let's fix it. We will require our functions, and then I will say PDO equals connect to db. So now we're starting to find a few more practical uses.We will require our functions, and then I will say PDO equals connect to db. So now we're starting to find a few more practical uses. So function dd, okay, that's fine. But in real life, you will isolate reusable code behind a function or a class method. So this function's responsibility is to connect to the database. So we perform our logic and we return the result. That way, the result can be saved into this variable. So I think if I give this a refresh, we should be back where we were. But now we've kind of isolated some of that code and made it more reusable. Now, if you want, we could even take some of this stuff and do the exact same thing.But now we've kind of isolated some of that code and made it more reusable. Now, if you want, we could even take some of this stuff and do the exact same thing. For example, we could say fetch all tasks. Maybe that's the name of your function. Now, once again, I will tell you that there are some cleaner ways to go about all of this. But creating your first functions in this way, I think, is great. It's a good way to learn. So that might consist of some of this. This function will accept an instance of our database connection. And then down here, we will return the results.This function will accept an instance of our database connection. And then down here, we will return the results. Okay, so once again, we could grab that and put it here. If I come back now, we could say fetch all tasks and give it our PDO instance and save that to tasks. Now the function will be called. We will prepare a statement. We will execute the prepared statement and then return the results by fetching each row from the database or from the result set into a class called task. So if I come back and give this a refresh, once again, we get the exact same thing.each row from the database or from the result set into a class called task. So if I come back and give this a refresh, once again, we get the exact same thing. But now you've isolated a bit more. And you've cleaned up your code. Now, once again, we can clean this up quite a bit more. In reality, creating functions for this sort of thing probably isn't the greatest idea in the world. But once again, for your current stage of learning, this is entirely fine and you're making a lot of good progress. Anyways, let's close some of this stuff out.making a lot of good progress. Anyways, let's close some of this stuff out. And I think we'll leave it at that. So in the next episode, we'll keep digging into PDO and figuring out how all these little pieces fit together.
