PHP and MySQL with CRUD Operations: Create, Read, Update, Delete

hello everyone in this video i will show you how to create a web application using php that performs crude operations this means that this application allows us to create clients to read clients to update clients and also to delete clients so first let's create a new client let's fill this form then let's click on submit but here we have this error message because all the fields are required so here let's provide the address then let's click on submit so here we can see that this new client has been added and also we are redirected to the index page now let's edit the data of this client so let's click on edit so here we can see that we have already the data of the selected client let's for example update the address we can also update the email address let's click on submit and here we can see that the email address and the address have been updated now to delete a client we have just to click on delete so here i am using xampp and for more information how to install xampp you can see the video link that is available in the description now let's go to phpmyadmin and let's create a new database let's call it my shop let's click on create so here we can see that this database has been created and to create a new table inside this database we have just to click on sql then here we have to write the sql queries that allow us to create a new table and to fill it with some data so the first query allows us to create the table called clients which contains the id and the id here is auto increment also we have the name the email the phone the address and created at so here we can see that the email should be unique and you can see that created at is a date time and has a default value then the second query allows us to add six rows into the table now to execute the two queries we have just to click on this button and here we can see that the queries have been executed correctly now let's click on structure and let's click on this table so here we can see that we have 6 rows into this table now i will create a new php project using visual studio code so let's create a new folder so i will create this new folder on the public folder of xampp so let's go to the folder that contains example then let's open the public folder of examp which is called htdocs and here let's create a new folder for our project let's call it my shop and let's select it now let's create a new php file inside this folder let's call it index.php so this index file will be used to show the list of the clients so here let's write exclamation mark and let's press enter and we obtain this template so we can change the title of this file so here let's write my shop then we need to add bootstrap to this file so let's go to the browser and here let's type bootstrap let's go to this first link then let's scroll down and let's click on this button so here we need to scroll down and here we can see the cdn links so i want to add the css to the file that's why i need to copy this url now in the head we need to add a new element so the new element is link and in the href we need to paste the url that we have copied now in the body we need to add the container now let's add the margin to this container then inside the container we can display the title of this page so here let's write list of clients let's add a button that allows us to create a new client so this button is called new client and it should allow us to create a new client so in the ashref we have to provide the url of the page that allows us to create a new client so let's suppose that the php file that allows us to create a new client is called create.php and is available in this folder so the url of this file will be slash then the name of the folder which is my shop then slash and then we have to provide the file name which is create.php so this file does not exist for the moment and we need to create it later now let's create the html table then let's add the header row into this table so here we can see that in this table we have seven columns now let's add the body of this table so in the body we have only this row so in this row we have six cells with the data of the client and in the cell number seven we have two buttons so this is the edit button and it should take us to the php file that allows us to edit the client and here we have the delete button so the url of the edit button should be slash then the name of the folder which is my shop then the name of the php file that allows us to edit the client which is edit.php so this file does not exist for the moment and we need to create it and for the delete button we have to provide the url slash then the name of the folder which is my shop then the name of the php file that allows us to delete the client which is delete.php so for the moment this row is created statically and we should fill it from the database so here we have to start the php code then we need to connect to the database so let's create the variables that will be used to connect to the database so here we have the server name which is localhost because the server is installed on my computer then we have the username and the default username is root then we have the password and as we can see the password is empty then we have the database which is called myshop now let's establish the connection with the database then we can check if the connection is established correctly or not so if we have any connection error then we have to exit the execution of this file and of course we will display the error message otherwise we can read the data from the client table so here i create the sql query so in this query we will read all the data from this table then we will execute this query so the result will be stored into this variable then we can check if the query has been executed correctly or not so if we have any error during the execution of the query then we will exit the execution of this file and we will display the error message otherwise we can read the data from this object using a while loop so in this while loop we will read the rows from the database and each row that we read from the database should be displayed into an html table so inside the while loop we have to use echo and this statement allows us to print one row from the html table so let's move this row into the echo statement then we need to replace the data of this row by the data of this array so because we are inside double quotes we don't need to use concatenation we can directly use this right inside this row so we can delete the old values then we can use the array of course we have to provide the array with the column name so the first column is id then the second column is the name then here we have the email address also we need to provide the edit file and the delete file with the id of the client so here we have to add question mark then the name of the parameter let's say id then the value of the id so we can use this array then we have to do the same thing for the delete file so let's copy all of this and let's paste it just here so the id allows the edit file to know which client we need to edit and also it allows the delete file to know which client we need to delete now let's save this file and let's run it so we can go to the browser then let's write localhost then the name of the folder which is my shop then the name of the php file which is index.php let's press enter and here we have six rows which are filled from the database now i will show you how to create a new client so let's create a new php file let's call it create.php then here let's write exclamation mark so let's change the title of this page let's call it my shop then let's add the bootstrap css link so let's go back to index.php and let's copy this light let's paste it in the head now in the body let's create the container class and let's add some margin to this container then here let's write the title of this page then let's create the form that allows us to create a new client so in this form we don't need to provide the action because we need to submit the form to the same page which is create.php that's why we can delete the action but we need to provide the method so we need to submit the form using the http post method so we have here to write method equal post then let's create the first row into this form so this is the first row of this form it contains one label called name and also it contains an input field called name so this is the name of the parameter that will be submitted to the server and here we can see that this input field is initially empty now let's copy this row and let's paste it so the second row will be for the email so let's change the name of the input field and let's call it email then let's add a new row for the phone number let's change the name of the input field and let's call it phone then let's add a new row for the address we need also to change the name of this input field let's call it address now we need to add another row that contains the submit button so here we have the submit button but also we have the cancel button and when we click on the cancel button we should go to the list of clients so here we need to change the url so when we click on the submit button the data of the form will be submitted to this file so at the beginning of this file we need to read the submitted date so here we need to write the php code that allows us to read the submitted date so first let's create the variables that will store the data from the form so these variables are initialized with empty values and we will use them to fill the form if we have any error so let's add these variables into our form so here in the value we need to display the name then let's copy this statement and let's add it to the other fields so here we will display the email then here we will display the phone number and here we have to display the address now let's check if the data has been transmitted using the post method so if the data has been transmitted using the post method then we can initialize these variables with the data from the form then we need to check that we don't have any empty field we can use a while loop that is executed only one time using this loop we can exit the loop using the brick statement if we have any error so we can use do while false then here we can check that we don't have any empty field if any field is empty we have to display this error message and we need to exit the loop now let's initialize this variable with an empty value we can initialize it just here so if we don't have any empty field then we can create a new client so for the moment i will just add a comment that says that here we need to insert a new client into the database so after adding the new client into the database we can initialize the different fields to empty values we can also display a success message to the user so the success message will be client added correctly now let's initialize this variable with an empty value now let's display the error message if it is not empty we can display it just before the firm so we need to check if the error message is not empty so if this variable is not empty then we have to display the error message then let's display the success message if this variable is not empty so we can display the success message just before the two buttons so if this variable is not empty then we have to display the success message now let's save this file and let's test it so let's click on new client and we obtain this page so let's fill the form so for the moment the address field is empty now let's click on submit so here we can see that we have this error message and also we did not lose the data of the form now let's close this error message but we can see that we cannot close it this is because we need to add the bootstrap javascript so let's go back to the documentation website of bootstrap and let's copy this url then in the head we need to add a new element it is called script now let's save this file let's go back to the browser now let's fill the form and let's submit the date and we have this error message so let's close it and this time we can see that we can close it then let's provide the address and let's submit the form again so this time we have this success message now let's add the client into the database so first we need to connect to the database so here we will create the connection then we need to insert the new client we can insert the client just here so in this sql query we will insert a new client into the clients table so we will insert the name the email the phone and the address using these variables that we have initialized from the data of the form then we will execute the sql query if we have any error then we will display this error message and we will exit the while loop we can also redirect the user to the list of clients so we have to add the following statements so this statement allows us to redirect the user to the index file and this statement allows us to exit the execution of this file now let's save this file and let's go to the browser then let's create a new client let's click on submit and we have this error message then let's provide the address then let's click on submit but we have this error message in fact the email address should be unique but this email address has been already used that's why we have this error so to fix this error we need just to change this email address so let's add number two and let's submit the form so here we can see that this client has been added correctly and we are redirected to the index page also we can see that this email address is already used now i will show you how to edit a client so let's create a new php file let's call it edit.php so here we need to copy the html code from the create file so let's copy all the html code and let's paste it just here then in this form we need to add a hidden input that will store the id of the client so the type will be hidden and the value is the id of the client so we can copy this statement and let's paste it here also we need to change the variable name so it will be id now let's create the different variables then let's check if we received the request using the get method or using the post method so if the request is received using the get method then we need to read the id of the client so first let's check if we have the id of the client or not so if the id of the client does not exist then we need to redirect the user and to exit the execution of this file otherwise we can read the id of the client from the request then we need to read the data of the client from the database but first we need to connect to the database so we can establish the connection with the database at the beginning of this file so here we will connect to the database and here we need to read the data of the client from the database so this sql query allows us to read the data of the client having this id and here we will execute the sql query then we will read the data of the client from the database if we don't have any data from the database then we have to redirect the user to the index page and we have to exit the execution of this file otherwise we can read the data from the database so here we will store the data from the database into these four variables and these variables are already displayed into the form in the else we received the request using the post method so first let's read the data of the form then let's check that we don't have any empty field so we can use a do while loop then let's check that we don't have any empty field so if any field is empty then we have to display this error message and we need to exit the while loop otherwise we have to create the sql query so this sql query allows us to update the data of the client it allows us to update the name the email the phone and the address so here we will update the data of the client having this id now let's execute this sql query then let's check if the query has been executed correctly or not so if we have an error then we have to display the error message and we need to exit the while loop otherwise we can display the success message we can also redirect the user to the index page and exit the execution of this file now let's save this file and let's go to the browser so we can for example update the data of this client so let's click on edit and here we have the data of the selected client we can for example update the name so let's add the number two then let's delete the address now let's click on submit but here we have this error this means that the form does not contain the id so let's correct it so in this hidden field i did not add the name of the field so here i have just to add the name and the name should be id now let's save this file let's go to the browser and let's refresh this page so we should not click on this button because this button allows the data to be submitted again we have just to put the cursor in this field and we have to press enter now let's update the name again and let's delete the address let's click on submit so here we have this error message so we need to provide the address let's click on submit so here we can see that the name and the address have been updated and also we are redirected to the index page now i will show you how to delete a client so we need to create a new php file let's call it delete.php so in this file we don't need to write any html code we need just to write php code so the delete file will receive the request using the get method so here we need to check if the get array contains the id or not so here we can use is set to check if this array contains the id or not if it contains the id then we can read the id then we need to connect to the database to delete the client so here we will establish the connection with the database then we need to execute the sql query that allows us to delete the client having this id so this statement allows us to execute the sql query then we need to redirect the user to the index page now let's save this file and let's go to the browser so here let's delete the last client so let's click on delete and here we can see that the client has been deleted and we are redirected to the index page

Channel: BoostMyTool

Views: 449,862

Keywords:

Id: NqP0-UkIQS4

Length: 32min 49sec (1969 seconds)

Published: Thu May 19 2022