10.4 Joining Data (And Deleting Posts)

If you remember from whenever we first started talking about SQL and relational databases, I said that many developers don't like SQL and or relational databases. And we're going to kind of see why in this lesson. Because with a relational database, our data is kept separate from one another, like for example, we have our posts table and we have our users table. These are two completely separate things. And logically we would think that this information would be combined, so that whenever we retrieved our post information the author information would be just right there with it. So it would be there ready for us to use, and vice versa. If we retrieve the user information, the post information would be there as well. But with a relational database, that's not the case. We have to do what's called a join in order to get all the information that we want. And in this case, we are going to be able to retrieve all of the information with one query. But sometimes you can't do that, especially when you have many tables, many relationships and especially if you want something a little weird as far as the end result is concerned. And you would be surprised at how often that that would be. But the goal is to get your data in as few queries as possible. Because any time that you send a query to the database, that's going to take extra time. So of course, the less queries you issue, the quicker you're going to have your data and display that to the user. So we're going to start by taking our query that we used inside of get post. And we're going to store this within a variable because we're going to format this so that it will be a little bit easier to read. So I'm just going to say SQL equals, and then our SQL statement. Then I will pass SQL to the query method. Okay, so the first thing is we want to select all from posts. We aren't going to change anything there at least right now. So let's put this on a single line. And then we will put the rest on another. So there is our WHERE clause. And let's just use double quotes here so that we can include the id without having to concatenate. And we're still going to end up with the same results right now. However, whenever we pull in the user information, we're going to use what's called a join. And there are several different types of joins with a SQL database, one of those is called inner join. Now there's a left join and right join. And I'm not going to explain what all of these joins are because frankly, it gets a little confusing. But an inner join is what we want to use here. Basically, it's going to match up. And only match up the posts where the author id equals the users with an id. So when user's id is one and posts author id is one, then that is going to be a join. And an inner join is usually the join that you use within your applications. Although sometimes you would want to use a left or right join and I'm getting into that and I'm not going to get into that. Okay. So we are saying that we are selecting everything from posts, but we want to join in the users table. So we're going to say inner join users. That's the table that we want to join. And we have to specify what we are going to join on. In this case we're going to join on the posts, author id is equal to the user's id. Now, why am I using the table name and then a dot and then the column name? Well with posts, we don't really need to do that. But if we look at our two tables, we have a Post's table that has an id column, and then we have a user's table that has an id column. So you could say that there is a collision as far as the names are concerned with our column names, and that's common. So don't worry about that. Yes, we could avoid the collision by using a unique id name for those columns. But this is a common thing. This is why this type of syntax exists. So basically we're saying users.id because we want to specify that we are using the id column on the user's table. In fact, if we left the users off. Well, the MySQL database is going to say, well, I'm not gonna do this. I have no idea what you mean by id posts and users have id. So, we have to specify users here. Now we don't have to specify posts for the author id, because author id is specific to the posts table, there is no naming collision there. But this is just a habit of mine, If I'm being explicit here for the id column, I also want to be explicit here, but that's just my preference. If you wanted to leave off posts.in front of author id that would be fine. Now, we also have to do the same thing down here, where we say id equals to our id parameter to the method. Because, once again MySQL doesn't know what id column we are referring to, so this is for displaying a post. We are going to say, posts.id. And now if we go to the browser, we will refresh and we aren't going to see anything different because we didn't use the author information here, but at least we're not getting an error. So that's a good sign. Let's go to post.php and let's do a var dump on our post variable because we're going to see something a little interesting. So let's change, well, no, let's not do this. We're just going to refresh this page. And here we see the dump of our posts variable. So we start with id. And then we have the value. One, we have title and then we have the value, this is the second post. You have the content and then the value here. This is the edited content of the second post hooray blah blah. Now what I want to point your attention to is this id. This is the id property on our post object and it is set to one. Now this would logically be set to two because we are displaying the information for the post with an id of two. However, since we had this naming collision, and because we said select all of the records, well, we have essentially lost the posts id field at least as far as our post object is concerned. So this is okay in this case, but if we wanted to use the id property and refer to the posts id column, we couldn't do that here. Because id, or at least the id property, and I'm kind of confusing myself. But this is what I mean the id property. This is referring to the id of the user and not the post. So what we can do is we can be a little bit more explicit in what we are selecting. So instead of just selecting everything because this is going to select both everything from the posts, table and the users table. We'll say that we want to select everything from the post table. But from the users table, we just want first name and last name and that's it. If we wanted their email address we could also do that as well. Now we could come in here and we can say users.last name and users.first name if you wanted to do that. But in this case, I'm not going to, which is interesting considering that I was explicit here for the author id. But this is then going to filter down all the information that we have within our object. So that now the id property is going to refer to our post id and not the user id. And that's what we want. So now we just need to output that information. And we're going to do this underneath our title. Let's use an h4 element. And let's do this. We will say that we want our post and then first underscore name, and then post last name. So that will give us the author's name if we refresh the page we now see all of that information but let's get rid of the dump on our post variable. And now we have what we would expect. Well now let's add the ability to delete a post. And let me first of all say that our implementation here is not very secure because anybody is going to be able to delete anything. But really everything that we have done this far except for reading the data would be hidden behind some administration panels. So there would be user authentication and authorization. And that is a whole nother ball of wax. So in this case, we're just going to take the easy way and say that everybody's an administrator. So what I'm doing is adding a delete link right after the edit and we are essentially going to do what we did with edit except without any of the form information. So we are going to pass the id to this delete file, which let's go ahead and create delete.php and we need the includes for the database and the get helpers. So let's just copy that and paste it, but we need a function for deleting a post. So let's go to database.php and we'll say function, delete post, we will accept the id and we want to have our database objects. So we will call the getDb function. And then we will just simply say db query. And our query is going to be rather simple just like everything else. It's very easy to delete with SQL. So we say DELETE FROM posts, where id is equal to Id and I should have stuck with the double quotes there. So that's it. There's no asking do you really want to delete this? It's just going to hey, delete this and we are done. So let's go to the delete php file. We are going to call the delete post and passing the id, and then we want to redirect. So let's open up the Edit file. Let's copy our call-to-header. And in this case, we are just going to go to local host and we don't need anything else there. So this should work. If we go back to the browser and refresh the page we will have a delete post link. Let's click it. And there we go. We have just deleted a post, we can see that it doesn't exist there. And if we looked at the database, we would see that that record is gone as well. So with a relational database, when you want to select data from multiple tables, you use what's called a join. Inner joins are fairly simple and straightforward. The other joins, not so much. But don't worry about those other joins until you have some experience and you feel confident with SQL because frankly, you're not going to need them with your initial applications. When you start building more complex things that do more than just crud then you might see yourself needing and using the other joins.