22 | INSERT INTO Database Using PHP From Your Website! | 2023 | Learn PHP Full Course for Beginners

so in the last episode we learned how to connect to a database directly from inside a website and in this episode we're going to learn how to insert data directly from inside our website so we don't have to go inside the database and start typing SQL code in order to do that so we're going to do everything from inside our website here yes I did cut my hair it is quite a bit shorter for health reasons so it is a little bit different it wasn't my choice but it is what it is I do want to start out by pointing out a little bit about what exactly we're going to be doing when we connect to our database and insert data into our database since there is a couple of ways you can do it we did talk about us using PDO in the last episode which is what we're going to stick to in this video here so we're not going to use mysqli or MySQL which is outdated we will be continuing to use PDO loan to do this and we're also going to be using something called prepared statements and that is something that is very important for you to do you can insert data into a database without using prepared statements but that is not C cure so don't think it's a good idea to teach you how to do it without using prepared statements since there's never a reason for you to do so so we will be using prepared statements in order to securely insert data into a database and just to talk a bit about what exactly prepared statements are and what exactly they're supposed to do let's say we have a website like this where we have a sign up form we can go ahead and type in your username your password your email and if a user were to go inside your website here and go inside one of these inputs it is actually possible to type code directly inside these inputs here so just like we talked about cross-site scripting in a previous episode like you had to sanitize your data and validate it to make sure that people couldn't inject JavaScript code into your website it is also possible to go in and write SQL code so if you were to write SQL code directly inside this input and they user submits it then they can actually destroy your database because maybe they decide to write a SQL query that can go in just delete the database or something so to prevent the user from being able to write SQL code directly inside an input like we have here we need to use prepared statements now the way a prepared statement work is basically we send in the query that we write so the SQL code and we send that to the database first and then afterwards we bind data submitted by the user and then send that to the database afterwards so because we separate the query from the data that the user submits to us we can do them separately and not have SQL code have an impact on the query that we write inside our PHP code because they're separated so using prepared statements is a very good idea so having talked a bit about that let's actually get started on creating an actual PHP script that can actually insert data into our website so going back inside our editor here you'll notice that I do have one thing that you do not have from the previous episode so in the last episode we did create this database Handler together where we can just go in and grab this PDO variable in order to connect to our database but inside my index page I do actually have a form that I created which is the one you just saw inside the browser this is just basic HTML form you should know HTML by now so this shouldn't be anything new to you this is just a basic form why go in and say I want to submit this data to a PHP file which is going to be inside by includes folder called formhandler.ink.php we did talk about the naming convention that I use here with DOT Inc so if you watch the last episode I did explain that in that episode and I am using a post method since we need to submit data and when we submit data it is more secure to use a post method and that is the method you're going to be using most of the time when it comes to submitting user data when you want to grab data from a database you use a get method most of the time and then you can see I have a couple of inputs down here I have one for the username I have one for the password and I do also have one for the email address now I do want to point something out here which is that we do have an attribute inside each of these form inputs which is called name we'll talk about this in a previous episode I what we talked about submitting data using a form this name attribute is the name that we're going to be grabbing inside this file up here when we send the data to the other page so it is very important that you have a name attribute and you remember what they are or you can just go back and look at your form so you know exactly what you need to grab in order to grab the data so having talked about this let's go ahead and start creating this formhandler.ink.php file since that is what we need in order to actually you know run this data submitted by the user so we can actually enter the Inside Out database I do also want to point out to you before we continue that this is the data that is fitting into the table that we created together in the table episode so as you can remember we did actually go inside our database here and we created two tables we created a comment and a user table and inside the users table we do have an ID username password email and create it at now we did set it up so that ID and created ad is automatically created for us so we don't need to submit any sort of data for that but we do need to submit data for username password and email which just so happens to be the three inputs that are included inside my form so now that we know this let's actually go ahead and create our formhandler.ink.php file so I'm going to go inside my includes folder over here right click say I want to create a new file I'm going to call it form Handler dot Inc dot PHP and having created this one we can now start creating a script that actually goes in and submits this data to our database so the first thing we're going to be doing is we're going to start up our PHP tags we're not going to close them though which we talked about in the previous episode since this is a pure PHP file that is just going to run a script and then that's it we don't need to have a closing tag because it can actually cause issues which we don't want to happen the first thing we're going to do is to actually run a check to see if the User submitted the data and entered this page the correct way because it is actually possible to go inside our website here and go inside the URL and then directly say I want to go inside my includes folder forward slash and then form handther dot Inc dot PHP and then you can see I actually entered the script that we just created and entering this page here in the way that we just did just by typing into the URL is not a good thing so we do need to make sure we check if the user actually submitted this form in order to access that page because otherwise we don't want them to access it so going inside our code I'm going to go in and create a if statement I'm just basically going to check for a super Global so in this case if we're taking for a dollar sign underscore server and then I want to set brackets and go inside and say I'm looking for a request method request underscore method and check if it's equal to a post method so if the user actually submitted a form using a post method which we did actually do because we just did it right here and enter this page using that method there if not then I want to create an else statement and basically say that I want to send the user back to the front page because you know they're not supposed to be here and we can do that using something called a header function so basically create a header function and say we want to add a location colon and then you add in the link that you want to send them to so in this case it will inside a includes folder so in order to get to our index page we have to go back One Directory so we say dot dot forward slash and then we say index dot PHP so basically now if the user tries to access this page without actually submitting the form so if we were to go inside the URL here and say I want to go inside my includes folder and access this page you can see oh okay now I got sent back to the front page so everything is working perfectly so now what we want to do is we want to go inside the actual if condition and say okay so if we did actually access this page legitimately then I want to actually grab the user data so I'm going to create a variable called username and I'm going to set this one equal to a dollar sign underscore post since we sent this data using a post method and inside of here I'm going to reference to the name attribute that we actually submitted inside the form so in this case if we call the username or at least I did I don't know what you called it but if you followed my tutorial you did call it username and then I'm going to cover this down two more times and the second one is going to be PWD for password and the third one is going to be email and just like so we now grab the data and you may point something out here because hey Daniel you forgot something you didn't use the HTML special characters function in order to sanitize the data why didn't you do that this is actually something we have to do when we want to actually output data inside the browser so when you're not outputting data into the browser it is not dangerous at least as it is right now to not sanitize the data so anytime you have to Output data into the browse and actually spit it out so if we were to go down here and actually do something like this here so if I go down a couple of lines and say I want to Echo out the username then I would need to sanitize this because I'm now outputting data into the browser so I would need to go in and actually wrap this in HTML special characters otherwise this is not going to work and it's going to be unsecured but because right now we're just submitting data into a database and not outputting it inside the browser we're not going to be sanitizing anything just quite yet you can of course do it if you want to and sanitize the data like we did in the last couple of episodes and just you know submit the data into the database being sanitized but it is best practice not to do so unless you actually try to Output data into the browser and the reason for that is that we are converting this to HTML special characters so in some cases you know we want to use data from inside a database we don't necessarily want to have it in HTML special characters and use it inside our code for example if we're not planning to actually output it inside the browser so do be aware that there are some cases where you don't want to have HTML special characters translated data inside your database so in some cases you don't want to have it the next thing I'm going to do here is I'm going to run a tri-cats block which we talked about in the last episode basically we're just trying to run a block of code and if it fails then we want to catch an exception so we're just going to go down here and say if there is some sort of error happening then I do want to go in and say I want to grab a PDO exception and I'm going to create a variable e as a placeholder that I can refer to and then inside of here if something happens that goes wrong when I actually try to insert this data into the website then I do want to you know output a error message so I'm going to die which is a function we have inside PHP that is just basically going to terminate this entire script and stop it from running and it's going to Output a error message so going in here we can actually say we want to write a custom error message in this case here I could say something like query failed so I'm going to say Cory failed colon space and then I can concatenate the error message so I'm going to point to the exception and then get methods on up method message get message it is a method but it is called get message so it is the same thing the next thing I'm going to do is I'm going to go inside this try block that we have up here and I'm actually going to grab my connection to our database because we have that inside our dbh.ink.php file to do that I'm going to use something called require require underscore once and this is basically going to say that we want to link to a file that we have somewhere so I'm going to grab a PHP file for example and just say we want to link it inside the script here so when I go in and say I want to link to a dbh DOT Inc dot PHP file I'm just basically linking to this file that we have up here do keep in mind that because I'm inside the includes folder right now and typing this script here we don't need to go inside another directory or something if there's a dbh the link to PHP was inside another directory you would of course need to go back out of the directory and go inside the correct directory and doing this here is basically the same thing and just going in and saying oh okay I'm just going to include all this code and just paste it in here like this is the exact same thing so we're just basically linking to another file which means that we have access to all the code inside that file after this point here and I do also want to point out because I don't think we talked about this yet we do have require underscore once we do also have require if I can and spell that correctly there we go we do also have something called include so we can say include and we can also say include underscore ones all of these basically do the same thing but with slight variation so include for example we'll go in and say oh okay so we're going to include this file just like we did up here but if we can't find the file then it's going to give you a small warning saying oh I can't find a file include underscore once it's going to do the same thing but it's also going to check if the file has already been included earlier inside the script and if it has then it's going to throw you a warning and when it comes to require and require underscore once they do the same thing as include and include underscore ones except instead of just throwing a warning it is going to actually run a error so it's going to have a fatal Arrow saying oh okay we can't find this file so stop everything from running or it's going to say oh you already include this file once so stop everything from running so these do slight variations of each other with you know different exceptions in this case here we're just going to go ahead and use require underscore once because we don't want to run the connection if we already have the connection included somewhere else so what I'm going to do now is I'm going to write a variable called query because I now want to actually create a query that I can send inside the database to insert data so I'm going to set this one equal to a string which is going to be our SQL query string that we're going to submit and I'm going to run a insert statement and you may recognize this one because we did learn how to do this Inside Out database episode this is the exact same thing so the SQL code is basically inserted into and then we're going to choose a table so in this case it's users and I also want to make sure that we include our column names so in this case if we have username we do also have something called a PWD and then we have email then I want to include the values so I'm going to say values and then parentheses and I'm just going to go ahead and wrap my code here to make sure that it doesn't disappear off screen so it goes down to the next line instead and then I'm going to go inside and give it the actual values now we could do this here and just say we want to copy the variable and just paste it in and say comma space and then password paste it in and then the email and paste it in and this would actually be okay do keep in mind to close off with the semicolon at the end here because this is a SQL statement which means that you do need to end off the SQL statement with a semicolon just like we did inside the database episode so it may look a little bit weird that we have a semicolon here and also one here but do keep in mind this is the SQL and this is the PHP but like I said earlier we're not supposed to insert user data directly inside our query otherwise they can do something called SQL injection and destroy our database so doing it like this is not really seen as a good practice now there is two ways we can use a prepared statement either you can use something called name parameters or you cannot use name parameters I will show you how to do both ways I'm just going to do one of them at a time so using not name parameters what you basically just do is you replace these different user data with question marks so you say question mark question mark and question mark and these are going to act as placeholders so we later on can actually go in and insert this data or bind the user data to this query after we submitted the query so going down to the next line I'm going to create an actual statement which is a prepared statement that I can actually prepare to query this query inside the database so what I'll do here is create a variable called stmt for statement then I'm going to set it equal to our database connection which is variable PDO which we have access to now because we actually required this file up here and then I'll point to a method called prepare parentheses semicolon and then inside this prepare statement I'm going to submit my query so basically now I'm submitting the query to the database so it gets run into the database and then afterwards I can actually go and say okay but now I'm going to give you the data that the User submitted so I'm going to reference to the statement we just created so statement and I'll go ahead and point to another method called execute so parentheses and semicolon and inside this method here I'm just basically going to submit the user data and I'm going to do that using a array so I'm going to add a pair of brackets and then I'm going to go in and just submit these data one by one so I'm going to say username then we're going to say password and then we're going to say email so doing this here is going to actually submit the data from the user and actually sign them up inside the website but before we test this out let's actually go ahead and just finish off the scripts here because there's a couple more things that we need to have in order for this to actually be kind of proper properly done the first thing we're going to do is manually close the statement and also the connection to our database it's not something you have to do because this is actually going to happen automatically but it is considered best practice to do so manually to free up resources as early on as you can so going down here what I'll do is I'll refer to my database connection so that is variable PDO and I'm going to set it equal to no then I'm going to go ahead and go in and say I want to grab my statement and I'm going to set it equal to no and I just want to point out too there's a couple of ways you can do this there's also methods for closing off a connection or a statement but I'm just going to refer them to null which is the same thing as just saying okay so just you know not set them equal to anything and free up those resources and the last thing I'm going to do is I'm going to write a die method just like we did below here when you know some sort of error message happens then we want everything to stop running I do also want to point out here that you can use dime or you can use something called exit and people do argue a lot about you know whether or not it doesn't matter which one you're using the general rule of thumb is that if you're just closing off a script that doesn't have any sort of connection running then just use exit but if you're running something that has a connection inside of it then use die and of course we do also need to make sure we send the user back to the front page after they signed up inside our website so I do want to go down here and copy this header function in and then paste it in right above the die statement so we send the user back to the front page and then kill off this script here so this is everything that we need in order to get this working so I could actually go inside my website here and go in and say I want to sign up John Doe you know just to give him some sort of you know username password is going to be one two three and then I can call his email John at gmail.com just to give him something if I would have signed him up inside the website you can see we get back to our front page if I go inside the database and refresh it you can now see that we have another person signed up inside our website so as you can see our script is working perfectly I do want to point something out here though which is something that I know some people might point out why is my user id 10 and this person here it's just basically because I inserted some users before this tutorial here so the the ID is going to change a little bit compared to yours so having done this we now did this using non-name parameters but what about name parameter is inside our code so if I were to go back inside the form Handler I do recommend using name parameters because it actually allow for us to know inside this query here which data is supposed to be inside where when it comes to using non-name parameters like we did here I do also want to point out that the order in which you insert the data down here inside the execute has to be the same order as inside the columns up here so these have to match up with each other but when we use name parameters this is not the case because I can actually go in and say instead of question mark I'm going to write a colon and then I can give it some kind of name so I can say something like username I can also say the second one is going to be colon PWD then I can say the third one is going to be colon and then email and in this sort of way instead of question marks I'm now giving them actual names so after preparing my query here I can go below and I can actually go ahead and bind my user data to these different name parameters up here and I can do that by referring to my statement so I'm going to say statement and then I'm going to point to a method called bind param which stands for parameters and then go in here and say that I want to have two pieces of information I want to first of all have the actual name parameter so the first one is going to be the username I'm going to insert that one and then the second one is going to be the actual user data so in this case our username variable up here so we would paste that in we now have a name parameter bound to a actual data submitted by the user so I'm going to cover this down two more times and I'm just simply going to change these so password then I'm going to change to email and then I want to make sure I delete the array that we have inside the execute down here because now we don't need it anymore because we actually bound them up here instead so doing it like this we now use name parameters instead of not name parameters so what I can do is I can go inside our website here and test this out one more time so I can say Jane do in this case here so we can say pass it one two three four then I can say Jane at gmail.com and then I can sign up go inside the database refresh it and then you can see we have Jane Doe instead and this is basically how we can go in and actually submit data using our PHP code from my website instead of going directly inside a database and manually querying the database in there so this is how we can insert data I hope you enjoyed this episode and the next one we're going to talk about how to actually update and delete data and then after that one we're going to talk about how to select data and show it inside our website so hope you enjoyed and I'll see you guys in the next video foreign [Music]

Channel: Dani Krossing

Views: 64,067

Keywords: INSERT INTO Database Using PHP From Your Website, php insert data into database, php insert into database, php how to insert database data, how to insert database data using php, insert into database using php, insert into database using php tutorial, insert into database using php for beginners, insert database data using php for beginners, upload data to database using php, php database insert, php insert into database from website, php, php tutorial, mysql tutorial, php mysql

Id: IagGGcC95Ig

Length: 22min 48sec (1368 seconds)

Published: Fri Jun 09 2023