Use form data to create records

- [Instructor] In this movie, we will learn to create new records in the database by using submitted form data. Let's start by reminding ourselves what an SQL insert statement looks like. We have insert into, and then the name of the table we're using, and then in parentheses we have a comma delimited list of the different columns we're going to be providing values for. Doesn't matter what order they're in, but the values have to correspond to the same order. And then you can see in the continuation there, we have values, and then in parentheses, the different values as a comma delimited list that matches the same order as the columns that we had above.

So that's the SQL insert statement that we're going to be composing, and then we're going to be sending that in as our query to MySQLI Query. That's different from what we were doing when we were using select, and we were asking for it to select records, now we're going to be using an insert. And when we're doing a insert, we're not going to need a subject's ID anymore, like we did when we were finding a single record. Right, we're creating a new record, we're saying hey, SQL, here's a batch of data, create a record with it. That data is usually from form data.

And that makes sense because a form allows us to provide a lot of information that we want to put in that record. However it doesn't have to come from a form. For example, when you click a like or favorite link on a blog post, it could tell SQL to create a new record there too. That wouldn't be from a form, it would just need to know the user ID and the blog post that was being favorited. And with those two bits of information, it could create a new record to mark that as a favorite. When MySQLI Query runs with an insert statement, instead of returning a record set, it returns only true or false, letting you know if it succeeded or failed.

Now when we do a new insert, we don't always know what ID MySQL assigned to that new record. And it doesn't return it to us when it returns back a value, it only returns true or false. So the way to find out what ID was created is to use another function called MySQLi insert ID. And then you provide the connections and argument. It basically says, hey MySQL, I just gave you an insert statement, you just created a new record. I'd like to know what ID you assigned to it. Can you tell me that. And that's what it does, it returns it as a separate call.

So after the record's created, if you need to know what ID it was, this is the way to retrieve it. So in my project, for subjects, I already have a page for create new subject. And I have a form here that I can submit, and once I fill it all out, I can hit create subject, it sends that form over to create.php. Let's remind ourselves that that's what we had done here. In new.php, I have some code up here at the top which I'm actually just going to remove, we don't need any of that code, so let's just remove that out of there. And let's save it. And then down here you can see that the action on my form is to go to create.php.

That's different than what we did on some of our other forms. Later we learned about single page form submission, this is two page form submission. The new.php is going to submit to create.php. And here's what create.php looks like. It's just a form processing page, it doesn't display any HTML, all it does is say hey, was this a post request? If it was, process the form. If it wasn't, then send the person back to the form. That's all we have there. So what we want to do now is change this code. If it's a post request, then, let's not just display it, let's actually send it to the database.

So let's make those edits. We still want to have all of this code here where we're setting up the variables and initializing them. But what we want to do is change this so that we're constructing SQL. So let's change this to instead be SQL equals, and let's remove what's in here. And let's start writing our SQL. Insert into subjects, and then a space, and then I'm actually going to go to a new line, SQL dot equals, and then let's put in the names of our different columns. So we have menu name, we have position, and we have visible.

Notice that I'm leaving out ID, I'm not providing the ID. And then I need a space at the end of this. And then let's start a new line, SQL, equals, and this one is going to be values, space, and I'm going to open my parentheses, and then I need to actually have my values. So I'm going to change these down here, just going to copy this real quick. Let's replace all these echoes with that. There we go. Now instead of menu name, this needs to be, single quotes.

Single quotes around menu name. So that's going to be the first one. Alright, just a single single quote. And then we need a comma at the end. Now even though this is broken up on separate lines, don't let this confuse you. I've got single quotes around the value, here's the value, another single quote here, and then a comma at the end of the line. A space is optional, you can put a space here or not, it doesn't really matter. So let's do the same thing for all of those. Just a single quote, and a single quote with a comma.

And the same thing here. Single quote, now this last one, it's very important that we do not put a comma at the end. It's the last one, there's nothing after it, we'll get an SQL error if it has a comma there. So do not put a comma after the last one. And then, let's close our SQL, because remember we have to have that closing parentheses at the end. So values is from here down to here. Now I broke these up into separate lines because later we're going to be working with them more. That's the reason why I did it this way, because this is a good format for some of the work we're going to do in the future.

Now, notice that I used single quotes, not only for the string of menu name, but also for position and visible. That's not strictly required by SQL that we have single quotes around those. It allows us to just pass in integers in place of that. But for security reasons, it's a good practice to always put single quotes around those values. We'll talk about SQL injection later on, and we'll come back to that topic, but for now, always put single quotes around all of the values that you submit to SQL. Okay, so now that we have all of that, now, we can simply pass it in to MySQLI Query.

We'll get back a result from MySQLI Query, we're going to pass in that database connection, DB, and let's pass in the SQL. Let's make sure up at the top I did initialize up here, wanted to make sure of that. So I have all of that available to me. So that's going to return a result. And if you remember I told you that for insert statements, result is true false. So, down here, we can check and see whether it succeeded or not.

We just check and see if result, and if it's true, then we'll know that it worked, otherwise, we'll know that it failed. So let's first do if it failed. We'll say insert failed. So in the case that it failed, we can echo back MySQLI error. This is a new function that we haven't talked about, but it basically just says, what was the error on the last connection. Not a connection error, just a general error. Just report back what was the problem, and then let's call our DB disconnect, which we already wrote as a custom function to just disconnect us, and we'll call exit, just to make sure that nothing else executes.

We're just going to bail out at that point. So display the error message, disconnect the database, and quit everything. Alright so if it succeeds, then what do we want to do? In that case, let's find out what that new ID is. We can use that new function we just talked about, MySQLI insert ID, DB, and then, let's redirect the user to, and let's do URL for subjects, and, oops I forgot staff in front of that, sorry, staff, subjects, and then, show.php, and show.php needs to know the ID.

Which is why we just went about and found that new ID. So now we can provide it. Now we can say, go to the show page for this. Right, we just created it, now show us, show us the detail page for this. We also could redirect back to the list or something if we wanted, but I wanted you to see how you go about getting this new ID when you need it. Alright, so let's save it all, let's go back, and let's try it all out. We'll go here to our form, and let's create a new form. The form I'm going to create is Commercial, I'm going to give it a position, that only has one as a position right now, we're going to need to come back and work on that a little later, for now we'll leave that, and let's say visible is going to be equal to one, and let's click create subject.

There we go. You can see that it created the new subject in the database, and then it redirected us to show.php with ID five, and you can see that it's there, it's in the database, we know that because we're seeing the detail page that we just created in the previous movie. Now before we leave this, I also think that we can take this and move it all to a function. Let's just grab all of this code and I'm going to copy it, and let's just come over here to our query functions. You can see that we have find all subjects, I have find subject by ID and I have find all pages, I'm going to add a new one here, function, which I'm going to call insert subject.

The idea is, if we pass it in the menu name and we pass it in the position, and we pass it in visible, that then we should be able to create a subject. We'll need to make sure we have access to the database variable, and then we can take all that code that we just created, and all those will just get dropped right in place to create our SQL and we'll get the result. Now, instead of having it redirect here, I actually think it's a little bit better to take that bit out, and if it works, just return true. That's going to create the subject, and then, back over here on our create page, we take all of this out.

We'll just say, result equals, and we'll say, insert subject, and we'll pass in all those values for menu name, position, and visible. And then, we'll ask it for that new ID and redirect here. So this is just going to take care of the insert for us, actually figuring out what the ID is and redirecting we're going to leave here. And that's because in different circumstances we might want to redirect to a different place. Or we might want to create it and not find the ID.

So I'm not going to build that into my function. Alright, let's try it out one more time and just make sure all of that's working as well. Let's click back to list, let's try create a new subject. I'm going to call this one Junk. Let's create our subject, and there we are. Now we're looking at our subject Junk. So now we've packaged up all of that creation logic, and moved it to a reusable function.