‫In the last video, we connected to our database using the credentials that we set up through workbench
‫or through the shell.
‫However you did it and now I want to use this PDO object or this PDO instance to fetch data from the
‫database from that that blog database.
‫Now usually what you'll do is, is your connection.
‫So what we have here, you'll put in a separate file and then you'll bring that file in using either
‫include or require or require once.
‫So these allow us to include files into one another.
‫So what I'm going to do is instead of having this in the index dot PHP, let's create a new file called
‫database.
‫Dot PHP.
‫And then let's take everything from the index PHP and put it into database.
‫All right.
‫Now what I want to do is now clear out index.
‫And I want to bring that database file in.
‫So.
‫Like I said, there's a few ways we can do this, and I'm going to go more into this when we get into
‫our project, because there's a lot of different files we'll be working with and bringing in.
‫So I'm going to use the require statement here.
‫And I'm going to say require and then database dot PHP.
‫And I can just put the file name because it's in the same location as my index PHP if it were in, let's
‫say a folder above where the index PHP is, and I would have to do dot dot slash database PHP, I would
‫have to go up one level.
‫If it was two levels up, I'd have to do that.
‫Okay.
‫Now doing this is the same as just having all this code in the index file.
‫And I know a lot of you guys already know this, but this is for the absolute beginners.
‫So if I save this file and I reload my index page, I'm still going to see database connected even though
‫it's in this file.
‫Because it's essentially you can replace this with this.
‫All right.
‫Now we don't actually want it to say database connected obviously every time we come to our page.
‫So let's get rid of that echo.
‫I just wanted it there so that so that we knew that we were actually connected.
‫Now what we're going to do is just get the HTML from the starter code for this lesson.
‫And I'm going to paste that down here.
‫Let's close the PHP.
‫And then I'm going to paste in the HTML.
‫And I'll save this and reload.
‫And you can see that what we have is basically the same layout we've been working with.
‫We just have this top header.
‫And then underneath that we have a container that has two divs, this one right here and this one right
‫here.
‫And each one represents a blog post that has an H2 with a title and a paragraph with the body.
‫And they're just hard coded.
‫And obviously this isn't coming from the database or anything like that, but what we want to do is
‫make it so that it is coming from the database.
‫So we'll go up to the top here and make sure you go under where we're requiring, requiring database
‫PHP, because we need access to that PDO instance.
‫And if we go above this then we're trying to access it before before it's included here or required.
‫So the first thing we want to do is prepare.
‫A select.
‫Statement.
‫Okay, remember I told you that paedo uses something called prepared statements?
‫And this is extremely important.
‫And for for a lot of reasons, mostly for security, because what it does is it allows us to write secure
‫queries because it separates the SQL command from the data, and it reduces the risk of SQL injection
‫attacks.
‫All right, especially when you're inserting data.
‫And I'll get to that in a little bit.
‫So what we do what we need to do here is prepare a select statement.
‫So let's create a variable that represents the statement.
‫So a common a common convention is to use Stmt.
‫You'll also see star.
‫That's another common convention.
‫But you could call it anything.
‫You could call it statement.
‫So I'm just going to use Stmt.
‫And then what we can do is take that PDO instance which is accessible to us because it's right here.
‫Right.
‫And this database file is being included.
‫So we want to take that.
‫And there's a method called prepare.
‫And in this prepare is where we want to write our SQL.
‫And what I'm going to do is just select all.
‫So just like we did, we did in the shell just like we did within workbench, we can just write SQL
‫queries.
‫So I'm going to say select all from posts.
‫All right.
‫Now that gives me a statement.
‫It doesn't actually execute it yet.
‫So what we need to do now is execute the statement.
‫And we do that with the execute method.
‫So we can say stmt or whatever you called that variable and then execute.
‫Okay.
‫So that will execute it.
‫Now we just need to get the results.
‫So we'll say fetch results and we can do that.
‫Let's put it into a variable called results.
‫And we take our statement.
‫So stmt.
‫And then since we're getting more than one record we're fetching all posts.
‫We use fetch all.
‫Now, if you're if you're just getting a single post, you would use fetch.
‫And we're going to do that in the next video.
‫But just know that if you're getting multiple records, you're going to use fetch all here.
‫All right.
‫So there's really three steps to this.
‫Prepare your statement.
‫Execute the statement.
‫Fetch your results okay.
‫You want to just remember those three steps.
‫That's what what you'll be doing when you're using PDO.
‫All right.
‫Now let's see what that actually gives us.
‫So I'm going to echo some pre tags.
‫And then in between that, let's do a vardump of the results variable.
‫And if we come over here and reload you can see what we get.
‫Now keep in mind I have three posts in my database.
‫You might have more or less depending on what you did.
‫So this is kind of the default format that we get back, which is a little weird.
‫So if we look we have we have three items in our array.
‫And each one is an associative array.
‫And it has the title right.
‫So the title is the key.
‫And then post one.
‫But it also has the index, the numeric index one to post one.
‫Same with the id zero to the id.
‫Then we have the body and also two pointing to the body as well as createdat and then three to createdat.
‫So this is a little weird.
‫So we can format how we get the data back in different ways.
‫I usually like to fetch it as an object, and we're going to do that in the in the project.
‫But you can also fetch as an associate of array where you don't have these, these indexes.
‫It's just the title body.
‫Cetera.
‫And there's a couple ways we can do this.
‫One, we can just pass it in to fetch all.
‫So we can do this PDO oops.
‫We can say PDO double colon and then fetch assoc.
‫So if I do that and I reload now notice I don't get those those numeric indexes.
‫We just have ID title body created that.
‫So that's what we want to use.
‫However you might not want to pass this in every time.
‫So what you can do is in your database PHP where we set our options here or our attributes, we can
‫add another one here.
‫Let's say fetch as associative assoc array.
‫So the way that we do this is just like how we set the exception the error mode.
‫We can do PDO and then call set attribute.
‫And we want to set the PDO double colon addr underscore default fetch mode.
‫And then we want to pass in another argument of what we passed in to the to the fetch all it's going
‫to be fetch assoc.
‫All right.
‫So now if I do that, save it.
‫Come back to my index.php and get rid of this.
‫And then we reload.
‫We get the same thing.
‫So that's probably what you're going to want to do rather than putting it into every fetch.
‫All because now whenever I call fetch all, it will give me this format.
‫All right.
‫So we know what we're getting back an associative array with our data.
‫So let's get rid of the the echo and var dump.
‫And obviously we want to display this in our UI in our application.
‫So all we have to do now is come down here and we have to hard coded posts that are wrapped in this
‫div, this MD, my four.
‫So let's get rid of one of those.
‫So this one right here, we'll get rid of that.
‫And then let's wrap this one in a for each.
‫So we'll say PHP for each.
‫And we want to say for each.
‫Well, actually, you know what I called it?
‫Results.
‫So we could change that to post just to.
‫So it makes more sense.
‫So let's say for each post as post.
‫And then this div ends right here.
‫So we'll go underneath that and let's say end for each.
‫And now if I save that and reload, you'll see that I get three posts, but it's still hard coded data.
‫So let's replace this post one here.
‫With, we can use our shorthand syntax.
‫Let's close that up and then we can say post.
‫Now, since it's an associative array we use this format with the brackets.
‫So post title.
‫There's other ways to do this.
‫Like I said, we can fetch as an object and instead of doing it with the brackets, you would use an
‫arrow.
‫And that's how we're going to do it in the job listings project.
‫But for now we'll do an associative array.
‫And then we can basically just get the body.
‫I'll just copy that and then change that to body.
‫So now let's reload.
‫And now you can see we have post one two and three.
‫So these are coming from the database.
‫All right.
‫So it's as easy as that.
‫Very clean cut.
‫You know three lines really to to create the prepared statement, execute and fetch the data and then
‫do what we want with that data down in our HTML.
‫So in the next video I'm going to show you how to fetch a single post.