‫All right guys.
‫So in this video we're going to create our query method.
‫But there's a couple things I want to do before that.
‫A couple of things to clean up.
‫And one in this database class we created the options array.
‫But I didn't do anything with it.
‫So that actually needs to be passed in to the PDO instance.
‫So this should take in the DSN the username the password and then the options array.
‫And then another thing I want to do is is add the fetch mode.
‫I want to fetch an associative array.
‫And I don't want to have the numeric indexes.
‫So we need to add into this options array PDO.
‫And then we want adder underscore default fetch mode.
‫And then we want to set that.
‫Two PTO, double colon and then fetch assoc.
‫For now, I'm actually going to show you how to fetch as an object later, but we'll do that for now.
‫So save that.
‫Yeah.
‫Just make sure that options is actually being used.
‫And then in the index.php I want to change up a couple of things.
‫This is public index.php.
‫So one the bringing in the config and instantiating the database.
‫We're going to do that in the specific files where we use the database.
‫So we can get rid of those.
‫And then I'm just going to move the router up here.
‫Just have all the requires together.
‫We do need to have this require down here because we need it after the router instantiation.
‫Because we use this router variable in that routes file.
‫And then here we're just let's just add some comments here.
‫So just to make it really clear.
‫So we're instantiating.
‫Instantiating the router.
‫And then we're getting, we'll say, get roots.
‫Say, instantiate the router, get roots.
‫And then here we're get we're going to get the current Uri and Http method.
‫And then here we pass those in so that we can route the request.
‫All right.
‫So that should be pretty clear as to what's going on here.
‫So one other thing.
‫It's not a big deal, but in router.
‫This register route.
‫I didn't add a comment before it with the params and stuff.
‫It's just something that I like to do.
‫I mean, it's not a big deal, but I'm just going to add that.
‫So this is going to be let's say add a new route.
‫And the PHP doc extension did this for me.
‫So it looked at the, you know, the the method Uri controller.
‫And the reason it did it here is because I already had the function created.
‫Usually I put this before it so it doesn't yet know what params are there, but that's why it did it
‫this time.
‫So if you want to do this after so that it automatically collects those params for you, you can.
‫All right.
‫That's all I wanted to do there.
‫Now let's go to our database class, and let's add the query method so that we can query the database.
‫So we'll go under the constructor.
‫And I'm going to add in.
‫My little comment here will say query the database.
‫And as far as a pram, it's going to take in a string and it's going to be the SQL string or the query
‫string, and we'll call it query, or you can call it SQL.
‫And then as far as what it returns, this isn't going to return like the listings or users or anything
‫like that, because we want to use it on with any query we want to make, whether that's fetching the
‫listings, fetching the users, adding a, adding a listing, whatever it might be.
‫So we're actually going to return the statement.
‫So let's say PDO statement is what it's going to return.
‫And it could possibly throw an exception.
‫So let's say throws PDO.
‫Exception.
‫And.
‫Yeah, so that should do it.
‫Now let's say public function query.
‫And it's going to take in.
‫The actual query.
‫Okay.
‫And we're going to it's the same thing we did back in the database section where we have a prepared
‫statement.
‫Then we call execute and then we call fetch or fetch.
‫All except this time we're doing it in an object oriented way, and we're returning the statement so
‫that we can call fetch or fetch all later on when we actually use this.
‫So let's put this in a try catch first.
‫Because it will.
‫We want it to throw an exception with an error.
‫So I'll say catch and here we'll say PDO exception.
‫Call this E.
‫Okay.
‫And then in the try we're going to create our statement.
‫So I'm just going to use star as a variable for statement.
‫And we want to take our connection okay.
‫Because that's the PDO instance.
‫And that's where we want to call prepare from.
‫I think in the, in the, in the video where we did this before, it was procedural.
‫So we just had like I think we set a variable called PDO and then we called prepare from that.
‫But now we're using the connection property up here.
‫Okay.
‫It's it's defined here.
‫And then we put in the PDO instance right here.
‫So now we can call prepare on that that property.
‫So prepare.
‫Then we pass in our query.
‫A query and then we want to execute it.
‫So call execute.
‫And then we just simply want to return.
‫Stage the statement.
‫All right.
‫And then down here we'll throw a new exception.
‫And we'll just put a message in here that says query failed to.
‫Execute.
‫And then I'll just put the variable, the E object.
‫Close that up.
‫That E object has a method called get message, just like we did in the in the connection.
‫All right.
‫And that should do it.
‫And we'll change this up a little bit because we're going to need to have placeholders if we want to
‫bring in any user input or anything like that.
‫But for now to just select all the listings, this should work.
‫So let's go into where we want to use this, which is going to be in our home controller.
‫And essentially we want to fetch the listings and then pass it into the view.
‫But that part where we pass it into the view, we're going to do that in the next video, because we
‫have to modify load view to be able to do that, but we can at least fetch it now and show it, you
‫know, inspect it.
‫So first off let's get our config for the database.
‫So let's say require.
‫Oops.
‫I don't know why I keep using that syntax.
‫So base path.
‫And we'll pass in here config slash db dot php.
‫And then we want to instantiate our database.
‫So I'll call this variable db.
‫You can call it something else if you want.
‫So new database.
‫Okay.
‫And we want to pass in that config.
‫Now we can use that query method.
‫So let's create a variable called listings and then use the database object.
‫Call the query method that we just created and pass in a query which is going to be select all from
‫listings.
‫And since it's the home page I want to limit it to six.
‫So I can actually just say limit six as part of the query.
‫Now here is where we call either fetch or fetch all whatever we're doing here.
‫We're getting all the listings.
‫So we're going to call fetch all.
‫All right.
‫And then for now we'll just go ahead and inspect.
‫The listings.
‫All right, so I'll reload.
‫And now.
‫You can see we have an array here.
‫We have the ID which is one, the user ID one, the title software engineer description salary.
‫So we have everything here for the listings.
‫Here's the second one.
‫Marketing specialist web developer, data analyst graphic designer.
‫So we know we're getting the data.
‫So now in the next video we want to take this data and we want to be able to pass it in.
‫Like, we want to be able to pass in an array and pass in listings or whatever we want to that view,
‫so that we can then load it down here.
‫All right.
‫But I'll get rid of that inspect.
‫Now that we know that it's working and we're fetching the data and let's move on to the next one.