2.2 Reading Data

In the previous lesson, we wrote the code that inserts data into our people table in the address book database. So we are creating content and the code works. It's great. Well, it's not great. We're not done here because we do need to do some validation. And we could refactor this to make it a little bit easier, especially when working with the database stuff, but that will come later. For right now, I want to write the code that is going to read from the database so that we can display that information inside of the index.php file. Now we will follow the same pattern that we used for add. In that we will have the index.php file and then we will have the code that processes the data inside of the app folder. And it will be called index.code.php. And this is going to be much more simple then the add code because we don't have to worry about the type of request. We don't have any data that we need to read from the request. We just have to grab data from the database and then output it. Pretty simple stuff. So we're going to start by grabbing the code for creating the PDO object. We're gonna paste that into index, and then we want to perform our query. So we will use the db objects query method, and then we will define our query. We want to read from the database. So we are going to select data. We're going to select all of the columns from people. Now, some people like to be explicit when it comes to the column specification here. So they would have ID and then first name and then last name, and then email. And then if there were more columns, they would add all of the column names. That's great, if you want to do that. We need them all, so I'm just going to say select all from people. And then we want the data from that query. And I'm gonna store this in a variable called a model in the spirit of model view controller. In that programming pattern, the model is loosely the data that we work with. So here we are going to use our query object, and we will fetch all of those records. So that's going to retrieve everything that matches our query, which is very simple. We're just selecting everything from people and that will give us our model to work with. So inside of index, we need to reference that code file. So we will require app/index.code.php. And then we are going to iterate over our model because our model is a record set. It is an array of all of the records in our people table. So it makes sense to iterate over everything, so that we can see that. And we're going to use a table here. Let's go ahead and define a thead, so that we can put our headings here. And let's have the last name first. So this will be Last Name and then we will have the first name and then the email address. So let's just copy and paste a few times, make the necessary changes, and we will be good to go, there. So now we need our tbody and inside of the tbody, we will have our foreach loop and we want to loop over our model. We'll call each item in our model, just item. That's what I typically do. If you like some other variables, some people might like record or data or something along those lines. I typically just use item. And let me go ahead and finish out the foreach because I will forget to do that. So endforeach, and then we will simply display that information. So we will have a row for every record in the database and we want to display the last name first. We will use item, we will specify the last_name key and that will give us the last name. And then once again copy and paste so that we can get the first name, and then finally the email. And if we hop on over to the browser, if we go to index.php, we will see our table, but it's not looking very nice, is it? Let's inspect. Let's make sure that Bootstrap is coming in. Or do I even have Bootstrap? No I don't. So let's copy that from the add file and that will make that look a lot better. So let's refresh. There we go. We have our table of names. Now, of course, we only have one record. So let's add a few more. Let's add Jim, Jeffers and his email can be jim@jeffers.com. And whenever we submit this, we have a choice. We can stay here on the Add Page. But typically what you would do is redirect to another page. The page that would more than likely display the list of all of the people. So let's go ahead and let's do that. So inside of add-code.php, after we set the statement and DB as null, did we do that inside of index? No, we need to do that. So let's do that very quickly, so that we don't use more resources than we need to. And let's add the location header. And actually, I guess we need to call header first, we'll set location to index.php, and that should work. So let's go back, let's add somebody else. Let's add Jane Smith and her email address can be jane@smith.com. Whenever we submit this, it should redirect us to the index, and it does. Now one thing that we might want to do is order our list of people by last name, which well that's currently being done because that just happens to be how we ordered them. So let's add one other person, and this can be Johnny Appleseed. So that he will be out of order and we'll have an email address for him. And we want this ordered according to last name. Well, we can easily do that with our query, so that we can tell the database give us all of this information from our people table, but order that by the last name. And the database will do that, and that will be great. Now, yes, we could have done that with PHP code, but when it comes to ordering data, in most cases, it's much more efficient to let the database do all of that work for you. Now, if you have a tonne of data that you're working with, and buy a tonne, I mean tens of thousands, hundreds of thousands, a million records. And that sounds unattainable, but really, I used to think that I work with databases with that many and more records. So unless if you're working with just a ton of data, let the database do the work for you. Otherwise, it might make more sense to do some of that in PHP. It really depends upon your application. How does it perform? And then you can make your own decisions there. But here we go. We have our list of people ordered according to last name that will make this a lot easier to find individual people. Well, in the next lesson, we are going to add the code for editing or updating the data in our database.