WEBVTT 00:00.530 --> 00:00.860 Okay. 00:00.860 --> 00:05.990 So we've created the form and the endpoint that is validating it. 00:05.990 --> 00:08.450 So it's time to work with the databases. 00:08.690 --> 00:13.490 But first we need this database to be somehow created. 00:13.490 --> 00:17.420 And this is what we are going to do in this video. 00:19.310 --> 00:22.430 So now it's time to cover the model. 00:22.430 --> 00:28.940 So we will be talking about the data, how we store it, the structure of the data. 00:30.140 --> 00:34.100 So let's begin with what database we're going to be using. 00:34.310 --> 00:37.310 This is a SQLite database. 00:37.340 --> 00:43.640 This is a database that lives inside a single file that can be part of your project. 00:43.640 --> 00:48.590 So the benefits of this database is that it is performant enough. 00:48.590 --> 00:50.900 And it is super simple. 00:50.900 --> 00:59.480 It doesn't require any additional database server software like would be the case with Postgres or MySQL. 00:59.510 --> 01:02.860 So it's great for learning tutorials. 01:02.860 --> 01:12.430 And also it's also using the same SQL language like you would have to use with MySQL or Postgres. 01:12.460 --> 01:19.420 So basically changing later to another database should be a no brainer. 01:19.570 --> 01:27.760 So we would like to have a db folder that would contain this SQLite database file. 01:27.820 --> 01:32.080 And another one would be an SQL schema file. 01:32.110 --> 01:39.640 So this would just define the structure of all the tables that we're going to have in this database. 01:40.750 --> 01:44.530 Now additionally we're going to have this db php file. 01:44.530 --> 01:53.440 This would contain all the logic related to working with the database, like a function to connect to 01:53.470 --> 01:54.640 the database. 01:54.640 --> 02:02.290 And by the way, to connect and to work with the databases, we're going to be using the PDO library 02:02.290 --> 02:08.490 that is built in to PHP and using it is the best practice. 02:08.610 --> 02:16.650 Now additionally, we're going to create a command line script that will load the schema file and just 02:16.650 --> 02:22.590 make sure that our database is created and the schema. 02:22.590 --> 02:26.760 So the tables are created with the proper columns. 02:27.630 --> 02:28.980 So let's get started. 02:30.120 --> 02:38.460 So I'm going to leave this diagram here on the right so that both you and me make sure that we do everything 02:38.490 --> 02:42.630 according to the plan and that the plan is solid. 02:43.620 --> 02:50.190 So let's open the directory structure and we are missing the DB folder. 02:50.190 --> 02:51.930 Let's create it right now. 02:52.350 --> 02:57.840 Then inside the includes folder we need the database file. 02:57.840 --> 02:59.280 Let's add it. 03:00.630 --> 03:08.960 Let me start with a PHP tag then the db folder, so it needs the schema file. 03:08.960 --> 03:12.350 Let's create the schema SQL file. 03:13.550 --> 03:19.550 And another one is the db SQL Lite. 03:21.440 --> 03:26.420 Now it's enough that we just create this empty file and close it. 03:26.660 --> 03:34.370 And when we create the tables by trying to connect to this database, it will be filled with some binary 03:34.400 --> 03:37.940 data so that we won't have to open it again. 03:37.940 --> 03:40.340 But the file needs to exist. 03:40.970 --> 03:46.010 First, let's add the database schema using SQL. 03:47.810 --> 03:55.610 Now, from the lack of a better tutorial on how to write SQL to create a table, I am forced to use 03:55.610 --> 04:01.730 the SQL Lite official documentation, which is not very beginner friendly. 04:01.730 --> 04:07.180 Anyway, let's try to write a statement that will create a table. 04:07.420 --> 04:11.590 So we need to write starting from create. 04:12.130 --> 04:21.190 So the SQL language has this notion, this convention that the keywords in it are written using uppercase. 04:21.190 --> 04:24.700 So you might as well enable Caps Lock right now. 04:24.940 --> 04:28.150 So we need to create the table. 04:29.530 --> 04:30.550 So this is what we write. 04:30.580 --> 04:31.930 Create table. 04:31.960 --> 04:37.900 Optionally we can add this combination of letters if not exists. 04:37.900 --> 04:45.880 So if you never written SQL you might be surprised that it is written and reads like plain English. 04:45.910 --> 04:49.660 Create table if not exists. 04:50.230 --> 04:51.730 Yes, we want to do that. 04:51.760 --> 04:55.510 We only want to create a table if it doesn't exist. 04:56.320 --> 05:00.460 So in databases you've got schemas. 05:00.460 --> 05:03.340 We're going to skip that and just use a table name. 05:03.340 --> 05:05.260 As you see this is possible. 05:05.260 --> 05:07.840 So I'm just following those arrows. 05:07.890 --> 05:12.000 I'm skipping the schema and just choosing the table name. 05:12.930 --> 05:16.950 I can disable the caps lock and this is messages. 05:17.580 --> 05:22.350 And now I open the parentheses and add a semicolon. 05:23.610 --> 05:25.080 Now why I did that. 05:25.110 --> 05:33.000 You can see here that right after the table name we follow and have an opening parenthesis inside. 05:33.000 --> 05:36.570 There can be multiple column definitions. 05:36.750 --> 05:42.180 And then we jump to the closing parenthesis which is essentially what I did. 05:42.210 --> 05:44.730 Now we need to define some columns. 05:44.730 --> 05:48.840 So for the column definition we can open this additional. 05:49.500 --> 05:51.390 Let's call it a diagram. 05:51.450 --> 05:53.880 And let's start with an ID. 05:53.910 --> 05:56.820 We need to identify every single message. 05:56.820 --> 05:59.010 So there is column name. 05:59.010 --> 06:01.110 Then the column type. 06:01.590 --> 06:03.810 This is an integer. 06:03.960 --> 06:07.050 And this is a primary key. 06:07.080 --> 06:10.250 Databases have only one single primary key. 06:10.280 --> 06:15.650 That's a column that will uniquely identify a row in a table. 06:15.650 --> 06:23.540 And we want this integer to auto increment, which means I'm not gonna be choosing a value for it. 06:23.570 --> 06:29.240 Database will by always picking the next bigger number. 06:29.390 --> 06:31.220 Then I want the name. 06:31.220 --> 06:34.490 That's the name of the person from the form. 06:34.490 --> 06:38.420 This is text and I want it to be not null. 06:38.480 --> 06:45.710 That's a database constraint saying that I don't allow this column to be null. 06:45.740 --> 06:48.620 This would be an error in the database level. 06:48.620 --> 06:53.990 If you try to insert a record with null value for that column. 06:53.990 --> 06:57.890 And this is part of this column constraints here. 06:58.820 --> 07:01.700 Next up we've got email. 07:02.330 --> 07:05.780 I also want this to be text and not null. 07:05.810 --> 07:11.720 By the way this SQL syntax this keywords like this and that and this. 07:11.960 --> 07:13.610 They can be lowercase. 07:13.610 --> 07:17.240 It's just a convention that they are all uppercase. 07:17.480 --> 07:17.930 Okay. 07:17.960 --> 07:20.450 Next up we are adding a message. 07:20.450 --> 07:23.390 I also want to make it not null. 07:23.810 --> 07:29.450 And let's add a created add column which would be our timestamp. 07:29.450 --> 07:38.030 This is just a date and time and we can select a default value for it, which is the current time stamp. 07:38.180 --> 07:46.820 It's a smart move because we would have the createdat date of every single message, but we would not 07:46.820 --> 07:48.350 have to create a logic. 07:48.350 --> 07:56.390 Producing this date database will, and it will do it automatically for every inserted row. 07:56.420 --> 07:59.510 Okay, I think we've managed it wasn't that hard. 07:59.510 --> 08:05.030 And now this schema SQL file should create our database table. 08:06.230 --> 08:12.730 So remember that you can look up this SQLite docs if you are brave enough of course. 08:12.970 --> 08:18.070 Okay, let's close this file for now and go back to this db php file. 08:18.340 --> 08:22.030 So we need a way to connect to the database. 08:24.280 --> 08:26.590 Let's call this function connect db. 08:26.620 --> 08:29.380 It returns an instance of pdo. 08:29.710 --> 08:38.320 So PDO is this PHP extension that is used to connect to different databases. 08:38.350 --> 08:41.770 It is a kind of a database abstraction. 08:41.800 --> 08:50.020 Now it means that you can use the same functions and the same interface to connect to different databases, 08:50.020 --> 08:51.820 even if they differ. 08:51.850 --> 08:56.470 Now you would have to write the SQL code yourself. 08:56.470 --> 09:01.630 So if there are differences between databases, well that's on you. 09:01.660 --> 09:08.470 But still, this is pretty convenient that we don't have to use some separate functions or classes. 09:08.470 --> 09:10.540 We can just use one. 09:12.310 --> 09:17.580 So here we would have to provide the path to the database file. 09:17.880 --> 09:27.270 And this means it would be convenient to create another constant that can easily point us to the database 09:27.270 --> 09:28.320 directory. 09:28.350 --> 09:32.970 Let's use the commonly understood DB shortcut for that. 09:35.250 --> 09:39.090 Okay now going back to this db file. 09:40.710 --> 09:42.840 So let's create PDL. 09:43.740 --> 09:48.300 So we create a new instance of the PDL class. 09:50.340 --> 09:54.240 And we need to start with typing SQLite colon. 09:54.270 --> 09:59.760 This is telling PDL that we are going to be using the SQLite connection. 09:59.760 --> 10:03.300 And now I need to point to the database file. 10:03.330 --> 10:07.440 For that I'm going to be using the db directory. 10:07.530 --> 10:08.730 Is that the right name? 10:08.730 --> 10:10.410 Yes db dir. 10:11.730 --> 10:20.540 And then I'm gonna add a And slash and the database file is db SQL Lite. 10:22.580 --> 10:26.450 So this is DB dot SQL Lite. 10:30.320 --> 10:30.680 All right. 10:30.710 --> 10:36.050 Now we would like to return this PDO connection. 10:36.050 --> 10:41.000 But before we do let's also set some attributes of the connection. 10:42.590 --> 10:50.150 So this is done by calling the set attribute method of PDO object. 10:50.150 --> 10:54.650 And there are some predefined constants that you can use. 10:55.340 --> 10:59.060 So this basically sets some settings of the connection. 10:59.060 --> 11:02.840 And I'd like to set the error mode to a specific one. 11:03.110 --> 11:09.770 And the second argument for this method is what you would like to be the value of this error mode. 11:09.770 --> 11:18.820 And we want this to be the error mode exception, which means that the PDO will be throwing PHP exceptions 11:18.820 --> 11:21.700 every single time that anything goes wrong. 11:21.730 --> 11:30.190 Now, so far, I've intentionally ignored handling errors and the exceptions because we will have some 11:30.190 --> 11:32.500 separate videos about error handling. 11:32.500 --> 11:36.040 So I just don't want to explain everything at once. 11:36.040 --> 11:39.910 That's why we're not going to be handling any errors in here. 11:39.910 --> 11:43.810 And we are just about to use this function. 11:46.420 --> 11:54.280 So at this point, we should jump to the index file and make sure that every single file has access 11:54.280 --> 11:57.640 to our newly created DB file. 11:58.810 --> 12:03.130 And let's think where we might need the database connection. 12:03.130 --> 12:04.600 Can you guess. 12:05.410 --> 12:05.770 Yes. 12:05.770 --> 12:07.600 So that's this route. 12:07.600 --> 12:12.580 So we've been getting the input here filtering it and validating it. 12:14.080 --> 12:21.480 So it might be a good idea to try and connect to the database by just calling this connect db function. 12:21.480 --> 12:23.730 And that would be everything. 12:23.730 --> 12:24.390 For now. 12:24.390 --> 12:32.910 We will just check if we can connect to this db SQLite file that is currently empty. 12:35.220 --> 12:38.400 So let me fill out this form with anything. 12:38.400 --> 12:47.010 And well we can see that we got the data and there wasn't any exception. 12:47.010 --> 12:53.640 Which means that we were able to connect to the database, which as a reminder is currently empty. 12:53.670 --> 12:56.340 We haven't created this table yet. 12:56.340 --> 13:02.340 We need to run this SQL first, but that's a topic for a separate video. 13:02.340 --> 13:10.140 For now, I think we are fine because we were able to create the code that will create the table and 13:10.140 --> 13:14.190 also the code that will connect to this database. 13:14.520 --> 13:16.230 So let's have a short break right now. 13:16.230 --> 13:20.250 And next up we're going to be creating this messages table.