Okay.
So we've created the form and the endpoint that is validating it.
So it's time to work with the databases.
But first we need this database to be somehow created.
And this is what we are going to do in this video.
So now it's time to cover the model.
So we will be talking about the data, how we store it, the structure of the data.
So let's begin with what database we're going to be using.
This is a SQLite database.
This is a database that lives inside a single file that can be part of your project.
So the benefits of this database is that it is performant enough.
And it is super simple.
It doesn't require any additional database server software like would be the case with Postgres or MySQL.
So it's great for learning tutorials.
And also it's also using the same SQL language like you would have to use with MySQL or Postgres.
So basically changing later to another database should be a no brainer.
So we would like to have a db folder that would contain this SQLite database file.
And another one would be an SQL schema file.
So this would just define the structure of all the tables that we're going to have in this database.
Now additionally we're going to have this db php file.
This would contain all the logic related to working with the database, like a function to connect to
the database.
And by the way, to connect and to work with the databases, we're going to be using the PDO library
that is built in to PHP and using it is the best practice.
Now additionally, we're going to create a command line script that will load the schema file and just
make sure that our database is created and the schema.
So the tables are created with the proper columns.
So let's get started.
So I'm going to leave this diagram here on the right so that both you and me make sure that we do everything
according to the plan and that the plan is solid.
So let's open the directory structure and we are missing the DB folder.
Let's create it right now.
Then inside the includes folder we need the database file.
Let's add it.
Let me start with a PHP tag then the db folder, so it needs the schema file.
Let's create the schema SQL file.
And another one is the db SQL Lite.
Now it's enough that we just create this empty file and close it.
And when we create the tables by trying to connect to this database, it will be filled with some binary
data so that we won't have to open it again.
But the file needs to exist.
First, let's add the database schema using SQL.
Now, from the lack of a better tutorial on how to write SQL to create a table, I am forced to use
the SQL Lite official documentation, which is not very beginner friendly.
Anyway, let's try to write a statement that will create a table.
So we need to write starting from create.
So the SQL language has this notion, this convention that the keywords in it are written using uppercase.
So you might as well enable Caps Lock right now.
So we need to create the table.
So this is what we write.
Create table.
Optionally we can add this combination of letters if not exists.
So if you never written SQL you might be surprised that it is written and reads like plain English.
Create table if not exists.
Yes, we want to do that.
We only want to create a table if it doesn't exist.
So in databases you've got schemas.
We're going to skip that and just use a table name.
As you see this is possible.
So I'm just following those arrows.
I'm skipping the schema and just choosing the table name.
I can disable the caps lock and this is messages.
And now I open the parentheses and add a semicolon.
Now why I did that.
You can see here that right after the table name we follow and have an opening parenthesis inside.
There can be multiple column definitions.
And then we jump to the closing parenthesis which is essentially what I did.
Now we need to define some columns.
So for the column definition we can open this additional.
Let's call it a diagram.
And let's start with an ID.
We need to identify every single message.
So there is column name.
Then the column type.
This is an integer.
And this is a primary key.
Databases have only one single primary key.
That's a column that will uniquely identify a row in a table.
And we want this integer to auto increment, which means I'm not gonna be choosing a value for it.
Database will by always picking the next bigger number.
Then I want the name.
That's the name of the person from the form.
This is text and I want it to be not null.
That's a database constraint saying that I don't allow this column to be null.
This would be an error in the database level.
If you try to insert a record with null value for that column.
And this is part of this column constraints here.
Next up we've got email.
I also want this to be text and not null.
By the way this SQL syntax this keywords like this and that and this.
They can be lowercase.
It's just a convention that they are all uppercase.
Okay.
Next up we are adding a message.
I also want to make it not null.
And let's add a created add column which would be our timestamp.
This is just a date and time and we can select a default value for it, which is the current time stamp.
It's a smart move because we would have the createdat date of every single message, but we would not
have to create a logic.
Producing this date database will, and it will do it automatically for every inserted row.
Okay, I think we've managed it wasn't that hard.
And now this schema SQL file should create our database table.
So remember that you can look up this SQLite docs if you are brave enough of course.
Okay, let's close this file for now and go back to this db php file.
So we need a way to connect to the database.
Let's call this function connect db.
It returns an instance of pdo.
So PDO is this PHP extension that is used to connect to different databases.
It is a kind of a database abstraction.
Now it means that you can use the same functions and the same interface to connect to different databases,
even if they differ.
Now you would have to write the SQL code yourself.
So if there are differences between databases, well that's on you.
But still, this is pretty convenient that we don't have to use some separate functions or classes.
We can just use one.
So here we would have to provide the path to the database file.
And this means it would be convenient to create another constant that can easily point us to the database
directory.
Let's use the commonly understood DB shortcut for that.
Okay now going back to this db file.
So let's create PDL.
So we create a new instance of the PDL class.
And we need to start with typing SQLite colon.
This is telling PDL that we are going to be using the SQLite connection.
And now I need to point to the database file.
For that I'm going to be using the db directory.
Is that the right name?
Yes db dir.
And then I'm gonna add a And slash and the database file is db SQL Lite.
So this is DB dot SQL Lite.
All right.
Now we would like to return this PDO connection.
But before we do let's also set some attributes of the connection.
So this is done by calling the set attribute method of PDO object.
And there are some predefined constants that you can use.
So this basically sets some settings of the connection.
And I'd like to set the error mode to a specific one.
And the second argument for this method is what you would like to be the value of this error mode.
And we want this to be the error mode exception, which means that the PDO will be throwing PHP exceptions
every single time that anything goes wrong.
Now, so far, I've intentionally ignored handling errors and the exceptions because we will have some
separate videos about error handling.
So I just don't want to explain everything at once.
That's why we're not going to be handling any errors in here.
And we are just about to use this function.
So at this point, we should jump to the index file and make sure that every single file has access
to our newly created DB file.
And let's think where we might need the database connection.
Can you guess.
Yes.
So that's this route.
So we've been getting the input here filtering it and validating it.
So it might be a good idea to try and connect to the database by just calling this connect db function.
And that would be everything.
For now.
We will just check if we can connect to this db SQLite file that is currently empty.
So let me fill out this form with anything.
And well we can see that we got the data and there wasn't any exception.
Which means that we were able to connect to the database, which as a reminder is currently empty.
We haven't created this table yet.
We need to run this SQL first, but that's a topic for a separate video.
For now, I think we are fine because we were able to create the code that will create the table and
also the code that will connect to this database.
So let's have a short break right now.
And next up we're going to be creating this messages table.