This episode is for members only
Sign up to access "Build Your Own PHP Framework" right now.
Get started
Already a member? Sign in to continue
Playing
26. Setting up and querying the database
Episodes
01. Setting up
02. Container
03. Application config
04. Routing and controllers
05. Views
06. Database
07. Authentication
08. CSRF Protection
09. Flashing messages
10. Validation
11. Middleware
12. Exception handling
Transcript
00:00
The goal for this episode is to swap over the manual data that we're sending down on the homepage like this and instead swap that out for the database.
00:11
Now the first thing that we need to do is figure out how we're going to access our database. There are a ton of different ways that we can do this. We're actually going to use Eloquent or Laravel's database functionality from the Laravel framework. And this is very easy to use outside of Laravel.
00:28
So we can just borrow this, use it in our own apps. The benefit that we're going to get here is that we'll also be able to work really conveniently with models to create data if we want to update data, do pretty much whatever we want. We'll also a bit later on get to pagination, which is also going to work really nicely. OK, so how do we use this?
00:49
Well, we have a separate subtree of this module from Laravel over in illuminate slash database. Illuminate is just the sort of code name for all of Laravel's modules. So let's go ahead and pull this in now and see how we get it set up. OK, so we're going to do a composer require on illuminate and database and just wait for that to finish.
01:12
OK, so now that is pulled in, of course, we're going to head straight over to our service providers and we're going to create out a service provider for our database functionality. So let's go and again, grab the app service provider and we'll create out a new database service provider inside of here and just paste in this functionality. Change over the name to database service provider. Get rid of everything in boot here.
01:37
And of course, register this over in config and app. So let's go and pull this in to here and we are done. OK, so there's two main ways that the database module works within Laravel. The database package works.
01:50
The first thing is a lot of bootstrapping, which we're going to do inside of the boot method to set up Eloquent and all that kind of stuff. And then we're going to register this optionally on our container so we can access the base database functionality. Most of the time, you'll probably want to work with models, which we'll cover a little bit later. But also we can directly query the database with this as well.
02:12
Now, before we start to pull this into our service provider, we need to set up a bunch of config that we're going to use for our database. And of course, this is really important because this is going to change between environments as well. So inside of our database config, let's first of all set a driver for this. So we're going to use MySQL here, but Laravel's database functionality supports others like Postgres if you want to use that as well.
02:36
Now, again, we don't want this stuff to be directly within our config. We want this to be within our environment variables. So let's head over to EMV and just start to create out some of the things that we need. So let's start with the database driver and we'll set that to MySQL.
02:52
And then we can switch this over to DB driver and then that will be pulled in. Now, the reason that we do this is because we might want to have multiple drivers in order to switch this over from local to production. And we'll have a key in here specifically for our MySQL configuration. Then what you can do later if you want to is you could have a separate Postgres config option in here with all of the config for a Postgres database.
03:17
And switching over the database driver will just choose between these two. But now we'll keep things simple and we'll just have MySQL. So let's define out all of the config that Laravel's database package requires. This is the driver itself.
03:31
So we're going to obviously give that as MySQL. We don't need to specify that in our environment variable because it already exists within here. Then we're going to have a host. We're going to have the database that we actually want to connect to.
03:43
We'll fill all these in in a minute. The username to actually connect to the database, of course, that's really important, as well as the password that we need to connect. And then we have specific MySQL stuff like the character encoding. So let's just set that to UTF-8.
03:58
We have the collation as well, which we'll set to UTF-8 underscore Unicode CI. And we'll also add in an optional prefix as well, which will prefix all of your tables with a certain prefix. For us, we'll just leave that empty. So really, for just these ones, we want to create an environment variable for every single one.
04:20
So we can change these over depending on the environment. So let's go over and create these out now. So we'll have a database host, which for me is just going to be 127.0.0.1 or localhost. DB username and DB password.
04:35
And let's just double check. I think that's everything that we need. Great. So let's fill these in.
04:41
And I've just realized we also need our database as well. So DB database. And that's going to be no framework. Username for me is just going to be root.
04:50
And the password for me is going to be empty. And of course, this is going to change over in a different environment like production. So you'll just have an EMV file in your production to give a sensible username and password. Okay.
05:01
Let's go over to our database config and switch these over. So this is DB host and DB database. And the username. And finally, the password.
05:16
And we should be good. Great. So we've got all of the configuration that we need now. I've also gone ahead and created out a database called no framework with no tables in just at the moment.
05:26
And of course, we're going to see now if we can connect. So over to our database service provider, this is where we can start to register this out. So the way that we use this outside of the Laravel framework isn't just mewing up a class and passing in some config. What we want to do is boot this.
05:42
So the perfect way to do this is inside of the boot method of our service provider. Now, the first thing we want to do is grab out our config from the container so we can pass this into the capsule manager, which exists within this package. So let's go ahead and grab our container and let's grab our config out of here so we can actually start to access that.
06:04
And then the first thing that we do, I'm just going to name this how we would name this. Usually within a Laravel project within the framework, we're going to create a new manager instance. So let's create this out from illuminate database capsule manager. We create that. First of all, we need to work out what driver we're using.
06:23
So let's pull that out of our config first of all. So we'll access the config variable that we defined up here and let's say get and database driver. So that should now be MySQL. Let's just dump as we go, just so we know that we're getting everything right here.
06:41
So let's dump on the driver that we want to use. OK, let's head over and give that a refresh. And yeah, sure enough, we've got MySQL. OK, so the next step is to add a connection.
06:53
So we want to add this MySQL connection with all of the config that we have created. So we're going to use our capsule variable to add a connection. And this connection is going to contain all of the config here and then the driver that we want to use. We already know the driver, so we can just put the driver directly into here.
07:13
But for the config, we need to read this from that database config under the specific key. So we want to read this from here based on what we've set the driver to. So, again, we're going to access our config and we're going to say get database dot. But then this can change.
07:29
So we're going to concatenate on the driver, which will read from that key. OK, so now that we have done that, there's a couple of other things we need to do to set this up. We're going to use capsule boot eloquent. That means that we can use eloquent models, which we'll get to a little bit later.
07:47
And then we want to go ahead and grab the database manager and set the default connection. So we'll use capsule get database manager and we'll set the default connection to the driver that we are using like so. That just means that as we start to use everything, it will use the MySQL driver. We added the connection here, but we didn't specify what the default connection is going to be.
08:12
So this is what this line does here. OK, so we're doing this all inside of boot because we need to boot eloquent at this point. Now, what we want to do is register this as a service so we can access this inside of our controllers or anywhere else that we need. So what we're going to do is at the top here, create our property which holds this manager instance.
08:35
And then we're going to use that to bind it to our container. So let's go ahead and create out a manager type hinted capsule variable up here. And then after we have done all of this stuff, which we need to do on boot, we can go ahead and assign this. So let's say this capsule to that new property we've just created and assign it to that manager instance just inside of here.
08:58
Now, what we can do is we can register this and start to try and access the database. So we already know how to do this. We go ahead and grab our container and we add in a service. Now, what this is going to be is not a manager instance.
09:10
It's going to be a database manager. So let's pull in database manager here. That's what we want to resolve this out as. Create our closure as usual.
09:20
Go ahead and set this as set shared. And then down here, make sure we add in the database manager. Under our provide section. And now in here, all we need to do, because we've got that capsule manager instance on capsule, we just say get database manager.
09:40
That will return to us a database manager instance, and therefore we can pluck it out of our container. OK, so we're going to head over to just our home page and give this a refresh. And it looks like we didn't do anything wrong here, because remember, our service provider boot is always going to be called. But we technically should now have access to the database.
09:58
Let's go over to our database and just manually create our table with a name for each of our users. We're going to change this over a little bit later because we're going to create a specific schema for our authentication. So let's just call this users and let's go ahead and add in the column names that we want. So we'll keep an ID.
10:18
Let's go and create a new column in here called name. And this is just going to be a varchar. And I think for now, that will be just about it. OK, so let's just switch this over to varchar 255, for example.
10:31
And there we go. OK, so let's go over to the data section of this and let's create out a new record in here. So let's create Alex in here. Let's create another one out in here called Mabel.
10:42
And while we're here, we'll create another one called Tabby. OK, save that out. And we have now got three names in this user database. What we can now do over in the home controller is pull out the database manager within our home controller, make a query and hopefully get this data back.
11:00
So let's go over to our home controller. And again, we know how to pull in any dependencies that we need. So we're just going to say database manager. We'll call this database and let's go down here and swap this out.
11:14
So if you're not familiar with Eloquent or if you've never used any database functionality in Laravel before, all of this is in the Laravel documentation. So you just need to head there in order to do anything. But let's go ahead and just do a very simple query on grabbing all of the users from a table. So we're going to say this database, which is now our database manager.
11:34
We're going to specify the table that we want to pull the data from, which we've just created. That's called users. And then we just have any methods on here to either update data, get data, insert data, whatever we need to do. For now, we're just going to say get.
11:49
And that should give us all of that data back. Now, this result, if we just have a look at it, we're going to dump it directly up here and kill the page. We'll see what kind of structure we get back. Or we could dump that in our Twig template.
12:04
Let's go over and give that a refresh. And you can see this is now an object with a Laravel collection. Laravel collection is a wrapper around array data. So, again, if you've never worked with Laravel before, all of these items from the database,
12:20
which you can actually see are now coming from the database, so we've successfully queried this, are wrapped in this collection. Now, don't worry too much about this because a collection is iterable. So we can iterate over these values as we normally would within a Twig template and it won't affect anything. But collections give you additional functionality on here to change the structure of the data, filter the data, whatever you need to do.
12:43
For now, though, let's just look at iterating through this data. And we don't really need to do much because we know over in our home, we're just iterating over each of these users and we're grabbing a name property out for each one. So let's go over and give that a refresh.
12:58
And there we go. Sure enough, we get all of the data now from our database. Now, what we've covered here is setting everything up. But what we are doing is manually accessing the database.
13:09
We're not working with models. It's probably a good idea to switch this over to work with models instead. It's a lot more fluent and a lot easier to sprinkle in any additional functionality you need, including creating relationships between items. So let's go over to the next episode and look at how we work with models.
54 episodes•4 hrs 45 mins•8 months ago
Overview
Starting completely from scratch, build a modern PHP framework with all the features you’d expect.
Whether you’re new to PHP or not, this is a great exercise for learning what happens under the hood, arming you with knowledge you can apply anywhere you use PHP.
We’ll cover routing, controllers, views, the container, accessing the database, models, authentication, config, CSRF protection, exception handling, pagination, validation, flashing messages and much more.
Let’s dive in and build a PHP framework, step-by-step!
Alex Garrett-Smith
Hey, I'm the founder of Codecourse!
Episode discussion
No comments, yet. Be the first!