PHP CRUD Tutorial with MySQL & Bootstrap 4 (Create, Read, Update, Delete)

hey guys what's going on it's clever techie and in this video we're gonna learn about PHP crud which stands for create read update and delete and this is what our final application is going to look like so we're gonna have a name and location of the users and display them on the same page as our HTML form it's gonna allow us to add new users to the database and then immediately display them on the page we're also gonna be able to edit or update our users just like that and finally delete any user we like as well so that makes up the whole create read update and delete functionality crud and just something to keep in mind to make things a little bit more easier to understand the functionality of create read update and delete correspond to my SQL statements of insert select update and delete and that's what we're gonna be using okay so this is the outline that I've created of all the steps that we're gonna be completing to create our application and we're just gonna be checking off these steps as we go along here and create a functionality so the first step here is to create the post form with name location input fields and the Save button so let's go ahead and get started I'm gonna go to projects new project name this project PHP crud and then navigate to the folder where I'm gonna be storing the source files under localhost and I'm gonna make sure the project URL is correct by adding this PHP folder here and click finish I'm gonna create a new file called index dot PHP and then I'm just gonna create the basic bootstrap HTML template as well as the form let's leave action blank for now method is gonna be post and the first input type is going to be text with a name name and value enter your name the second one is gonna be text as well or the name location and for the valley let's enter enter your location let's also go ahead and add the button the type is gonna be submit I'm gonna name this button save and let's enter save for the actual button text let's also go ahead and add label star input fields so label name for the name input field and label location or the location field and we are now done with the first step so we can go ahead and check that off the next step is to add divs and bootstrap classes to the form to make it look good and center the form and let's also go ahead and create process that PHP added to form action and include it from the index dot PHP file and for the bootstrap I've created this table with all the booster classes that we're gonna be using and all the HTML elements that these classes apply to okay let's go ahead and add bootstrap classes so we're gonna add a div tag to our label and input fields with a class form group and it's going to apply to every label an input field that we have so we're gonna do the same thing here close the div tag and this form group class also applies to the button so let's also do the same thing for the button and the next bootstrap class are we gonna use is called form control which applies to both input fields let's go ahead and add form control and form control for both input fields we're also gonna add another div tag which is gonna wrap our form close it down here at the bottom of the form and we're gonna apply a class called row as well as a class called justify content center which is going to Center our form so justify content center now let's see what our form looks like in a browser and our form looks okay we just forgot to add the styling to the button so it's gonna be BTN BTN - primary so let's go ahead and add that to the button plus equals BTN BTN - primary refresh the page and now it looks a lot better alright so we are done styling our form now let's go ahead and create a new file called process PHP which is gonna be doing all the form processing and then we're gonna include it from our index.php file using the require one statement process dot PHP just like that and we're also gonna add it to form action process dot PHP okay we're now done with those steps so we can go ahead and check them off and move on to the next one which is to create MySQL database crud and table data with ID name and location fields so I'm gonna go ahead and go to the localhost four slash PHP myadmin go to databases and create a database called crud and then create a table named data which is going to have three fields the first one is going to be the primary ID and let's set it to primary and auto increment the second was gonna be name and the third one is gonna be location and both of these are gonna be varchar' let's use 104 values for both of them and that's it let's go ahead and hit save and our database is ready so let's go ahead and check this off and the next step is to connect to the database and insert the name and location records into the data table if the Save button has been pressed and we're gonna be doing this inside of our process dot PHP file so first let's go ahead and connect to MySQL database I'm gonna be using object-oriented style provide a host name which is localhost in this case username password and the database name which is crud let's also create a MySQL error if the connection fails all right I'm just gonna test it to see if it works in the browser and there are no errors so it's connecting to MySQL that way successfully okay so the next thing we're gonna do is check if the Save button has been pressed and remember that we have named our button save and the form is using method post so we're gonna be accessing PHP global variable called post and we're gonna check if the Save button has been pressed with an if statement so we're gonna say if is set post save and that's how we check if the Save button has been pressed next let's go ahead and store name and location inside of variables because it's just gonna make it a little bit more easier to work with the use variables just like that and next up we're gonna use the MySQL I insert into statement to insert these records into the database so we're gonna say MySQL I Curie insert into our table which is data the column name is name and the second one is location and values are name and location I'll make sure to not forget the single quotes for both of those values or we're gonna say Die MySQL I error in case there's a syntax error and once again you guys can always look at this cheat sheet to see what the syntax is for all a my SQL statements okay let's go ahead and test this out now let's go back to our form and view it in the browser and try adding a user to the database hit save and you have an error in your SQL syntax so let's see what's wrong and I have forgotten the I have forgotten to close the the bracket here let's try this again and let's go ahead and go back to the PHP myadmin and see if the record has been added hit browse and it has been added so that part if the application is working let's go ahead and check this off and move on to the next step which is to connect to the database and select existing records and create the loop to display them above the form in an HTML table and then add the booster for classes to style and center the table ok so that's a lot of things so let's go ahead and first connect to the database and select existing records so this is gonna be done on our main page because we're gonna be displaying all the records on our index page above the form so let's go ahead and open up PHP tags just above the form up here and first what we're gonna do is connect to the MySQL database just like we did in the process that PHP file so it's gonna be the same exact thing like a host user password database name or Die MySQL error minus QL I linked and we're gonna run a Curie and store it inside of a MySQL result variable - ql Curie and the cure is just gonna be select star from data which is gonna select all the records from the data now I want to print out the result so that you guys can see what it looks like so far so for that I'm gonna use my favorite function called pre R to print that array in a more readable format so I'm just gonna paste it over here and then use pre our result and so this is what the result looks like so far as you can see it's got a field count 3 and num rows 1 so that means it has pulled one record with three columns but since it's an object and there's no data in it like there's no name and location we got to use a specific method to pull this data from the object and for that we're gonna use a method called fetch a sock so the result fetch a sock and then we're gonna print that out as well and let's go ahead and comment out this part view it in a browser and now you can see that we do have a data we have the ID name and location which is exactly what we need and the way this fetch a sock works is if we print it out again it's gonna fetch the next record and it's gonna keep going and going and going until it gets all the records so from that we can create a while loop which is gonna keep fetching the records from the database and keep pulling them until it gets to the end of the database and prints out all the records to the screen so just to show you guys what I mean let's go ahead and add another user here and then when I go back to this page you can see that it has printed two records and they're both different even though we have used the same exact function so that's how fetch a sock works and that's why it's great to use it in a while loop to print out all the records from the database and before creating the while loop let's actually go ahead and create the HTML table first so I'm gonna go ahead and close PHP tags so we're gonna add HTML and whole thing is gonna be inside the div and it's gonna have the same exact class to Center the entire thing inside of a justify Contin center class let's go ahead and close the div tag over here for now and then create the table and the table just gonna have a simple bootstrap class called table close the table and then let's create the table head just a header of the table table row and inside the table table row we're gonna have three th's first one's gonna be named our name value second one is gonna be location and the third one we're gonna use call span equals two which is gonna hold our edit and delete buttons so we're gonna name it action okay so below the table header is where we're gonna be creating our while loop so let's go ahead and reopen PHP tags here and we're gonna say while result fetch a sock so what we're doing here is we're looping through the the result fetch a sock so it's pulling records from the database just like we seen before and we're storing everything inside of the row variable which is gonna be array with our name and location of the user so let's go ahead and use a short syntax for this and close the PHP tag here and we're gonna use a table row first and inside this table row we're gonna have our table cells where we're gonna be printing the actual values so reopen the PHP tags and print out row name which is gonna be the name of the user and then I'm just gonna copy this paste and change this to location it's gonna be the location of the user I'm also gonna create an order table cell down here which is gonna hold our edit and delete buttons that we're gonna create later on okay so the last thing that we want to do here is open up the PHP tags and and the while loop save everything and let's see what it looks like so far in the browser and looks like everything worked it's printing out name and location and let's see what happens if we add another user refresh the page and everything is working it's printing out all the records but there's no padding on a page so it kind of looks weird so let's go ahead and add a div with a class can Taner and this is a bootstrap for class which adds page padding's and margins so it's gonna look so it's gonna make our page look good and now everything looks good so we're now done with this step and now let's go ahead and add edit and delete link buttons and pass the ID of the record as get variable in the URL for both links and we're gonna do that inside of the table cell that we've left empty so for the edit link we're gonna say href equals index dot PHP because that's where our form is located and that's what we're gonna be editing our record on and we're gonna pass the variable as edit and print out the actual row ID inside of the edit variable let's also add a bootstrap class right away called BTN info and for our delete link we're gonna be linking to the process that PHP and pass the variable as delete and do the same thing here by printing out the row record and for this one let's go ahead and use BTN danger class because it's dangerous to delete a record alright I think this is it let's go ahead and see what it looks like and it's looking good and you can see down there at the bottom of the screen that the variables are being passed correctly so we're done with that part and now if the delete button has been clicked we want to delete the record from the data using past ID from the get delete variable value okay we're gonna do that inside of our process that PHP and it's gonna be basically the same logic as the Save button so we just want to check if the if the delete button has been clicked so if is set and this time we're accessing the get variable because that's the variable that were passing through the URL instead of post so we want to check if it said get delete and that's how we know that the delete button has been clicked so now let's just go ahead and store the ID inside of the variable and then we just want to run the MySQL like URI to delete the record from the database so when I say delete from data we're ID equals ID as simple as that or Die MySQL I error and that should do it let's see if that works let's try deleting a record go back to the page refresh and it's working okay so the next thing we want to do here is create a session message and message types for save and delete buttons and then redirect the user back to index dot PHP for both okay so for that we're gonna incorporate sessions let's go back to the process that PHP and here at the top we want to start the session twice by a session start function and then inside of the save button we want to set the session message variable to record has been saved and we also want to set the message type so we called msg type to success and for the delete we want to do pretty much the same thing with a different message type and message so record has been deleted and for the message type let's use danger and I'll explain what we're gonna do with the message types and a little bit let me just fix this part here I'm just gonna cut and paste it here just to keep our code organized and we also want to redirect the user back to the index dot PHP page so for that we're gonna use a header function with location Colin index dot PHP and now ready register user back to the index page same thing for the delete button okay so now that our session variables have been set all we have to do is print them out on the index page and we want to do this part at the very top of the page so I'm gonna open PHP tags up at the top and first of all we want to check if the session message has been set so we're gonna say if is set session message then we're gonna use a div with a class or the bootstrap class alert and then alert - and here's where we're gonna be printing out that message type so it's a really handy way of using bootstrap classes along with PHP to have this kind of dynamic functionality so what it does here is if the message is for example success it's going to use the bootstrap class called alert their success and if the message is danger is gonna use the bootstrap class alert - danger and both of those are bootstrap classes if we go here you can see that there indeed booster classes for danger and success and that's what's going to change the color of the message based on their type okay so message type and then we want to print out the actual message so we're gonna open up PHP tags again an echo session message and then we also want to unset session message let's close a div tag and the and if statement as well that said let's go ahead and test it out all right let's try deleting their record first and boom everything has worked we've deleted the record and we display in the session message record has been deleted with appropriate bootstrap class now let's try adding another user to the database save and record has been saved the bootstrap class is working as well so we're done with that part and we've already completed this step as well so I mean check that off and the next thing we're gonna do is if the edit button has been clicked select the existing record from the database and set name variable and location variables and display them in the form input fields so just to show you guys this is the completed application and when the edit button is clicked we want to change the values of our form input fields to the values of that record ID which is nine in this case and you can see how it's changing and we're also going to change the Save button to update when the Edit link has been clicked so you can see how it's changing here alright let's go back to process that PHP and first of all check if the edit button has been clicked so if is set and remember that we're still accessing the get variable so get edit and then let's go ahead and set the variable ID to get ID and then run the MySQL I Curie and say select from data which is our table where ID equals ID or Die MySQL I error now when we're selecting a record like this from our database it's good practice to make sure that the record exists before doing anything else so we're gonna say if count result equals equals 1 so that means the record has been found inside of the database and now we can set our variables so we're gonna store the result inside of the row and we're gonna use the the method fetch array just like we did before and that will return the data from the record so now we can set the name to role name and we can set the location to role application and now we have our variables ready so now let's go ahead and print those variables inside of the values of our input fields so this is our form and I'll actually screwed up this part so instead of valley here guys it should be placeholder of course so let's change that to placeholder and for the value we're gonna be printing out the actual variables here so echo name for the name and value equals echo location for location and so now it's printing out some kind of an error and that's because the variables haven't been set because the edit button hasn't been clicked yet so we want to make sure to set the default values of name and location to empty strings in case the Edit button hasn't been clicked yet so when I do this at the top here let's say name equals empty string and location equals empty string as well and let's see what happens now and now we're not getting that error anymore so let's try clicking the edit button undefined index ID on line 35 let's see what's going on here Oh actually we want to say edit here of course because that's the actual name of the variable okay let's try this again edit and now those values are being set inside of our form input fields as should be now the only thing that we got to do is change the button to update when the edit button has been clicked and for that let's go ahead and create a new variable called update and set its value to true and the default value of update is going to be false then go back to our index page and here at the bottom of the form where our button is we're going to open up PHP tags and we're gonna say if update equals true so that means the Edit button has been pressed then we want to change the value of our bootstrap class to be TN info so that's just gonna create another button type and we want to change the text to update and the name to update as well so that's going to change our button to update and we want to say else we want to print out the regular save button and we want to make sure to close the if statement down here and if and that should do it let's test it out I'm gonna click the edit button and the values are being changed to the actual record ID and the button has been changed to update as well so that's working and now all we have to do is create the actual functionality to update a record so we have already created the update variable so the last three steps that we're gonna be completing is for the Update button functionality and the first step is to add a hidden input field with a valley of the record ID to access it from post so let me explain it okay so since we're gonna be accessing the update button from within the form and the form is using the post method we need some kind of a way of accessing the record ID from the post variable and for that we can create an input type hidden' with the valley of ID inside of it so the actual input field is gonna be hidden from the user but we can still access the valley of the record ID so that we can then work with a record and update it okay so just beneath our form tag we're gonna create that input hidden field input type pickles hidden I'm gonna name it ID and the valley is going to be the actual ID variable that we're going to create so echo ID and at this time the ID variable doesn't exist so we want to make sure to set its default value so we're just gonna set it to zero okay so we are done with the hidden part and now if the update button has been clicked we want to update the record with a new name and location using the valley from the hidden ID input field and we want to set the value of ID to zero out side of the if-statement we're already done that and finally we're gonna create the message with the session variables as well as the type and redirect the user back to the index page okay so let's go ahead and check if the Update button has been clicked first so if is set post update and we can now access that ID so post ID so that's our hidden input field and then name and location are gonna be accessed from post name and location so these name and location are the new variables that we want to update our record with so I'm gonna say my rescue like URI and this time we're gonna run the update query update data set name equals the new name then comma location equals location and we want to say where ID equals ID out the record or Die MySQL I Eric and that should do it now let's go ahead and create the session messages so same thing session message I'm gonna say a record has been updated and MSG type so that's the different type of message for the bootstrap class when I use warning and then redirect the user back to the index page and that should do it let's go ahead and test everything out I'm gonna click the edit button change this to another value hit update and the record has been updated successfully so all the functionality is working let's test it out once again delete the record add another record out another one edit update delete and everything is working and let's go ahead and check out these two last steps and that's it for this video for PHP crud create update and delete if you guys like this video please like share and subscribe and you can download all the content of this video from my patreon page and I'll see you next time clever techie out

Channel: Clever Techie

Views: 850,583

Keywords: php crud, php crud tutorial, php crud with bootstrap, php crud tutorial mysql, php crud operation using mysqli, php crud application, php crud app, php crud with mysql

Id: 3xRMUDC74Cw

Length: 37min 24sec (2244 seconds)

Published: Tue Sep 04 2018