Okay guys.
So this is where we have stopped in the last video by making sure the messages table is created in our
SQLite database.
Let's close it right now.
And let's go back to the points that we need to cover.
So now it's time that we start really working with a database in the sense that we're going to be inserting
some data, reading it, and also seeing how we can sort the results to see the most recent comments
first.
So starting with this video we're going to be actually inserting the data into the database.
This means we are going back to our contact post router.
So we stopped at this point by just seeing the data is fine.
Now it's time that we insert this data into the database.
Now at this point we will jump back to the db php file to add a new function that will insert the new
message into the database.
And by the way, we are going to learn about some safety measures that we will undertake to make sure
we are secure.
So specifically, this is the prepared statements.
I'm gonna talk about this in detail once we start writing this insert SQL query and also discovers how
to use PDO, which is this database abstraction of PHP that I've mentioned before.
Now I've also have this page open which basically describes in, let's say, a super technical way,
how you should construct the query that inserts data into a database.
So let me write a function called insert message.
And here we're gonna be asking for name For email and for the message.
And this time we're going to be returning a value, a boolean that will just say if we were successful
or not.
And maybe let me make the first argument, the PDO connection.
So let's begin by writing the SQL statement into the SQL variable.
So we're going to begin at this point because we just want to insert data every single time.
So we just type insert.
Then we go straight to into to specify where we would like to insert it.
After that the arrow leads us to schema which we don't have a name for, and we just go straight to
table.
That's also a possible connection.
Our table name is messages.
And after that we open the parentheses as you see here, right after the table name, we jump to parentheses.
And here we list the column names we would like to specify values for.
In our case this is name email and message.
Why only those columns.
Let's jump back to the file.
This is the structure of the table.
We've got ID name email message and createdat id is auto incremented.
I've mentioned that before.
It's just the value for it is being assigned by the database.
That's the next number.
And Createdat is also automatically populated by the database because we have added a default value
for it being the current timestamp.
That's why we only need to insert something into three of those columns.
What next?
We close the parentheses and we add this values keyword.
Let's do it.
Then goes parentheses.
And we either put expressions or we might do parameter binding which is what we are going to do.
And I'm going to explain it.
So at this point we would like to use a prepared statement.
That's a generic SQL databases technique that separates the arguments and data that you insert update
inside the database from the actual query.
This means we won't be passing the values here like the name and email directly.
At least we won't be passing them directly because we can't trust the user input.
That's why we use the placeholders like name like email and message.
I've came up with those names.
You can use any names.
I just want them to be sensible and match the column names.
So use a column and you provide a name for a parameter.
So bear with me because the next step is to prepare a statement.
You run PDO prepare SQL.
Now at this point we are ready to run the query.
So we run the statement.
By the way, the statement is just an object that is being returned by the PDO.
Prepare.
That's an object with some specific methods, one of which is execute.
And now I can pass an array with the values of the parameters that I've given names here.
And this would just prevent a cyber attack known as SQL injection.
Let's stop for a second here because this is super important.
So the SQL injection is as the name suggests, someone wants to inject some malicious SQL code into
your queries.
If you wouldn't separate the user input like we do using the prepared statements, and even worse,
you will just just straight ahead.
Add whatever user sends into this query.
For example, doing it this way.
This might contain some malicious SQL that might, for example, delete the data or even drop your tables
if your database permissions aren't set up correctly.
That's why we always use prepared statements to make sure that nothing wrong happens to our data, and
by using prepared statements, we make sure that if the input for name or email is the SQL code, then
it's just not run.
Because if you think about it, what stops me from writing?
Drop database or drop table here, which is a code that could potentially remove the database or remove
a table.
Okay, so with that said, now we should bound to the attributes.
So we bound to name the value of name variable to email the value of email variable.
And consequently to message we bound the value of message variable and only.
This safely runs this query.
Okay, so finally the return type from this function is boolean which should indicate if we were successful
or not.
So let's return it after the statement is run using execute, it will have another method that we can
use called row count.
So since we are inserting data here, if we are successful, the row count should return something that's
more than zero.
And this is how we can check if we were successful.
Okay, now I think we can go back to our route and instead of VAR dumping data here, let's actually
insert it into the database.
So at this point we can call insert message.
Let's grab the connection from here and just pass it to this function.
We won't run any more queries in here.
Then the parameters I can use named parameters because I don't remember the order name is name.
Email is email and the message is message.
So you can see how the name parameters is really a lifesaver.
I could never remember the names or the order of the parameters names I can remember.
Okay, so this returns a boolean value.
So let's use it.
Let's call it insert it.
And now I think we owe people the message if they were successful or not.
So let's say If inserted, then we also like to escape the name.
So keep in mind that also nothing stops people from using HTML tags or some other meta tags that might
even try to include some scripts, some malicious scripts.
That's why when we are displaying the user input, we should always escape it.
That's another thing that you need to keep in mind.
In PHP there is this HTML special chars function that that can do that.
I'm passing the name here because I'd like to just say thank you for sending the message and refer to
the person name.
I also want to escape the quotes.
So I'm passing this constant and I want the UTF encoding.
Then I can say thank you.
Save name for your message.
It was stored something like this.
And at this point we would like to stop the execution.
But it would just happen anyway.
Maybe.
Let's make sure we exit anyway.
Because now if it wasn't inserted.
On the other hand, I'd like different status code.
This would be 500 that there was some kind of error without saying what it was.
That's why I think we can jump to router and add another function of.
This kind, which could be just server error.
This is how it is often just described without going into specifics.
That's the default message.
It's void.
And this is HTTP response code 500.
This means it is an unknown server error.
It typically is returned when there is something very bad going on, like database is down or the server
isn't working, or there is some kind of a fatal exception fatal error in your PHP code.
You also don't want to give people too many details about what happened, so it's enough to just tell
them something went terribly wrong.
Goodbye.
Okay, so instead of using this code directly, let's use our server error function.
I think this is how I call that.
Let me check.
Yes, that is a.
Server error and let's say could not store the message.
Sorry, I don't think we need this line anymore.
Now, in the future, there can be a better way to handle this.
We might store a so-called flash message that will be displayed for the duration of the next request
only.
And we can redirect the person to some other page.
For now, let's keep it this way.
So the final task here is to test that.
So I'm going back into the form, and I'm going to use this bold tag to try out our functionality.
I'm going to use my real email.
And I'm going to just say hello.
So as you see this displays the HTML tags as it would be a text.
It's not treating this B element as this would be an actual tag.
It's using the HTML entities, which means whatever tags anyone would like to use in the input, it
won't be parsed by the browser, it would be just displayed as code.
So the message was apparently stored.
We don't really display those messages.
That's why I will jump to the database.
Okay, I had to press this refresh button.
Okay.
There we have it.
For every single column we've got the values that I have just submitted.
And you can also see that Createdat column was automatically populated with my current time, or at
least the database current time, which confirms that everything works perfectly and that more importantly,
we are safe when inserting data to the database.