2.1 Creating Records (Inserting Data)

In the previous lesson, we created a database in the database table for our address book. And now, we just need to add some data to that table. And let's first of all, talk about where these files are going. So, I'm assuming that you're using XAMPP, if not, you need to go to wherever your web server serves files. So for me that is inside of XAMPP and then there's htdocs. That is where this installation of Apache is serving files from. And then I created a folder called address-book. And that's where all of my files are going to go. So the first file that we need is index.php. And this would typically be where we display our list of people inside of the address book. So of course we don't have any data to display yet. So I'm just going to add some HTML, add the title. And that's going to be it for now. Now for this lesson, we want a page that has a form so that we can receive the first name, last name, and email address. And then we can process that information and then store it in the database. Now I am going to paste in this markup because you don't want to see me type all of this. And it's very simple. I mean we have just three fields. But before we get into the fields, I am using the cdn of bootstrap, just so that we have something a little bit nicer than the default style sheet to look at. Then I have a form where the method is POST, because when it comes to creating something on the server, we want to use a POST request. From a form the other alternative is GET and that is for getting or retrieving information. That's not what we are doing here, we are creating. So then I have three fields. I have a first name where the name attribute is first-name. Now that is important because when the browser sends our form data to the server, it's going to use whatever is used as the name attribute for these fields. So that's how we get information on the server. So we need name here. And then we have one for the last name, the name is last-name. And then finally, we have the one for the email, and there we go. So we have our form, now we just need the code that will process that form. And we can put that inside of this file because we are posting to this same file. However, I like to separate my code from my markup as much as possible. I mean, we can always get away from mixing a little bit here and there. But for the most part I want to keep the vast majority of the processing code away from my markup. So what we're going to do is require another file. Now ideally we would be using some kind of templating engine that would have this capability just built in. And we could also have a layout page, so that we wouldn't have to replicate a bunch of markup. We could write our own templating engine, but that's getting into the weeds. So for now, we're just going to just do something very simple. We're going to require. We'll have a folder called app, and that's where we're going to have the code for this particular page. And there we go. So all of the processing code is going to be inside of this add.code.php inside of a folder called app. So there we go. Then we will add the new file and away we go. We will have our php here. Now the first thing that we need to do is check to see what type of request has occurred, because this add page is going to be handled for both GET and POST requests. So if we make a request for this, we will of course see our form here, that is a GET request. In the case of a get request, there's nothing there for us to process. So we don't want to do anything for a GET request. So we have two options. The first thing that we could do is check to see if it is a POST request, and then do all of our processing inside of there. Or we can check to see if it is a GET request and then do something like this. So we'll have our server variable, we will get the request method. And we could do this, if it equals GET, then we're done here. We don't want to do anything. Everything else will be used for processing a POST request. And the first thing that we need to do is get the form information. First name, last name, and email. But one very important rule that you need to remember is that you should never ever, ever, trust user input because you don't know who your user is. They could be the nicest person in the world. Or they could be a criminal trying to break into your application, steal information, or at least cause a little bit of trouble by trying to delete things inside of your database. So the very first thing that we want to do right off the bat is filter the INPUT. And we're going to specify the POST. And we want to filter the first-name here. And we're going to say that we want to SANITIZE_STRING, that's going to take out anything that could be considered harmful like HTML or certain encoded characters. So we want to strip out all of that stuff. And we essentially want to do that for everything else, including the last name and email. So, we will just copy and paste and then make the necessary changes. Now email is going to be a little bit different because remember that we may or may not have an email address, so it's not enough to just sanitize the email. We also need to check to see if we have something for the email address. And if not, then we want to set $email = null. Now, this is very important, we don't want to set it as a string of null because that is just going to store null as a string in the database. There is a value inside of the database called NULL. It is a special value and we want to use that, so we will set $email = NULL, and then we have our data ready to store in the database. And we use something called PDO or PHP data objects. We new up the PDO constructor, and we need to supply three things. The first is what's called the connection string. It contains the information about the database server that we want to connect to. For example, we need to tell PDO that we are going to connect to a MySQL database. Then we want to specify the name of the database, we do so with dbname, and then we set that value, which in our case, is address_book. Then we have a ; and then we have the host. This is the computer that our database server is running on and in our case that is localhost. Then we want to set the port and our port information Is in our XAMPP Control Panel. So right there, 3306, we want to use that. So the port will be 3306. And there we go, that is our connection string. Now we need to supply the username, which in my case is root, and then the password which is an empty string in my case. So there are three arguments, the connection string that contains the type of database that we are connecting to, the database name, the host, and the port. Then the second argument is the username, the third argument is the password. And if everything goes according to plan, then we will have a database object that we can work with. So then we wanted to define our SQL. Now SQL is in and of itself its own language and it's how we insert data, it's how we select data, update and delete. So in our case we are inserting data, so we are going to INSERT INTO the people table. So in this particular case, it reads very much like how we would want to say it. So we are going to INSERT INTO people. And then we specify the columns that we are going to provide data for. So that is first_name, last_name, and then email. But then we also say here are the values for those columns and we have to use the same order that we specified. So if we had first_name, last_name, and then email, and then for the values we specified last _name, and then first_name, and then email, well that would get our data all mixed up. So, it doesn't matter the order that we specify our columns in, but our values need to be in the same order that we used for our columns. Now in this particular case what we are going to do is build a prepared statement, which is really the best way of working with a database because it allows us to circumvent a very popular attack called a SQL injection attack. Now basically what this is, is someone would come to our page and try to break it by inserting SQL commands. And if you're not careful, an attacker can actually display all of the content within a table or the entire database. They can also delete things and break your application. So by using a prepared statement, we can essentially get around that attack all together. And so for our values, it's gonna look like this. We're gonna have a :, followed by what is essentially a variable name. But it's not really a variable, it's a parameter. So you can think of this like a function and we're going to supply values to that function, bypassing arguments and you will see us do that here in a moment. And we will do the same thing here for the last_name, and then finally, email. So this is our string and I used double quotes, I'm going to use single quotes. So we have INSERT into people. We specify our columns, followed by this VALUES keyword, and then the values that we want to display. And we're going to use the SQL statement to build a prepared statement. And we will do that using our db-object. We're going to call this prepare method and we will pass in that SQL variable. So this will create a prepared statement that we can then execute. And then we will pass in an array, where the keys are these variable names or these parameter names that we specified. And then the values are, well, the values that we want to store for them. So we would have first_name and then we would have last_name. And that would be our last_name variable. And then finally we would have email which would be our email. And by calling this execute method, we are actually executing our SQL statement, and that would be inserting data into our people table. So that by the time that that's done, we want to go ahead and set our statement object as null. And our db object as null and then that's it. So let's test this. Let's do a refresh, make sure everything is going to load just fine, and we will try to store John Doe. Now John Doe is not going to have an email. So let's submit and we have an error, call to undefined function filter, and yes, it is not filter. It is filter_input. So, there we go. Let's go back to the browser. Let's refresh which is going to resubmit the form. And it looks like everything went just fine. So we can go to phpMyAdmin. Let's go to our address book. Let's go to people, and we should see a new record, we do. Now notice here that null is italicized. That mean that's good. That means that that is actually null. If it was not italicized, and if it was just normal text, which we can come in here and we can change that to. If it looks like this, then that would mean that we actually stored the string of null, that's not what we want. In this case we wanted null, so there we go. We have just inserted data into our database. So in the next lesson, we can write the code that will retrieve that information and display that in the browser.