How To Insert Record into MySQL Database using PHP PDO

hey guys in this video we will start wiring up our registration form with our database the objective of this video is that we when we submit our form the data that is displayed on the successful PHP page will also get submitted to our attendee table in our database now this process starts with modification of our code so we go back to visual studio code and then what we want to do is create a new file in our DB folder so I'm going to create a new file click on the DB folder create new file and I'm going to call this one crud dot PHP now crud is an acronym for create read update and delete and the purpose of this file is to store all of the function or functions or all of the code associated with those operations this will also represent our first step into using an object-oriented approach to our PHP development so the first thing that we want to do as usual in any PHP file is create our PHP block and then inside this block we're going to define a class and the class name I usually make my class name reflective of the file names that way I know exactly what class is represented by what file so I'm going to call this class fraud and if you're not very familiar with what I'm doing here with class and objects and so on it's fine you can just stick with me and I will explain it along the way but this is the object-oriented approach to programming now I'm going to define private attributes and I'm going to call this one DB and then I'm going to define a function but this is a special function that every class has our can have and it's called a constructor so I'm going to write the word function and we would have looked at how we defined functions before and in PHP all you have to say is the word function and then give it a name so we say function and then this is how we define a constructor so it is to underscores and in the word construct so that's underscore underscore con stroked and our constructor is going to take a parameter and then we'll call this parameter con so essentially when you're initializing an object of a class you can actually opt to do it without any objects without any parameters or if you require a parameter this is where you would have defined that a parameter is required or some value is required anytime you're initializing an object of that class type so I'm going to take a parameter called pun and then I'm going to assign whatever value came in to this con paramita to or private variable called DB so and then I'm going to say this which is a keyword in most programming languages which just means in this class in this class file a time in so this is just a short way of seeing I'm home alright so this gives me access to all the private and public attributes of this class file so in this case I want this DB to get the value that Khan has all right so in other words wherever I'm going to use DB through this class it should embody whatever value came in in con all right so just went ahead and added some comments so that we have a bit more context as to what is happening with each line of code now the cool thing about a class is that you can put as many functions and variables all inside of that class and wherever you need to reference it all you need is a instance of the class and you just make reference to the function that is there so we are going to be using this class to house all of the functions associated with creating a record in our database retrieving a report from our database updating my record in our database and deleting a record in our database so for this video we will focus on fleshing out the insert function so I'm going to say function but then I have to qualify this one as public so I did say private here and I also have the option of public so what this means is that if I declare an object of class crud there is no way for me to interact with DB because DB is private to the class so the only way I can interact with DB is from another function inside of a class however when I do say public then it allows me to interact with it outside of this so I'm going to say public function and I'm going to simply call this insert and I'll just create that function body and then insert no it's supposed to get some parameters so that it knows what it should be inserting so I'm going to define parameters here inside this insert function that will be able to accept all of the values that are being passed in the post action so let me say that a different way remember from our forum we're collecting a bunch of data points name first name last name date of birth etc so all of these data points are going to get submitted when they get submitted they go into the success dot PHP page which is our action and we can access them all using the post variable what we can do however is actually put them in the database at least not yet so I'm going to define a function inside of my crud file that is going to be waiting and willing to accept values that would be coming from the post function or the post array so in other words I'm going to create add an a parameter her data point so I'm going to have one for first-name and I'm going to kind of write it shortened so you don't have to necessarily reflect the exact name someone to shorten it but at least I know what F name means an L name and I'm just declaring some variables that will actually house all of the values coming back and specialty okay so they so once again when I call this function to insert the expectation is that I'm going to be passing a value per parameter and then the operation that I carry out inside of the function will use those values to complete the insert now on to the fun part inside of my function I'm going to start off with a try and catch so I can actually just write the word try and then press tab twice and then Visual Studio just completes the entire try catch for me so let me do that again I just wrote the word try first tab twice if it select if you have both weekends make sure the second one is the one that is in focus and press tab twice and then you will get your track catch block automatically if that doesn't work for you then just type this or try open brace close brace catch open parenthesis and open open and close braces now once again the track catch block C is that I'm going to try and do something here but I'm watching to see if I get an error so that I can take a particular action with this error and this logic called exception handling alright so and I just added a comment here so what I'm going to do is I'm going to go ahead and create a variable called SQL and this variable is just going to play host to my SQL insert statements now I'm going to give you guys a little trick if you're not so comfortable with SQL statements then you can actually go over to your database in PHP my admin click on your table and then go to SQL and then click insert and it kind of gives you a little template as to what an insert statement needs to look like so following this guide we know how we need to type out our statements and how we are going to put them into PHP but once again if it is that you want to develop your SQL and database my my development skills then you need to check out my course which is my SQL database development and mastery so back in our code we're just going to type this all together so I'm going to say insert into so those are SQL keywords and then I'm going to specify the table name which in this case is attendee because I'm about to insert a record into the attendee table and I have to specify which columns now there are two schools of thought or two ways to do this you can specify all the problems you don't necessarily have to because if you put the column values sorry if you put the values in the exact order that they would have appeared if you set value then you don't necessarily have to do this part of it so I'm going to skip that part of it and I'm just going to write it's the more you know the faster way so the first column would be first in last name date of birth email address contact number and we'll remember we that specialty ID and not just a specialty so we'll get to that but that's the order we don't need to put in anything far it's in the ID because it's an auto incrementing primary key so after the would be list of columns becomes the word values so we would have had attendee and then listing out all the columns and then we say values but since we're not listing the columns I'll just remove that so if you're listing the columns you have the parentheses if you're not they don't need them all right and then we'll say values but here is the crazy part so with a regular SQL statement you would have stipulated a value that corresponds with each one of these so if I just fill this all right now I would have put something like the static value Mary and pop-ins etc right I'm not going to fill out all of this but that's what I would have done the meter soon insert body can see all right so that is what I would have been putting in if I was writing this statement manually and if you were using that MySQL MySQL I did a API then that's also how you would have written it however the binge of doing it directly like that in PHP it opens you up to what you call SQL injections which are a very very nasty kind of attack that you really don't want to entertain okay so as a guard against that PDO requires that we react is in a particular way and then it will execute it so the particular way that we're going to write it will have us actually using like placeholders so instead of seeing the value I'm just going to put : and a placeholder to correspond with what should be there all right so I'm going to say : F name : L name and I'm just going to you reuse all of those variable names from the parameters so really and truly once again these are placeholders they don't have any values right now so at the end of this exercise you should have these placeholders once again and I gave them similar names to each variable that I know they will correspond with you didn't have to but for consistency I recommend that you do all right so we would have created some very very abstract form of an SQL statement the next step is to prepare this SQL statement so I'm going to declare another variable and I'm going to call this one SD empty that's short for statement so that was my naming convention alright and then I'm going to say this and then this is how we actually access our private variable BD so remember that evey represents the PDO connection or the connection and we would have already defined our connection from inside of Conn and this is our PDO object which represents connecting to the database so here within the class crud this is an abstraction of this connection so PDO exists here but what we intend to do is pass it into the prod class and it will get stored as DB so the only way to access DB is to say this so it's dollar sign this and then we use that operator so that's a - and greater than sin or listeners and an arrow and this arrow is based very how we reference reference attributes or properties or functions within from a class all right or from an object so this represents the object of class crud and then we are accessing the property or attribute dB so we say DB and then inside of DB we have another function so I'm going to use that error again and this function is called prepare and then prepare takes a parameter which would be the SQL statement so we could have just done this we could have said this arrow DB arrow prepare and then just write that SQL statement inside of the parentheses but I like segmenting it like this so that each step of the way it is clear what is happening so we kind of defined our SQL statement first and then we went ahead and prepared it for execution later the next step is to actually bind our placeholders to the actual values so remember that these were Poland's here instead of our SQL statement are really play solos but the real values are expected to come in through these parameters so we need to have a section where we say st Mt and an arrow and we call a function called bind param and then this function takes two parameters it takes parameter number one which is our placeholder so f name is the first placeholder and then we have a comma and then we say what do you want to bind this placeholder to and essentially what this will do we say that within this statement that is being prepared I want that these placeholders gets replaced with these values now it may seem tedious it may seem mundane and especially if you're used to just writing SQL statements and insert into but once again this is an added level of protection against the the SQL injection attack so you are going to do that for each placeholder and I'm just going to duplicate these lines and I'm just going to change what I need to change all right so you can just copy and paste and kind of expedite that process and here's one of the advantages with using the placeholder making the placeholder loop just like a variable name the changes can occur more quickly so I'm just going to go ahead and change those oats all right so I'm finished binding all of my placeholders and everything looks good to go and the final thing to do or sorry I made a mistake with my object name I had the code written somewhere else and I copied and pasted it so I've gotten that corrected all right so now that we've boned all of the placeholders with our actual values the next thing that we want to do is actually execute so we say SDM T and we and then I use my arrow and I'm going to call a function called execute and this one takes no parameters and then after we write execute I'm going to write return true so at least if it gets this far and it returns true then we can take this as a success indicator that our statement was executed successfully now on the negative side of the fence we want to actually handle any error that might come up and at this stage we really want to see an error that comes up so I'm going to replace this terrible th with PDO exception so remember that we're carrying out a PDO operation here so the more most likely exception to occur is with the PDO operation and I'm going to assign any error object that comes back to a variable called e and then what I'm going to do down here is echo to the screen so I'm not going to throw those through in wouldst the execution altogether but some went ahead co e ro get message so we would have used e arrogant message before and I'm sure you're wondering what that arrow meant so I hope that this exercise has brought more context to what this arrow is because is really just an object of a whole class and we just established that within a class the Lua to access what is in the class is to use the arrow operator and then we can actually interact with the other function so s TM T here is an object of of D be prepare and then the SD empty object has a function bind param and a function execute so in the same way e dollar sign e represents the class or an object of the class video exception and one of the functions available to us is get message and then if an arrow is caught and through well not throwing but printed to the screen then we want to return false so then then this is just an indicator to say it was not successful now that we have made those changes to our crud page or crud file now we have a few changes that we want to make to our connection file the first thing I want to do is add what we call attributes to the PDO object so right now it's connecting to the database and it's all fine and dandy but when we were doing this exercise we did not set attributes and those attributes that I really want are like arrow mode so in other words PDO will always try to behave itself and be non intrusive but then you can force it to behave a certain way by setting the attributes and a certain way I wanted to behave is that if there's an exception I actually want it to stop all execution and show me that there's an exception so what I'm going to do here is reference the PDO object and I'm going to call the function inside this object called set attribute and then I'm just going to type these all quickly off-screen and then we'll just review them so these are the two attributes that I want to set and they're both as it relates to error so I'm setting this attribute and arrow mode' is exception all right so essentially this attribute is just saying that whenever there's an error I want to see that there's an error don't behave yourself tell me that there's an error because the last thing you want is doing debugging you think it's all well and fine and then you push the production and then nothing works so we want to see when there's an error so that's what this line is saying that's changed number one the next change that we want to make is to actually reference our crud file from right here in Cod in con and I'm going to explain why we're going to do it this way remember that in our pages we are referencing our con dot PHP instead of having to reference conduct PHP and then reference crud what I'm going to do is after setting up a successful a successful connection of course because the only way we'll get this far as if no exception is thrown I want to include or what I can require and I'm only going to require once and I want crud dot PHP notice I don't have to put DB / crud because this is the con file that is requiring the file so if I look at the folder level they're both on the same level in the DB folder so I don't have to say DB / fraud so if you roll DB / credit it didn't work then try and remove the DB / and it should work so I'm referencing crud dot PHP and then I'm going to define a whole new object and I'm calling it crud and to define an object I'm going to say this is the name of the object and I want it to be equal to a new instance of myclass and remember that a class the file name is Craig PHP the class name is crud dot PHP and the object name is Kru dot PHP as well they screwed so everything is croats looks uniform so the new instance of crud and then crud requires and here Visual Studio code is telling me that there is something that needs to go inside of the parameters and that's because when we defined our constructor we defined it to accept some value all right so the value that is expecting in this situation in our context because we're the ones building it we know that the value is expecting is some connector so I'm going to pass the PDO object into my parameter so PDO so now what will happen is that for each time that we actually include or require the con file the connection will be established and this will be done once and then it will just reference our thread file so that any operation you want to carry out we need only reference the crud object and whatever function so at this point we only have one function so anywhere we actually have one referenced on any page we can actually just go ahead and reference dollar sign crud and then using our our operator we can look at all the other functions in there so armed with this new knowledge what we need to do is actually start setting up our success page to be able to call our code function so so step number one is require and I'm just going to require once and we're going to put on the DB / con dot PHP so when this page loads it's going to require this file which is going to go ahead and initiate a connection and then it's now going to require our crud file and define a new class and then anything we need to do on the page we can just say object crud and call the function so this way we're segments in the code and we're keeping everything one place or not putting too much PHP or too much too much database too many database operations mixing up with our HTML all the time and it's a nice cleaner way to have your code know that I've completed that activity I'm going to start writing some more PHP code and I'm just going to use the block that's there already so what I want to do is check if this if this page is being loaded on a post action and I'll contextualize that remember that we can always go to our application and just say local slash attendance slash success dot PHP alright and because it's a post we don't have any of those parameters we don't have a query string but then when I do that I'm going to be getting a bunch of errors about undefined variables and that is really because it it is trying to print values from the host action if there was no post action meaning I'm just browsing to the page casually I didn't actually submit a form to get there then these variables don't exist so before we attempt to put anything in the database we probably want to check if such a variable exists and if at least one out of all of these exists that we can assume that the that the rest of them exist all right now when we were designing our form I had put a name on the submit button and this is why so a lot of the times you see that people put a name on the submit button and what they'll do is check if the submit variable exists because this would have come over in the post variable along with first name last name etc etc even though it has no value it really it exists so it's like an empty variable on the post action so what we want to do is start off by checking if that submit value exists so I can say if and I'll say is set so that's the function to say does it exist and inside is set we pass in a variable so this is a nice way to check if a variable exists on a page so I'm going to check if is set square bracket and submit so once again this is not checking for value it's just checking does this variable exists whether it's empty or not is irrelevant does it exist on the page and if it does then we want to carry out an option the redline is there because I miss the semicolon sorry about that all right so we say if is set post submit so if the post submit variable exists then we want to proceed with our operation so I'm going to define another variable and I'm going to call it success equals and the reason I'm doing this is I'm about to call the crud function insert and remember that insert is a valley returning function because it's either returning true or false so that means well I probably can't call this is success instead of success so this is asking a question is it successful true or false is it a success all right and then I'm going to assign to this variable whatever is returned from the function now because we defined or we required DB / Condotti HP and remember that it initializes some object for us called crud I have instant access to this object called crud and then inside of the crud object I want my insert function and this insert function is defined to accept in this order these values the first name the last name date of birth email address contact and specialty so I'm actually going to go ahead and fill in these values using the post values so one pattern could be the idios past post value directly into the function I'm not particularly fond of that I prefer to define my variables so I'm going to have F name so within the context of success that PHP F game didn't exist so I can reuse it here so f name is equal to post first name and I'm going to do that for each of the values in the post and I'm going to do that off-screen so you can go ahead and do that all right so I've added all of these values from the post array and some comments so that we know exactly what's happening at each step and then what I'm going to do is call the insert function and pass in the required parameters so it's f name l name D will be email in that exact order so I'm going to see dollar sign F name as the first parameter the sign L name and I'm just going to do all of them just make sure that they all line up alright so once that is done let me just recap what happened so when this page is loaded it will check if the post if one of the post variables exists and we're working with the assumption that if one exists and the others exist so if one exists then we want to extract all the values from each one and this is done because maybe you get in the volleyball you want to manipulate it before you put it into the database so that's why this is always a good idea before actually calling the insert function and then at the end of that we check if it was a success well we pass those values into our insert function and then the result of the insert function will get stored in this variable is success so then this comes in handy because if we want to print a specific message if it was successful versus if it was a failure then this gives us that ability so I can say if is success meaning if this was true then do something else do something else all right so right now what we're doing is printing a nice alert a nice success message when you have been successfully registered up until no there wasn't any real operation to me to make it a failure so what I'm going to do is take this h1 tag and actually echo this h1 tag if it was a successful if is success then we will see that success message being printed to the screen else we're going to echo the opposite I'm going to reuse that code but I'm going to change this to text danger which will give us red text and then we will say there was an error in processing there was an error all right so once again we're checking to see if we got true or false return from this insert function and if it is true then we want to give them a success message and if it was false then we want to show them that hey there was an error processing your registration now we have one additional and very major change to make and that is as it relates to our specialty now up until this point specialty has been copying across this text we have a select control here a drop down list giving us each specialty and when we submitted it on the success page when we printed the value we got a text we got some body of text now the problem with that is we designed our database to accept a specialty ID so we have a specialties table which is empty right now so we can always fill this with data and what the attendee table is taking the ID relative to the specialties in this table so firstly I need to insert at least well I'm going to insert one for this video so I'm going to insert the database admin specialty so I go so I click on my table specialties and then I click on insert and then I'm going to not give it an ID value because I can't but I'm going to give you the name database admin and then click go this will show me that it's about to execute an insert command and the bottom of page I can click go again and then that inserts it so when I look in that database by just clicking on the table it will always show me what's there and I see that well they did a double entry okay I can remove one that's fine but now I see that I have at least one record here and I have database admin and the ID here is three which means because we set up that constraint on the table I can't put in an ID value here that doesn't exist in the table so whatever I'm about to insert into my database needs to match with those constraints so I'm going to force this so we're going to make this more dynamic as we go along but for now I'm just going to force the value associated with this option to be three so we'll just make sure that Mary Poppins for examples sake selects three and she's a database admin and when we submit what the expectation is that three will be printed here and what three will be passed over in the post and then three will be the value that is received here in specialty and ultimately put into the database all right so I'm going to try and submit the form and let's see if we get any URLs and try and weather through them so when I submit I'm seeing that I'm getting a few errors here and the first one is saying that there's an undefined index date of birth and they give me the line number in the file and when I look back they are right because it's not date of birth it's actually do B so that's minus T so I'll just meet that correction the next arrow is seeing that I have an undefined method called get massage so that's a spelling error on my part so uncrewed dot PHP on line 30 we can go ahead and make that correction so let's get message and then we can try and resubmit and I expect to see errors again because all right there we go there was an error in processing so what this did was it attempted the operation as specified and it returned false and then because he returned false were printing out that red text scene there was an error processing there was an error in processing but then we're also echoing the message because we did see get message to be it should be echoed and what we're echoing is saying that the insert value this does not match something something something and that's because the date of the word database admin is still coming across instead of the ID all right so let's try that again and the error was because I did not refresh my page I just resubmitted the form so let's try and fill out the form once again and then we can proceed to submit all right so I'm still getting the error and if we read the Orochi and that the value list does not match the column list so that that is because well it's saying that there's some column miss matching with the values that we provided so what I'm going to do is just modify my insert into and be very very explicit and I'm going to say column first name last name date of birth so these are the column names as they appear in the database design so just you can always just remind yourself as to what the columns are by clicking on the table itself and then go into structure and you see first name last name date of birth email address contact number so you have to be very very very sure that you're spilling them right so that's date of birth email address contact number and specialty underscore ID so we can try this one more time so I'm going to go back to my form I'm going to refresh once again fill it out and submit and I have success all right and then you notice that there's a three coming back here in this in this printout as opposed to the word database admin so that that's fine as we go along we will refine it but the important thing is that after all of these changes and activities we have finally registered somebody successfully know to validate that the person has been registered successfully we can always go back to PHP my admin click on our attendee table and we will see our record the MARY POPPINS and you see that the date of birth came across zeros and that's because of a formatting issue but we will sort that out but right now we have victory because we have successfully submitted or first record to the database Congrats

Channel: Trevoir Williams

Views: 2,046

Keywords: jamaica, php, development, package management, web, programming, xampp, wamp, server, open source, git, github, source control, community, free, hosting, app, website, heroku, cloud, paas, mysql, user, password, configure, database, full stack, How To

Id: CQNIFDdaF1Y

Length: 37min 56sec (2276 seconds)

Published: Sun Sep 29 2019