PHP Front To Back

PHP Front To Back [Part 22] – MySQLi Insert, Update & Delete

hey what's going on guys welcome back to PHP front-to-back I believe this is episode 22 in the last video we went ahead and started working with MySQL I

or MySQL MySQL improved which is an extension to start working with our database okay with minus QL what we did was we went into PHP myadmin we created some posts in a post table and we

connected to the database we look in config ok we have all of our database parameters or connecting to the database and we're pulling out some posts okay so this is index.php that's what we're

looking at and then we also made it so if we go to the individual page we pass the ID to post dot PHP which is this file we grab the ID from the get the get array then we do another query to get

that single post so what I want to do now is I want to add a new page to add a posts which will obviously have a form but before I do that I want to fix this the responsive navigation here isn't

working notice that if I make this bigger we have a home link but if we go smaller it's not being displayed and the reason for that is we didn't add the JavaScript

the bootstrap j/s file so let's go over to includes and go to footer PHP and we're just going to grab the bootstrap CDN ok we're already including the CSS but we just need the JavaScript so let's

copy that put that in the source attribute oops and that and then we also need jQuery because it's a dependency of bootstrap gif so let's just say jQuery

CDN and let's just grab jQuery 1x uncompressed I'm going to just copy this script tag here and we're going to put that right above the bootstrap j/s save it let's go back reload and now we have

the menu all right so let's create a new file in website mine's in a folder called website 8 it's basically the root of this of whatever you you called your website and then we're going to say new

file and let's save it as add post dot PHP alright and then what I'll do is copy everything from index.php into add post alright and then we're going to get rid of all this stuff because we're not

fetching any posts for this page okay we want to keep the header in the footer all that container let's change this h1 to add post and then we don't want this for each loop we can get rid of

everything in there and we just want to create a form all right so let's give this form a method attribute of post because that's the type of request it's going to make and then action is going

to be this page so let's just do a PHP and we'll use the server superglobal and we can say PHP underscore self and that will submit to this actual page all right and then inside the form let's put

a div and give this a class of form group and let's put a label and we'll just say title and then an input tag let's give it a type of text let's give it a name of title and then a class

form control okay so that's the title let's go ahead and copy that form group I'm actually going to make this bigger all right and then this is going to be the author so change the name to author

and then we're going to have a body change that actually we going to change this to a text area and give it a name of body and then just a ending tag and then we'll give it a submit button so

we'll say input let's do type submit name submit' value submit and let's give it a class of BTN whoops BTN and then BTN primary it should be a – alright so there's our

form let's save it and then let's add it to the navbar so if we go to Inc and then go to navbar dot php' let's copy this and here we'll just say add post and this is going to go to the root URL

and then it's going to go add post dot PHP all right so let's check that out we'll reload and we have an add post link click that brings us to the form so let's go back to our add post PHP file

and up here we're going to do we're going to check for a submit all right so let's a check for submit and we'll do if is set remember we gave the button a name of submit so we can actually check

for that in the post array submit and then let's just do echo submitted all right just to test it out so reload submit and you can see we get submitted now I'm not gonna I'm not

going to create this as if it was a production application we've already gone over a form validation so if you guys want to add that he be my guess but I'm not gonna I don't want to keep

repeating things I know that some people may comment say oh you know you didn't do this in that validation the goal of this isn't to create the perfect application of the most secure

application the goal is to teach you guys the syntax and to do that you know fairly quickly so I'm not going to do any validation here I'm just going to show you how to insert it into the

database alright if you want to learn validation if you haven't watched that episode already I mean you can watch the the ones about filters you can watch I don't know I'm not going to go through

all of them but we've already done that a few times I believe so once we check this let's look to get the form data so we'll say title and this is going to come from the post array okay all right

now I mean as I said I'm not going to make it the most secure app but there are some things that you just need to do and I'm not going to leave those out so we're going to wrap this in my sqli real

escape string alright which is going to protect us from harmful harmful that people can do so let's say MySQL real escape string and we'll just wrap that alright and then we'll copy that

and we want to get the body in the author all right and let's see we're going to create our query now so let's create a variable called query and we'll set it

to insert into by the way guys if you haven't watched my SQL video I think it's what I call it SQL for beginners and intermediates or something like that in that video we go over all the base

you know the core syntax of SQL so I would suggest watching that if you have no idea what you know insert into is and stuff like that so say insert into posts and we're going to insert the title the

author and the body and then we'll define our values which will be and these have to be in quotes it's a title author and body okay so that's our query now what we're going to do is we need to

actually know what we have to add in the connection up here when we use this real escape string you also want to add the connection as the first parameter alright and that con I'm getting from if

you don't remember it is right here alright so we have the query now we're going to do an if statement and we're going to run it through the MySQL I query so we'll say if MySQL i underscore

query and this also takes the connection as the first parameter and then the query okay so if that is successful then we just want to redirect so we'll say header and let's specify location and

that will go to the root URL so we can concatenate in here root underscore URL now if you don't know what that is that's also in the one fig right here config.php we define

the constant root URL which goes to this this website eight directory alright so that's if it's successful if it's not for some reason then we just want to echo out let's say error and

then let's just concatenate my sqli on the score error and wrap the connection alright so that will tell us the specific error if something goes wrong all right so I think that should do it

let's save it and then let's go over here and let's just refresh this page and we'll add a post let me see on the home page we have post one and post two so let's add post three so this is post

number three submit and there it is those three reload it's still there it's in the database now if you want you probably would want to order this to have the most recent first so we can

just add on two I don't know why we didn't do this before but let's go to the index and where we said select all from post we'll say order by and let's see where we want to order it by forget

the fields see posts so we have created at so we'll order by created at and we want to order it descending so the EFC alright and now if we go back and reload now post three

is first okay of the most recent first alright so now let's do the edit let's make it so that we can edit a post and I have to do is kind of quick because I have to pick my daughter up at the bus

and I don't want to be late so in the individual page here we're going to put an edit button so remember that's in post dot PHP so we'll go down here and let's go see will go under the body here

and we'll put let's put an HR tag and then we'll put a link and that's going to go to let's first of all echo out the root URL okay so it's going to be root URL and then it's going to be edit edit

post dot PHP which we still have to create now we need to send along the ID as a parameter so we'll say ID equals and then PHP echo and remember we can get post and that's an associative array

so we can get the ID alright and then let's just give it a class of BTN and BTN it will say default alright and then it'll just say edit okay let's go back and reload okay so now we have an edit

button we click it it's going to give us an error because we didn't create the page yet so let's create that so in the root of our website here let's say new file and save it as edit post dot PHP

now this is going to be very similar to add post so I'm going to just copy from add post PHP and paste it in here and let's see we're going to add – we need to make it we need a query to get the

current post okay just like we did on the individual post page so we can actually grab it from that–from post.php where we did this here I can grab all this copy it go

to edit post and let's go under the submit paste that in and then what we'll do is put the value okay remember we're getting it's going to get put in this post variable so what we can do is go

down and add values to these inputs okay so we'll say value PHP echo post what is it post title and then C I'll just copy that value and we'll go down here and this will be post author and

then this will be in the text area okay we don't need value here just just that and we'll change this to body all right so let's go and reload and now we have the values inside the form so now we

need to take care of the submission so once we submit it's going to go you know it's going to check for the submit and it's going to run this now we don't want to we don't want to do an insert this is

going to be an update now we also want the ID because we need to know which one to update so what I'll do is go back down to the form and we're going to put a hidden input here so we'll say type

hidden' and let's give it a name of update underscore ID and then we'll give it a value and remember we can get the value by saying PHP echo post ID and then we can grab that up here go right

here and we'll say update on the score ID equals and then let's just copy one of these and then this is going to be post update ID all right so now we have actually to get

rid of that D it's just update so for the query we're going to change this around to actually just get rid of this and we're going to stay update the table which is posts and then set and then

whoops not the setcookie that's want that let's see we're going to then describe the fields we want so title is going to equal title that's passed in from the form author same thing what

else we doing here body ok and then we need to specify where this is going so we're going to say where the ID is equal to the update ID all right so you can organize it however you want but that's

going to update the whatever is passed in is the ID so right here we can also we can actually just leave this how it is so let's save this and then let's reload the edit page and let's change

the the spelt out word 3 to just the number 3 will change the author to John Doe and we'll say this is post 3 submit okay so we have an error I'll see we read to be at line 5 so

looks like we have an error in the query update posts set title so let's uh let's print out this query just to see so we'll say let's let's do a die function here and we'll pass in query

just to make sure that everything is actually being submitted correctly so set title post 3 author body ok so the ID is not coming through so why is that so we have a hidden field

with the name of update ID and we're passing post ID you know what I think we just need to reload the form or refresh it no that's not it why is it not showing let me look at the source code

here if we look down here ah value is spelt wrong you guys probably saw that a while ago huh idiot alright so let's change that to value and then we'll go back up here and get

rid of the die and it should work let's see let's go back post 3 edit change that to post 3 I know there's a lot of banging upstairs guys this isn't the most professional episode but I have to

hurry up John Doe and submit and there we go you can see it's been changed authors John Doe so now we want to do is we want to be able to delete posts now when we create a delete button here we

can't just put it in a in a regular link or a button it has to be a post request okay I mean we could do a get request but that's very very unsecure to just have a get request to make a delete so

we're going to create a form that just has one it's going to have an button a submit button and two it's going to have a hidden field with the with the ID so let's go to post dot PHP and go down to

where we have the edit link and we're going to go let's see we'll go right above it and let's create a form tag and we're going to give this a class of let's do pull write because we want it

to align to the right and let's see let's give it a method of post and give it an action and the action is going to be the current page so we'll just say PHP echo server and this is

going to be PHP underscore self and then inside here we're going to first put a hidden field and this is going to have the name of delete underscore ID and then the value and for that we'll do PHP

echo when we do in post ID okay so that's the hidden field now we need to submit so say input type submit and let's give it a we'll give it a name of delete and let's give it a value of

delete and then give it a class of VPN BTN danger which will make it read so let's save that and take a look I'll reload and now we have a delete button now we want to catch the submission of

the delete button so what we can do is copy from let's see let's go to edit post and just grab this right here and then copy that and then we'll go back to post dot PHP and we want to check for

the delete submit so let's paste this in and remember we called it delete not submit so we're going to check for that and all we need is the ID so say delete ID and remember we submitted that hidden

input with the name of delete ID and then we can get rid of this stuff because we're not adding anything we're just deleting it and then for the query this is actually going to be easier than

that we're going to say delete from posts we're ID is equal to the delete on the score ID and then we're just going to do the same thing here we're going to run it through my sqli query and then

redirect so let's save this and let's reload and click delete remember this is post three and now you can see that post three is gone alright so we now have an application that has

full crud functionality we can create read update and delete posts now this isn't the best way to do things using the procedural mysqli extension we can also use it in an object-oriented way

which I'll get to and even better there's an extension called PDO which is PHP data objects which is a in my opinion a much better and a much safer way to work with the database but this

MySQL is definitely something that you need to know if you're learning PHP alright so thanks for watching guys and I will see you in the next episode

In this part we will be adding to the sample blog application and adding insert, update and delete functionality using the MySQLi extension.

Continue with the OOP PHP MVC Udemy Course $10 Promo:
https://www.udemy.com/object-oriented-php-mvc/?couponCode=TRAVERSY010

CODE: Code for this video
http://www.traversymedia.com/downloads/phpsandbox/phpsandbox20.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