Next lesson playing in 5 seconds

  • Overview
  • Transcript

10.1 Reading and Displaying Data

One of the most common things we need to do in web apps is to read and display data. In this lesson, I'll teach you how to connect to your database, read data from it, and display that data in the browser.

10.1 Reading and Displaying Data

Now that we finally have some data in our database, we can start to query our database and display data within the browser. So this is where we finally start getting into PHP so that we can interact with our database. So we're going to need two files for this lesson. The first is index PHP, this is going to be what is going to display our data to the user. The second file is going to be where we do all of our database stuff. So this is where we are going to connect to the database, and then perform our query. Now, the first step in doing anything with a database is connecting to it because you can't do anything if you aren't connected to it. So in order to connect to a database, we need a few pieces of information. The first is the computer where our database lives. And this could be an IP address or it could be a hostname. In our case, we're just going to use a local host because that is where our MySQL database lives. Now, the variable name doesn't matter. But host is what you would typically see. Now, we also need the username and the password. Because not everybody has the ability to see the data within our database. They have to be authenticated in order to do anything with our database. So we need the username and password. The default username and password is root. At least as far as our MAMP installation is concerned. This is definitely something that we would want to change but really in a production environment, we wouldn't be using MAMP anyway. So root for the username and password is going to be fine. The next thing we need is the name of the database that we want to work with. And that is our blog database. So those are the four things we need in order to connect to a database. So, let's write a function that is going to do all the work as far as connecting to the database. It's going to perform our query for retrieving all of our posts, and then returning them to whatever called the function. So let's just call this get posts. And we want to reference these variables inside of our function. But we need to do that by using the global keyword. We're saying that okay, we want to bring in these global variables. So we want to host, we want to username and password and DB name. We need all of these so that we can then connect to our database. And we will do that with a variable. Some people use DB, some people use con, some people use connection. It really doesn't matter as long as you're consistent. I like to use DB because it is short. And whenever you connect to a MySQL database, well, you can do so in a variety of different ways. You can use an object oriented approach which is what we are going to be using. Or you can use a more procedural approach where you just call a bunch of functions in order to do the things that you need to do. Now, the reason why I like the object oriented approach is because I like object oriented programming. So this is probably going to be a little foreign to you unless if you have some experience with other object oriented languages like JavaScript. So here we're going to create a new object called MySQL i. So we are going to say new, and then MySQL i. And then whenever we call this constructor, we need to pass in all of our database information. So the first is the host. The second is the username. The third is the password. And then the fourth is the database name. So now we have connected to our database. And the next thing we want to do is actually perform our query. So we will do that with DB, and DB is an object. And if you're not familiar with object oriented programming, it's rather simple, at least on a fundamental sense. Because we work with objects on a daily basis, not just in programming but in real life. Just pick anything and that is an object, and object has properties. Those are things that typically describe the object. Like, for example, the keyboard that I'm using, the keyboard can be an object. It has several properties. One of those is that it's black. It has a chiclet style keys. And so we could keep going on and on about the different properties of an object. But an object also has methods. These are things that the object does. And in our case, our database object, we have a method simply called query. A query is well, it's a query. We're going to request data from the database server and then we need to specify our query. Now a SQL query for retrieving information or reading information is select. If you'll remember from the previous lesson we talked about CRUD, create, read, update, and delete. We are reading data from our database and that is the select command. Now whenever you select information, you have the ability to choose the information that you want to work with. And that information is columns. So we could specify all of the columns that we wanted to read. And we could do that very simply by saying that we want ID, we want title, content, and then date created. And that would be fine, but usually if you want to retrieve or read all of the information within a given record, we can just use an asterisk. So select, and then asterisk. Then we need to specify where we want to select the data from. So we use the from keyword, and then a table, and that is our posts table. So, here we are giving a result by issuing a query to our database. And our query is to select all of the columns from our posts table. But this isn't going to give us the actual data. What we need to do then is fetch the data. And we can do so in a variety of different ways. But in our case, we're going to do what's called fetching an object so that we can still work with our data as they are objects. You can also retrieve them as an associate of array. So that you could use a different syntax to get to the data that you want. But since we're doing everything object oriented, it kind of makes sense to do that. Now, we're going to use a while loop because basically what we want to do is iterate over every item within the result. Our result is going to select all of the data within our posts table. So right now we have two records. So this while loop is going to execute two times. If we had ten records, it would execute ten times. If we had 1,000 in well, you get the idea. So we're going to iterate over our results, and we're going to call each row, row. That just kind of makes sense. We can call it whatever we wanted to, but row is going to be fine. And we're going to use our result, we have an object here, and we're going to use a method called fetch_object. This is going to give us our data as an object. Now in our case, this is a function that we are going to call. So really what we want to do is create an array. An array is a collection of data. And that's really all it is. And we want to create an array, we want to populate that array with our data from the database, and then return it. So let's create our array and let's just call this records. And we'll just say, array. A way to think of an array is kind of a database table in memory. Now that is an oversimplification. But in this particular case, that's kind of what we're going to be doing. And so inside of our while loop, we're going to say records equals our row. And that is going to populate our records array with each row that we are retrieving from the database. And so then we will finally just return our records. So this is our function. We are connecting to the database. We are reading data and then returning that data from the column. So let's go to index PHP. And the first thing we want to do is include our database.PHP file because we're going to use this in order to retrieve all of our records. So let's save our posts equals and we're going to call getPosts. That way this file is fairly clean. And then we just want our markup. So let's have an HTML tag. Let's have a body tag. And yeah, we need to do a few other things to make this valid, but this is going to be fine. So inside of our body, we want to just display all of our data. So we want to take our posts, we want to loop over them once again, and then we're going to output the information. So we need to switch into our PHP mode here. So, inside of our HTML, we're just going to embed some PHP, we're going to use the foreach loop. And we're going to say for every post as, I'm sorry, posts as post, so we're going to go over each item within our posts array. We're going to call each item post which makes sense. And then we are simply going to display that data. Now switching in and out of PHP and HTML, it is a little cumbersome, but in this case it's going to get the job done. So now we are going to output the title. So we're going to use h2 elements for the title. And we're going to use a paragraph element for the content. So for every post, we're going to have a title and the content. And now we just need to output that information. So we will have the shorthand syntax for outputting a value. We are going to use our posts and the title property. Our column names are properties with our object. And we will essentially do the same thing for our content. So we will just go down to our P element, and instead of the title we are going to output the content. So let's hop on over to the browser. Let's refresh this page, and there we have our data. This is the first post, this is the second post. And unfortunately, I used the same content for both of those records. So let's change that, let's head on over to PHP myAdmin and let's change the content of our second post. So instead of just saying that this is the content, we'll say that this is the content of this second post, hooray. And we will save that. We will go back to Chrome. Let's refresh the page. And now we can see that we are definitely pulling both of the records that we have from the database. In the next lesson, we are going to build upon what we did in this lesson. We are not only going to display all of our posts on the index page, but we will also display each individual post on a separate page. We're going to be passing data to that separate page, and we're also going to be validating that data.

Back to the top