PHP Front To Back

PHP Front To Back [Part 21] – Fetching Data With MySQLi

hey what's going on guys welcome to parts was this 21 of PHP front-to-back I know it's been awhile since I uploaded to this series I've just been really

busy I I'm working on a 10 project laravel course for onyx and that's taking up a lot of time so what we're going to do today is work with databases finally I know a lot of you guys have

been have really been asking me when we're going to start working with MySQL so we're going to do that we're going to start off with MySQL I or MySQL MySQL improved which is an extension that

allows us to interact with the database in my opinion it's not the best I would prefer something like PDO but we will get to that but MySQL I is easier and to be a PHP programmer you

should definitely at least know it now you can use it in a procedural syntax or in object-oriented with classes what we're going to do is start off with procedural and then later on we'll move

on to the the object-oriented way of doing things so I have open PHP myadmin and if you've been following along and you have xampp installed you should have this just go to localhost / PHP myadmin

and what we're going to do is create a database to work with alright so this is going to be the start of a really simple blog application alright so we're just going to call this PHP blog and create

the database alright let me make this a little bigger now we're going to have to create a table okay unfortunately with with relational databases we have to kind of predefined everything the tables

or the columns we're going to use the data types for the columns it's not like something like a MongoDB or you know another no SQL database where we can kind of do it on the fly through the

application we have to first set it up in the database layer alright so let's say we want five columns and we're going to call this table posts alright so this is going to

hold blog posts so this is where we need to put our fields we're going to have an ID field and the type is going to be integer which is already selected and then over here we make this a little

bigger over here going to mark this off right here AI which is auto-increment meaning that it's going to the ID is going to it's going to add one every

time a record is added so let's say that we have records up until ID three the next one we put in will automatically be ID 4 alright we're also going to make it the

primary key ok you should have this little pop-up just click go and that will that will label it as the primary key all right then we want a title and that's going to be a VAR char which is a

variable character and we're going to just set it to the max of 255 and then the next one is going to be body ok the body of the post and that's going to be the text type and text we don't have to

specify a length all right and then we're going to have the author which will also be varchar' will just say 255 and then finally we'll have created at that's going to be a timestamp so right

here at timestamp and we're going to make this have it's going to have a default value of the current timestamp okay that way we don't have to worry about it every record that we insert

will automatically get that you know that day and time so let's say save now we have our post table so I want to insert some data because the first thing we're going to do with our application

is you know select data from the database so we need to insert and we're going to say for title it's a post one you don't want to touch the ID because it's auto increment it's going to update

by itself and then we'll just say this is my very first post on this awesome vlog I don't know doesn't matter author just put myself and then let's add another one we'll say post two we'll

say this is post number two alright author will just say John Doe ok and then we'll click go down here and now if we go to browse you can see we have two posts

so now what we'll do is create a new folder in the PHP sandbox and that's going to be website 8 and let's create a new file and we'll save that as index dot PHP tape so this is going to be just

basically the blog role just a list of posts like you'd see on any blog homepage ok so we're going to start off by connecting to MySQL so to do that we're going to let me just put a comment

in here and we'll say create connection and then we're going to put a variable I'm just going to say Co and n for connection and we're going to set that equal to MySQL underscore Connect I'm

sorry MySQL I that's the old way of doing it without the I alright so what we want to put in here is for things basically we want the host which is going to be

localhost we want to put the user which is going to be root for me and then the password okay make sure you put your users password and then the database name which we called PHP blog all right

so that's the connection string now what we want to do is check the connection and we can do that by putting an if statement and we're going to check for a MySQL i underscore connect underscore

Jarno all right which is actually a function so if that's look so if that's true then we're not connected or there's an error so we'll say connection failed and then let's just echo out a message

will say failed to connect to MySQL and then we can actually concatenate onto this the the actual error so we know what's going on we can get that from MySQL connect I connect i haven't used

this in a long time underscore yo are no and yeah that's that should work so let's go ahead and save that and then we'll go to localhost slash PHP sandbox slash website 8 what

the hell I like mrs. belt local host PHP sandbox website 8 okay so we're not seeing anything which is actually a good sign if I were to go over here and let's say route 1 which obviously isn't isn't

correct and we reload we get access denied for route 1 ok if we were to let's say change the name of the database to something that's not there and reload will get unknown database so

this will tell us what's wrong alright so I'm going to put it back to the correct settings and that's it so we're connected but I don't want to have this inside of this file because we're

going to need this in other files as well so what what I would usually do is create a new folder why is that not updating new folder see refresh hell not sure why that's happening let's

create it out here then on htdocs PHP sandbox website 8 and we'll create a folder called config alright guess we have to do that for some reason and then in config we'll create a new file and

we'll save it as DB dot PHP all right and then we'll move this this whole thing to the DB file and then we'll simply require that file so say require that's going to be in config slash

DB dot PHP all right so now we're connected let's go ahead and try to fetch the posts all right so let's see we're going to we're going to first create a query so we can put that in a

variable and the query I want is going to be select all from posts now if you don't know SQL at all I would suggest going and watching my SQL beginner-to-intermediate series and

it'll tell you all about select statements inserts updates and all that so that's our query it's put in a variable and then what we want to do is get the result let me just put comments

here too so this is going to create query and to get the result we're going to set a variable result and set it to MySQL I underscore query and we're going to pass in query all right actually we

want to pass in the connection and the query all right we can use this because we're requiring the DB file up here which has that connection variable alright now there's a lot of different

ways to fetch data we're going to use MySQL I fetch all and we want to get back an associative array okay so let's say fetch data and for that let's put it in a variable called posts and we'll set

this to MySQL i underscore fetch all all right and then in such all we're going to pass in the results and then the type of data or the type of format that we want which is an associative

array so we want to say MySQL i underscore associative array is it's like this so name Brad and so on alright so now we should have that data inside a post what we want to do now is free the

result so this basically freeze it from memory let's say MySQL I my SQL i underscore free underscore result and then we just want to pass in the result all right and

then finally you want to close the connection so for that we're going to call my sqli underscore close all right l we going to pass in the connection okay so now we're getting the data let's

save this to make sure there's no errors all right there isn't we don't have any output here but what I'm going to do is just do a var dump okay remember that for our dump we're going to pass in

posts and let's see what we get and we get this array with you can see title and it gives us the type and the characters and all that so it looks a little mushed together but the title

post one body has the body and then it has post two and so on all right so what we want to do now is output this stuff somehow so down here I'm going to put some HTML we need to close this out all

right so put our HTML down here I'm just trying I'm going to try to set this up like a real application let's go ahead and put in a title here so we'll just say PHP blog and let's make it look

decent by adding bootstrap so we're going to use boots watch and I'm just going to grab this first one I'll click download' grab this link and we'll go and put that right in here

all right and then in the body let's put an h1 and we'll just save posts okay and then what we need to do is loop through the posts that we're getting back here we're going to do that with a for each

loop okay so we're going to use this this some shorthand syntax so for each it's not really short it's just alternative and for each okay so we're going to say for each posts as post and

then let's put a div and we're going to put a class here well which is a bootstrap class and then we'll put let's say an h3 and in here we'll say PHP echo post remember it's an associative array

so we can access them like this okay so that's h3 and then let's do let's see let's put a small tag and in here we'll say created on and then we want to date so PHP echo post and remember we have

that created app field that gets filled automatically and then we'll say bye and then we'll echo out post post author all right and then under that let's just put a paragraph tag and we'll say PHP

echo post body all right yeah it looks good so let's save that and let's go over to our application and reload and there we go post one you can see we're getting the

date the author and the body I just want to wrap a container around this stuff I know we don't have to use bootstrap it's just kind of adding something to the mix but I wanted to you know look at least a

little decent alright reload and there we go so that's how we can select multiple rows from the database so now what I want to do is I want to make it so that we can click on these and then

grab that specific post from the database okay so let's go down here and let's put a read more length okay so this will go to post dot PHP and we're going to send a parameter we're going to

send the ID and then in here we can say PHP echo post and get the ID alright and let's see let's also give it a bootstrap class of BTN and BTN default alright and then the text will just say read more so

now we want to create a new file in the website eight directory and we're going to save this as post dot PHP okay so this is going to represent a single post and what I'll do here is just copy

everything from the index file paste that in we're bringing in the database we're going to say select all from posts but we want to specify what post and we can do that with a where clause so we're

going to say where ID and for now we'll just say where ID equals one alright and then we're going to do the same thing here we're going to you know pass in the query get the result and then when we

fetch the data instead of doing MySQL I fetch all we're going to do my MySQL fetch Assoc which will basically take that one and turn it into an associative array

all right and then we just want to get rid of this right here all right and then let's see we'll free the result that can all stay the same now since it's only returning one post

we don't need to do a for each so let's get that out of there and I also don't want this in a well and so you'll just move this back and let's actually put the title in the h1 so we'll grab that

put it in the h1 and then get rid of that okay we'll keep the small we'll keep the body get rid of the read more because this is an individual post we don't need a read bar and that should do

it so let's save and go to website eight slash host PHP undefined variable post all right here take off that s right there and there we go now if we want it to correspond to one of these which to

reload for instance post two we need to get this ID right here and then pass it into the query all right so let's go back here and let's say let's say get ID okay so get ID we're going to create a

variable called ID and then set it to remember this get ID now there's an extra security precaution we should take here and run this through a function called MySQL i underscore real how is it

real escape string okay we want to wrap that in that and that'll escape any dangerous characters or anything that's you know harmful and then let's see right here instead of using just

hard-coded one let's uh let's concatenate on two here and we'll say ID okay and let's save so now if we click on post – whoops what happened here oh this real escape string it expects the

connection as well so first parameter should be the connection and there we go post – if I click on this read more we get post 1 all right we should also put a like a back button here so let's go to

post and let's see right above the title I'll just put a link to slash and we'll say back and we'll give it a class of BTN and BTN default okay now you see how I click back and it's actually going to

the root of localhost and I don't want that what I want to do is create a config variable for the root URL so we can use that throughout the site so in the config folder I'm going to create

another file and save it as config dot PHP and we're going to define a constant okay so let's say define let's say root so it should be caps roots underscore or URL and then we're going to define that

it's going to be HTTP localhost / PHP sandbox slash website 8 okay so this way if we were to deploy this onto a server with a different URL we could just replace this and use this throughout the

site so let's save it and we're going to include it on both pages as the first file first require okay so that will be config dot PHP and then let's go down here where we where we have the link and

let's put in right here PHP echo root URL and then we'll do route URL I'm also going to include the slash at the end here so we don't have to put

that on extra alright so that should work let's try it ok good now for the back button let's go to our post PHP and we also need to require the config here and then down here let's put PHP echo

root URL ok we'll try that it's reload back and there we go good now it's also a good idea for your database parameters to be in the config rather than just right in here so what

we can do is say define and we'll say DB and you may have seen something like this before so this will be localhost just copy that so host user password and name user root pass and DB name which is

going to be PHP blog ok and then we can save it and go to our DB and we can replace these with DB host user paths and name alright and that should still work there we go

alright so let's see oh wow it's been a while let's see what can we do to clean this up a little let's uh let's create an includes folder as well I'm trying to structure this like a real application

so we'll say new folder and that's high yeah it's not working all right so it's a new folder Inc and then in there let's create a new file called header dot PHP and also file called

footer.php all right and then in the index page let's grab from the doctype down to the beginning body tag and cut that out and we'll just put here include it's going to be include and then Inc

slash header dot PHP all right and then we'll put that in the header save that close it up and then for the footer I'm going to grab just this just the HTML on the body cut that put that in the footer

save it close it up and then we want to include that as well okay because you want to try to you want to try to not repeat yourself as much as possible all right and then we'll do the same thing

on the post page so I'm just going to grab that and we'll replace this with that and then these with footer all right and everything should still work good and then the last thing I want

to do really quick is put a nav bar so let's say new file and the includes save this as nav bar dot PHP and let's quickly just go to get bootstrap comm get started

examples starter template control you and grab the nav ok we'll just put that in here let's get rid of the nav bar fix talk let's change inverse to default let's

add the project name which is PHP blog and then all we need is right now is the home link and that's going to have PHP echo root URL we're going to take this active class off for now all right so

now we need to stick that in the header file right below the body so let's say PHP include and that's going to be let's see now far we don't need the Inc because we're already enough in that

directory alright so there we go so we have a little start of a blog application alright so in the next video what we'll probably do is create a form so that we can actually add a post to to

the database alright so hopefully you guys like this and it wasn't too confusing and I will see you in the next one

In this video we will start to work with the MySQLi extension. We will create a MySQL database using phpMyAdmin as well as a posts table to interact with.

CODE: Code for this video
http://www.traversymedia.com/downloads/phpsandbox/phpsandbox19.zip

EDUONIX COURSES: Please use affiliate links from website below
http://www.traversymedia.com/eduonix-courses

SUPPORT: We spend massive amounts of time creating these free videos, please donate to show your support:
http://www.paypal.me/traversymedia
http://www.patreon.com/traversymedia

FOLLOW TRAVERSY MEDIA:
http://www.facebook.com/traversymedia
Posts by traversymedia
http://www.linkedin.com/bradtraversy