Okay, so to be honest, there is so much we have to cover. We need to talk about prepared statements, and SQL injection, and different ways that we can configure the DSN. But before we get there, let's just take a few moments and refactor this into something that's a little more friendly to use. Okay, so here's what I'm thinking. If I were to, at the top, make a little note about what we're trying to do here, I could do that with //, and now I can enter any text, and this won't be interpreted as PHP. It's just a comment.

as php. It's just a comment. Anyways, if I were to write this out, I sort of want to connect to the database and execute a query. Okay, so why don't we convert this to a class? And we already learned a little bit about classes in the last episode, but we'll learn a little more in this video. We learned that we can create a class like so, and I did say connect to the database, and at least initially, it's helpful to follow the nouns. So why don't we start with that, class Database.

and at least initially, it's helpful to follow the nouns. So why don't we start with that, class Database. All right, next up, we have and execute a query. Yeah, in this example, execute and query would both be pretty good method names because they can both be treated as verbs. And remember, we learned that methods are the verbs of the programming world. So in this case, let's just pick one. Do we want to call execute or query? And this is where the design aspect comes into play. If I'm working with a Database class and I want to trigger a SQL query, do I want to

And this is where the design aspect comes into play. If I'm working with a Database class and I want to trigger a SQL query, do I want to call execute or do I want to call query? In this case, I think I'll go with query, public function query. And yeah, remember, whenever we define a function within a class, technically, we would call it a method. And don't forget to add the visibility. But yeah, right now, we're making everything public because we haven't yet dug into that. All right, so this is looking pretty good to start. The next step is to create a new instance of the Database class.

All right, so this is looking pretty good to start. The next step is to create a new instance of the Database class. So I will begin by creating a variable, db is very common, equals a new instance of the Database class. Okay, so now I can call this query method by saying db->query. And yeah, you'll know you did it right because if you're using a good IDE, it will give you some intellisense. And you can see, yep, you can call this method. Okay, so now how would we go about refactoring all of this into our new Database class? And you know what, we're going to do it one refactor at a time.

Okay, so now how would we go about refactoring all of this into our new Database class? And you know what, we're going to do it one refactor at a time. So we will begin by selecting everything, cutting it, and pasting it into our query method. Okay, so now when I call this query method, yeah, it builds up the DSN, it initializes PDO, it prepares a hard-coded query, which doesn't really make sense. What if I want to run a different query? Well, we'll get there. Then we execute it, and then we fetch the results, and we save it to a variable called posts.

Then we execute it, and then we fetch the results, and we save it to a variable called posts. But yeah, if we wanted, we could return that. And now we should have an array of posts if we did everything correctly. So let's try this out. $posts equals DB::query(), come back to Firefox, give it a refresh, and yeah, it still works. So it's not very flexible because right now I have a Database class with a query method, and all that query method does is it selects everything from the post table, which of course you don't want. So yeah, that would be the next step.

course you don't want. So yeah, that would be the next step. Let's make this query dynamic by turning it into a variable. And we can accept that when we call the method. Okay, so now if I scroll down, you'll see squigglies here because we are requiring that you pass us a query. So I will paste that in now. And yeah, notice I've moved it out of the method and made it just a little more flexible. So notice I can tweak this if I need to. For example, select * from posts where id > 1.

So notice I can tweak this if I need to. For example, select * from posts where id > 1. And in this case, I think I only have one record. So if I come back, yeah, notice I have complete control. If I had a different table, well, I could say select * from users. Again, I've made it that much more dynamic. But now if we scroll up, it works, but it's a little unfortunate because as you can imagine in a real life application, you could potentially execute hundreds of queries as the user browses around your site. And it's a shame that every time you call this query method, we basically start over.

And as it turns out, when we initialize a new instance of a class, there is a way to say, okay, right when that instance is created, I want you to do something. And here's how we do that in php. We create a function, and it's very awkwardly named. It's __construct. So when an instance is constructed, that's how I think of it. But yeah, remember two underscores and then construct. So yeah, if I wanted to, I could say, hi there, just to prove to you that we are triggering this method automatically. So if I come back to Firefox and give it a refresh, sure enough, that function or that

this method automatically. So if I come back to Firefox and give it a refresh, sure enough, that function or that method has been called by php automatically. Okay, so it sounds like that's the perfect place to initialize our PDO instance. So I will grab this and move it up here. Okay, but now notice in our query method, we have an issue. We expected to have access to that PDO variable, but it's in a different scope now. We moved it out of this method. Okay, so here's what we can do. Right up in the construct, let's define a instance property.

Okay, so here's what we can do. Right up in the construct, let's define an instance property. And again, right now we're making everything public, but later I'll teach you when it would make sense to not make things public. All right, and what should we call this? Well, we could do PDO like we have here, but really it's sort of our database connection. That's how I think of it. So why don't we call it connection? Okay, so now I have an instance property on our class called connection. Now I want to write to it.

Okay, so now I have an instance property on our class called connection. Now I want to write to it. So instead of creating this variable PDO, I will instead say $this for this instance -> connection equals new PDO. And then stylistically, I would probably put a blank line above it. Okay, so now think about it. Whenever I create a new instance of Database, well, we automatically call this construct method as we learned. Within it, we initialize a new PDO instance and we assign it to this connection instance property.

Within it, we initialize a new PDO instance and we assign it to this connection instance property. And yeah, by the way, that's generally the term we use, property. Okay, and what's cool about that is anywhere else in this class, I can access that property. So now if I want to use my connection, I can replace this with this->connection. And on the connection, we will call prepare. So what we have here should work just fine. Let's give it a shot. Come back to Firefox, give it a refresh. And yeah, I once again get all of the posts that have an ID greater than one.

Come back to Firefox, give it a refresh. And yeah, I once again get all of the posts that have an ID greater than one. Let's remove that and try it again. And now we have all of the posts in the database, which is only two. Cool. Okay, so what we have here actually looks fairly good. But we have one issue right here. I don't love this. And here's why. What if I were instead trying to fetch a single post?

And here's why. What if I were instead trying to fetch a single Post? Well, I might say, select * from posts where id = 1. And I will just dd that Post. Well, it'll work if I come back and give it a refresh. But notice it's effectively returning a collection, a list. So I have an array of arrays, which means I would have to do something like this. Give me the first item from that array, and that would give me this. And that too is an array. So I would then say, then give me the title.

And that too is an array. So I would then say, then give me the title. And that works. But notice it's kind of weird. I would expect a single array that has all of the attributes of my Post. But because we called fetchAll, that's going to return to us a list. So the solution is to not call fetchAll, but instead just fetch. Give me one record. Come back and refresh. Yeah, so now notice I have a single level array here.

Come back and refresh. Yeah, so now notice I have a single level array here. And I can once again do things like postTitle, and that'll work. Okay, so once again, we're seeing that whether we call fetch or fetchAll or maybe something else, well, that needs to be dynamic. So instead, why don't we just return the statement itself? And yeah, at least for now, I can be in charge down here of whether we call fetch or fetchAll. So in this case, I want a single record, so I could call fetch, and then once again, PDO::FETCH_ASSOC.

So in this case, I want a single record, so I could call fetch, and then once again, PDO, fetch association. And notice this right here is kind of annoying. It would be nice if we didn't have to manually write that every single time we fetch the results. And trust me, you can. We'll do that in the next video. So if I come back and refresh, there we go. And yeah, if we later decide, no, I actually wanted all of the posts, then I could switch over to fetchAll.

And yeah, if we later decide, no, I actually wanted all of the posts, then I could switch over to fetchAll. And oh, yeah, of course, we have to update this. Now I have all of the posts, which is an array of arrays. And sure enough, that works. All right, and I will wrap up this lesson by creating a new database.php file. And I will grab all of this here and move it. And we'll paste it right here. Okay, cool. So now, if I switch back, we can require database.php, and actually notice that I made that D capital.

Okay, cool. So now, if I switch back, we can require database.php, and actually notice that I made that D capital. And that's because whenever you have a php file that only contains a class, and you're going to do this all the time in the future, by the way, again, general conventions state that it should be capital. Okay, so granted, we still have so much more work to do. But already things are starting to look, and more importantly, feel pretty good, I think. So yeah, stay tuned for the next episode, and we'll keep chipping away at it.