9.3 Inserting Data

I have yet to meet a developer who likes SQL. It is just something that most developers don't like. And that might seem a little weird, but the more that you use SQL, chances are the more you're going to dislike it. Now that is a generalization, I'm sure that there are some developers who like to use SQL, but most do not. So, I'm just going to throw that out there. So, if you start to see this and you think well, I don't want to do that, well tough because this is how we do things. However, there is a trend that is called ORM. It stands for Object Relational Mapping. Basically it's a framework or a library that hides all of the messiness of SQL so that you just use pure code to interact with your database. So, in our case, we would just write PHP and we wouldn't have to worry about any SQL at all. The object relational mapping framework is going to handle all of that for us. But unfortunately, trends change, fundamentals do not. So, I'm going to show you the fundamental commands for interacting with the database. And in this lesson we're going to start with what's called INSERT. But before we get into that, we have four main commands, and they are for Creating, Reading, Updating, and Deleting. And if you take the first letters of those words you end up with CRUD. So, as you're reading in the articles or books and you see CRUD in all uppercase, that typically means create, read, update, and delete. Those are the four main things that we do with any data store. It's not just SQL, but it's any data store. So, as far as creating in a database or a SQL database, we have the command INSERT for reading, we have SELECT. For updating we have UPDATE, and then for deleting, we have DELETE. Now, I'm using all upper case here, but SQL is not case sensitive. So, these could to be in all lowercase, or you can have an upper case letter to start the command. And then all lowercase after it doesn't matter, it's case insensitive. However, the tradition is that we specify all commands and keywords with uppercase letters. That way we can easily identify what it is inside of a SQL command because basically we're going to build a command with several words. And in the case of an insert command, there are several different pieces involved with that command. So, I'm going to use all uppercase for any command or any keyword. So, when it comes to inserting data, we are inserting data into a database table. So, we need two things, we need where we want to insert that data into and the data that we are going to insert. So, we start with the INSERT keyword. Then we use the INTO keyword. So we're inserting into whatever table that we want. We're going to INSERT INTO our Posts table. And then we specify the columns that we are going to insert data into. And in our case we have title, we have content and we have author id. Now we have some other columns as well. We have id and we also have date created, but we don't have to specify values whenever we are inserting for those columns. Because they have a default value. The date created has a default value of the current timestamp. And the id is going to be automatically incremented whenever we insert a new record. So, we don't have to insert any data for id, or date created. But for title content and author id, we do. So, we have INSERT INTO Posts, we specify the columns we are going to insert data into. And then we have a keyword called VALUES and then after VALUES we have set of parentheses. And inside of these parentheses are the corresponding values. Now the order in which we specify the columns doesn't matter. If we wanted to specify the content first and then the author id, and the title we could do that. However, the values have to match the order in which we specified the columns. So, just keep that in mind. I typically go with the order of the columns in the table, that's just my habit and my practice, you can come up with your own. But the main thing to remember is that our values have to be in the same order as our columns. So, our title, we want a title and we can say that, This is the First Post. Now a string in SQL uses a single quote, it is not a double quote. So, strings are single quoted. So, we have our title, we have the content, and we'll just say that this is the content. Now I am separating each value with a comma, just like we separated the column names with a comma. And the spacing doesn't matter, you can remove the whitespace if you want to. But I typically use whitespace just like I would if I were writing code. So, we have our title, we have our content. And now we need the author id. Well right now we just have one author, and that has an id of 1. Now you could have a semicolon at the end of a SQL statement. That signifies that this is the end of the statement. So, that if you wanted to execute another statement, you could. But in our case this is all we're going to do. We are going to just run this insert command. So, let's copy this. And let's head on over to phpMyAdmin. And we are going to go to this SQL tab. Now I am in the posts database. So, let's click on SQL. And you can see that it's automatically populated this with a SELECT command and we will go over that at a later time. But I'm just going to paste in this INSERT command and we are going to press the Go button and hopefully everything is going to go as it should. It does. It says that one row was inserted and If we go look at the Browse tab, let's do that. Here we see our data. The Post has an id of 1 that was automatically added by the database. We have a title. This is the first post. We have the content. This is the content, the date created, that is the timestamp that was used. And then we have the author id. Now if we wanted to specify a date, we could do that. We don't just have to say that we will rely upon the database to do that. So, let's go back to the SQL tab. Let's paste back in our INSERT. And we're going to change the data here because this is no longer the first post. This is the second post we'll still say that this is the content but we also want to specify the date created. So, we will add that to our column list. That is after the content. So, as far as our values are concerned. In between the content and the author id, we want our date time. So, in this case, let's have 2016 for the month. We will say May. And then for the day the 22nd. So, it is year dash month dash day. If we wanted to specify the time we could do that as well with a space. And then let's say that this is 10:01 in the morning, and we can have the seconds there as well. So, if we click on Go again, this is going to insert that data. And if we go back to the Browse tab, we can see the results. We have a second record, it has an id of 2, let's zoom in. This is the Second Post. This is the content. Then it has the date and time that we specified. And so, now you know how to insert data into a table. In the next lesson, we're going to talk about the SELECT command. And we're also going to start incorporating PHP. So, we are going to select data and display that data in the browser.