Define a create method that will automatically convert an object's properties into an SQL INSERT statement. Send an admin's values to the create method using a web form.
- [Instructor] In this movie we will learn to create a database record using the properties of an existing PHP object. In order to do that we are going to enable the Add Bicycle page in the content management system we are creating. We already have a form here. We want to be able to fill out this form and click Create Bicycle and have PHP gather up all of the form values that we've just submitted, create and object from it in PHP, and then tell that object that it should save itself to the database.
That's that active record design pattern that we've talked about. Let's go to the code and let's take a look at that new.php page. You can see that's is already got code in here that's going to call initialize.php so we will have a database connection and our bicycle object will know about that database connection, and then we've got code here that says if it's a post request, if we're getting back form parameters, then let's build an associative array called args and it's going to go through each of the post parameters and it's going to set the brand equal to whatever value was submitted in the form for brand, and same thing for model, year, et cetera all the way down the line.
So then we're going to have a set of arguments. Right now I just have a place holder here for bicycle. Bicycle equals an empty array. That's now what we want. What we want is to create a new object from it. We know how to create a new object. We just have new, and then bicycle. So that'll create a new bicycle object. Now what we want to do is have the bicycle object have its properties set to these same values that are in our argument associative array. If you remember, our bicycle class has that ability already. If we scroll down 'til you get to construct, you'll see that when we wrote our construct function in the object oriented programming class, we allowed it to take an argument that is an array.
That's called args. So it's going to take those args that are passed in and it's going to set the property for brand equal to whatever value is in that associative array. So we have this functionality already built into here. All we have to do is use it. So if we come back over to new.php, put parentheses after bicycle, and pass in that array of args, then that'll automatically be sent to our construct method, and it will set all the properties that we need all the way down the line. Okay, so at that point we now have a PHP object in memory that has the correct properties.
But that doesn't implement the active record pattern that we've been talking about. What we want to do now is take that in memory object and tell it to save itself to the database as a row in the database. And we want to do that by just simply calling bicycle and then call create. So there's this create method that'll be on bicycle, and it will know how to take all of those properties of the bicycle and turn them into an SQL query that can be submitted. So we've got to write this method called create.
Let's save this, let's switch over to the bicycle class, let's scroll back up to where we've got our active record code, and right here at the end let's create a new public function called create. Now this is an instance function. We have an instance of the bicycle here. We created a new bicycle, so it's an instance method, not a static method. When we were working with find all and find by id we didn't have an instance yet, so we had a static method. This time it's just going to be an instance method, a method on the instance that we've already created.
So how are we going to create this? What does it need to do? What's the process that we need to go through? Well if you think about it we need some SQL, and then we need to send that SQL to the database. So let's have our result equals self database. The bicycle class knows about the database because it has it stored in the database property. And then we can tell that database that it ought to query using our SQL. So if we can construct a good SQL for this, that's all we need to do.
And then last of all, let's just return back the result. The result for an insert operation will either be true or false. So our create will either return true or false, depending on whether or not it succeeded or not. So now how do we write that SQL? Just as a quick review, remember that this is the structure of an SQL insert statement when we want to create something. So we've got insert into and then our table name followed by a comma separated list of the columns. And then after that we have values, and then a comma separated list of the values that we want to put in each of those columns.
And they have to be in the same order as we define the columns. It's going to match them up. So let's start writing it. We've got insert into bicycles space. I'm going to go to a new line here. I'm going to pend SQL onto it. And we're going to have all of these different properties. I'm going to leave off id. I don't need to add id. But all the other ones, brand, model, year, et cetera, those all need to be in here. So brand comma space model, comma space year, comma space category, comma space color.
And the order doesn't exactly matter. I'm actually going to leave description 'til the end. Gender price wait underscore kg condition underscore id, and then description at the end. Again, the order doesn't matter as long as it matches up with what we've got in the values. Now, those need to be inside parentheses. I like to put my parenthesis, start it on this line, and then SQL, and I'll end it on this line, and then we've got values.
And I'll start my parentheses there for the values, and I'll just go ahead and put the ending parentheses here before I forget. And then in between I need to have SQL that has all the different values. So what does one of those values look like? So the first one is brand. So I need to have brand. And I know that this brand is the way that I access that property. It needs to be inside single quotes, so I'm going to put a single quote at the beginning here, just one, and also add a single quote at the end.
That'll pend those together. And then it should have a comma after it because I'm going to have another one following. So that's what each one of these looks like. So now I can just take that line and paste it a whole bunch of times. And I've got model, year, I need to do these in the same order as they are above. Category, color, gender, price, and I need three more. Weight kg, condition id, and description.
And the last one should not have a comma after it because it's the last one. So that is the key step in this. The ability for my object to have enough intelligence to know, how do I translate my properties into an insert statement so that I can submit it to SQL, so that I can have that active record feeling between my object and my database record. We're not quite done with this, though. Because if you remember, when we create new records in the database, we don't submit id because SQL is going to automatically assign an id to it.
So, the database record is now going to have an id once it's created but my object is not. They're actually going to be out of sync. I need to get that id from the database. And SQL provides a way for me to do that. So I'm just going to add a line here that says if we got back a result then let's find out what that id was and let's assign this id equal to that value. An the way we do that is by asking the database connection for the property insert id.
We talked about that in the last chapter. That's one of those properties that's built into the database object. What is the last insert id. So that'll set it and now my two objects will be in sync. And then I'll return back the result. Let's save that. Let's switch back over to new.php and finish up over here, because we don't want to just create it, we want to also find out what the result was. Result equals, now I don't need this result. If the result is equal to true, then what is my new id? Well, my new id is going to be the bicycle id that I just found out.
And then it will store a message for the bicycle was created successfully, and it should redirect me back to that detail page. Let's save it and let's try it all out, see if it's working. Let's go back over to our page. Let's first reload it to make sure that everything's still working. It is. And now let's try entering some data. So we've got Schwinn, Cutter, and I'm going to choose 2016 from the list. The category's going to be city. The gender is going to be unisex.
The color will be white. The condition is going to be great. And the weight is going to be 18. And the price is going to be 450. I'm not going to put in a description for now. Let's hit create bicycle. And there you go. It added it, and we know that it added it because it redirected us to that show page which is reading back values from the database. If we click on back to list, you'll see there it is in our list as well. So that does the trick. In the next movie let's see how we can improve this a bit.
Note: This is an intermediate-level training course that assumes you have existing knowledge of PHP. To refresh your skills, check out PHP Essential Training and PHP: Object-Oriented Programming.
- Creating a project database and tables
- Connecting to the database
- Database queries with OOP
- The active record design pattern
- Defining a database-driven class
- Performing common database tasks with OOP
- Creating inheritable and reusable code
- Object-oriented user authentication
- Object-oriented pagination