Build Your Own PHP Framework
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

0%
Your progress
  • Total: 4h 45m
  • Played: 0m
  • Remaining: 4h 45m
Join or sign in to track your progress
01. Setting up
01. Introduction
2m 23s
0%
02. Composer and PSR-4 Autoloading
6m 17s
0%
03. Bootstrapping and structure
3m 18s
0%
04. Error reporting with Ignition
3m 49s
0%
02. Container
05. Using a container
3m 21s
0%
06. Adding service providers
5m 23s
0%
07. Autowiring with reflection
5m 5s
0%
08. Refactoring to a singleton
4m 17s
0%
03. Application config
09. Building the Config class
6m 19s
0%
10. Reading and merging config files
6m 21s
0%
11. Registering service providers through config
2m 13s
0%
12. Using env files
6m 26s
0%
04. Routing and controllers
13. Everything starts with requests
6m 52s
0%
14. Adding routing
5m 27s
0%
15. Responding in routes
4m 13s
0%
16. Cleaning up responding
7m 12s
0%
17. Defining routes in a separate file
3m 10s
0%
18. Creating a controller
5m 17s
0%
19. Using services inside controllers
2m 3s
0%
05. Views
20. Installing Twig
6m 22s
0%
21. Rendering a view
3m 36s
0%
22. Debugging in Twig
4m 45s
0%
23. Creating a base layout
3m 38s
0%
24. Page titles in Twig
4m 31s
0%
25. Creating a Twig config helper
8m 57s
0%
06. Database
26. Setting up and querying the database
13m 29s
0%
27. Working with models
4m 32s
0%
28. Creating an example user profile page
5m 49s
0%
07. Authentication
29. Setting up Sentinel
8m 36s
0%
30. Creating the registration flow
5m 44s
0%
31. Registering and authenticating a user
5m
0%
32. Showing authenticated state
5m 40s
0%
33. Logging in
3m 59s
0%
34. Logging out
3m 2s
0%
08. CSRF Protection
35. Protecting routes with CSRF
7m 51s
0%
36. Adding tokens to forms
6m 5s
0%
09. Flashing messages
37. Basic session flashing
5m 46s
0%
38. Creating a Twig helper and flashing globally
1m 39s
0%
10. Validation
39. Validating the login form
6m 40s
0%
40. Manually adding validation errors
1m 24s
0%
41. Validating the registration form
4m 5s
0%
42. Custom validation rules
9m 39s
0%
11. Middleware
43. How middleware works
5m 41s
0%
44. Adding auth middleware and grouping routes
7m 37s
0%
45. Persisting old form data
6m 53s
0%
12. Exception handling
46. Creating an exception handler
3m 38s
0%
47. Custom 404 (and other error) views
7m 27s
0%
48. Rendering views for CSRF errors
5m 18s
0%
13. Pagination
49. Paginating with Eloquent
7m 48s
0%
50. Building a pagination view with Twig
7m 38s
0%
14. Global helpers
51. Registering functions with PSR-4
1m 50s
0%
52. Container resolution helper
2m 24s
0%
53. View and config helpers
4m 12s
0%
54. Route generator helper
4m 34s
0%

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.

Episode summary

In this episode, we make the big switch from using hard-coded, manual data on our homepage to actually pulling real data from a database. The goal is to connect to a MySQL database and fetch users dynamically, so our app gets a lot closer to something you'd use in production.

We kick things off by setting up Laravel's Eloquent ORM outside of a normal Laravel project, using the standalone illuminate/database package. We do a composer require, set up our environment variables, and create a new database service provider to centralize all the configuration. There's a bit of a walkthrough on how to configure MySQL connection details (host, database, username, password, etc.) using env vars, so you can easily switch between local and production setups.

Once the config is done, we actually create a simple users table manually, toss in a few test users, and then wire up the database manager so that our controller can run queries. The highlight is doing a basic .get() query to fetch all users from the database, dumping the results out to show how Eloquent collections work, and then looping through them in the Twig template. At this point, we've fully replaced static data with real database records!

The episode wraps up with a quick note: we're still directly querying the database, but next episode, we'll improve everything by moving over to proper Eloquent models for even cleaner code and better features. So, if you're following along, you now have database queries set up and ready for the next step!

Episode discussion

No comments, yet. Be the first!