Save HTML Form Data to a MySQL Database using PHP

In this video you'll learn  how to make this form in html,   then how to write the PHP code to safely insert  the data from this form into a MySQL database. let's start by creating a new file called  form.html. In here we start with the doctype   which tells the browser that the code that follows  will be valid html. Then we'll add an html tag   and inside here head and body tags. Inside  the head tag we'll add a title and a meta tag   specifying the character set of this document as  utf-8. This will allow us to use characters from   non-English alphabets with no problems. In the  body let's just add a level 1 header element. Before we go any further let's just  make sure this works in the browser.   If i specify that file name at the localhost  address we see the content from the html file.   Now we can add the form. Inside the body  let's add a form element. Inside this   we'll add an input element with a type  attribute that has a value of text. Before this   we'll put some text so the user knows  what to type in the input. In the browser   this shows a simple text input  in which we can enter some text   note how the text before the input is just text  let's surround this text with a label element   this won't affect how the text looks  but it does affect how it works   we associate this label with an input by adding  an id attribute to the input then a for attribute   to the label specifying the value as the id of  the input we want to associate this label with   in the browser as we can see nothing has changed  visually now however if i click on the label text   the input focus is placed on the  text input and we can start typing   in addition to this associating a label  to an input like this makes the form more   accessible for those visitors that are using  assistive technologies like screen readers let's add another input control to the form   let's add a text area element and  give this an id and associated label in the browser there's the text area   note how a text input only allows one line of  text whereas a text area allows multiple lines   also note that this isn't an input element  with a type of text area it's a text area   element and unlike an input element you  have to have the opening and closing tags before we add more inputs to the form let's add  some styling so that it's easier to see what's   going on styling with css is beyond the scope  of this video but a quick way to add styles   when developing a form is to use an existing css  framework some of these like bootstrap for example   require you to add container elements and classes  to your html so that the styles are applied   however an even faster way of styling a form so  you can concentrate on the functionality is to   use some classless css there are many of these  available for example water.css the simplest way   to use this is to include the stylesheet directly  from a cdn so we'll copy this line of code and paste it inside the head element of the html   now in the browser the form has some nice  styling without us having to add any classes next let's add a list control where the user  can pick from a predefined list of values   we'll add a select element and an associated label each item inside the list is an option element  let's add three options low medium and high like this when the form is submitted the  value for this control will be the text   of the selected option alternatively if we  add value attributes to the option elements   this is the value that will  be submitted not the text   this is the recommended way to do it as  the text is just a label in the browser   the select element is shown as a drop-down list  where you can choose one of the supplied options   note that when the page is first loaded  the first option is selected by default   you can change this default by adding the selected  attribute to the relevant option for example let's   add it to the second option now in the browser  the second option is selected by default so if   this isn't changed this is the value that  will be sent when the form is submitted next let's add some radio buttons a radio  button is an input element with the type   radio to add a label instead of adding this as  a separate element we can enclose the input and   the text inside the label doing it this way  means the label doesn't need a for attribute   let's add another radio input in  the same way just after this one in the browser there are the two radio buttons and  to select them you can click on the radio button   itself or the text note how once they're  selected though you can't unselect them to make these into a group of  controls where you can only select one   we add the name attribute to both  input elements specifying the same   value for each while we're here let's  also add a value attribute to each input   as with the select control this is the value  that will be sent when the form is submitted now in the browser when we select  one option the others are unselected in a group of radio buttons like this  only one option can be selected at once   note that when the page is first  loaded there is no option selected   by default to select an option by default we add  the checked attribute to one of the input elements now when the form is displayed for the  first time that option is already selected   to add a label to this group of controls we can't  use a label element as we're using one for each   option instead we enclose them in a field set  element then add a legend element inside that let's also add a line break  element between the input elements   so each input is shown on a separate line in the browser the radio inputs  are now surrounded by a line   with the text from the legend element shown at the  top with the radio buttons now on separate lines   note that with a select list you  can have as many options as you like   as the list will use scroll bars if the  list is long radio buttons however are   best suited to a limited number of options  as they are always visible on the form next let's add a checkbox control a checkbox  is an input element with a type of checkbox to add a label to this let's do it in the same way   that we did with the radio buttons enclosing the  input and the label text inside a label element in the browser there's the checkbox we can  check and uncheck it by clicking on the box   itself or on the text of the label finally  on the form let's add a button to submit it   we'll add a button element and i'll also  put a line break element before this   so the button is on a new line  in the browser there's the button now we can add some code to process the  form data when the form is submitted   let's add a new file called processform.php back in the form to make this form  submit its data to this new file   we add an action attribute to the form element  containing the url to the location we want   as this file is in the same folder  we can just put the name of the file   let's also add a method attribute specifying  the post method this means that the data from   the form will be sent in the body of the request  if we don't specify this the value will default   to the get method and the data will be sent in the  url so it will be visible in the browser's history   whenever you're saving the data from a farm on  the server the form needs to use the post method   in the php script let's add the php opening tag   the data from a post request will be in the post  super global so for now let's just print that out if i enter some data into the form  and submit it all we get printed out   is an array with one element with an index  of type and a value of one in the form   if we look at the various inputs  note that the only ones with name   attributes are the radio buttons only inputs that  have a name attribute will have their values sent   when the form is submitted so let's  add name attributes to the other inputs   the text input the text area the  select control and the checkbox now if we refresh the page and submit the form we see the values for all of the fields each array key comes from the name attributes   of each input in the html and its value  is whatever was entered into the form next in the php script let's create a  variable for each value we're interested in   taking the values from the  post array using their indexes   instead of printing out the post array we'll  use vardump to print out each of these variables if we submit the form we see  the values of the variables watch what happens though if i submit  the form without checking the checkbox   we get an undefined array key warning this is because if a checkbox isn't checked no  value is sent in the form data for that control   so when we try to access an element of the post  array with that index we get the warning there   are several ways to fix this for example  checking to see if the index is set using   the array key exists function r the null coalesce  operator to set this variable to a default value   if the array key doesn't exist another way to do  this is to use the filter input function with the   boolean validation filter if the value is set  we'll get true otherwise we'll just get null so instead of getting the value  from the post array directly   we'll call the filter input function passing  in the post input type constant the index we   want to check for in this case terms and  the boolean validation filter constant so now if the checkbox is  checked we get boolean true if it's not checked we no longer get a warning we get null note how the values for the other controls are  all strings even though these values are numbers   all values in the post array will be strings  as that's how the data is sent in the request   to get these as numbers we can use  the filter input function again   so for the priority variable instead of  getting the value from the post array   we'll call the filter input function passing in  the post constant the index of the value we want   and this time we'll use the integer validation  filter we'll do the same for the type variable now when we submit the form the values of  these variables are integers we're almost   ready to insert this data into the database  but first we need to do a bit of validation   if we're using this checkbox to make sure the  user has accepted some terms and conditions   we shouldn't continue if it isn't checked as we just saw as we're using the filter input  function the value of the terms variable will   be true if the checkbox was checked and no  otherwise null is the equivalent of boolean   false in a statement so we can just check  for it like this as the condition in an if   statement if the value isn't true we'll  stop the script with a suitable message so if we submit the form without  checking the checkbox we get the message if we check the checkbox we get the  variable values printed out as before if you want to validate any of the other  values then here is where you would do it   note that even if you add client-side  validation in the browser you still   need to include the server-side validation as  client-side validation can be easily bypassed now we can insert this data into a  database first let's create a database i'll do this in phpmyadmin first let's  create a new database called message db   accepting the default collation then let's create  a table called message that has five columns the first column will be called id it's an integer  we'll make it the primary key and we'll check   the auto increment checkbox so that the values  for this column will be assigned automatically then we'll call the next column name  which is a 128 character varchar next a column called body for the  body of the message which will be text then a column for the priority which is an integer  and one for the type which is also an integer   let's save that and there's the database table now we can connect to this database from php we  no longer need to print out the data from the   form so we can remove this line first let's create  some variables to contain the connection details   for the database the host is the same as the web  server localhost the name of the database we just   created is messagedb and will connect using the  root account which by default has a blank password   note that connecting with the root account like  this is only recommended when developing locally to connect to the database we call the mysqli  connect function passing in the host username   password and database name these variables have  to be in this order and it's a common error to   get them in the wrong order if you prefer  you can use named arguments by prefixing   each value with the name of the argument if you do  this it doesn't matter which order they're in this   returns an object that represents the connection  to the database so we'll assign that to a variable   before we can use this database connection we need  to make sure we connected ok the mysqli connect   error number function returns an error code from  the most recent connection attempt if there was   no error this returns zero so we can check for  this by simply using this as the condition for an   if statement if a connection error has occurred  we'll stop the script as above and print out a   message we can get a description of the error  by calling the mysqli connect error function   if an error hasn't occurred for now let's print  out a message saying the connection was successful let's give that a try when we submit the  form we get the connection successful message   let's see what happens if i get  one of the connection details wrong   for example let's change the password now if  i submit the form we get the connection error   message specifically access denied this is  what happens if the credentials are incorrect let's change this back so  the details are correct again   once we've successfully connected to the database  we can insert a new record first let's remove this   connection successful message then let's create  a variable that will contain the sql that we need   to insert a record we use the insert into  statement specifying the name of the table   which is message and in parentheses a list of the  columns that we're going to insert values into   we don't need to specify the id column as a  value for this will be assigned automatically   then we add the values keyword followed  by a list of the values we want to insert   in parentheses the values from the form  are in the variables we created above   there are several ways we could include these  in the sql we could insert the values of the   variables directly like this making sure that  string values are enclosed in single quotes   however doing it like this makes the  code vulnerable to an sql injection   attack an attacker could insert some sql code by  including single quote characters in the form data   there are several ways to avoid this but the  one that leaves us with the clearest sql string   is to use a prepared statement to use a prepared  statement first we replace the literal data   values in the sql string with question mark  characters which are known as placeholders   note that we don't have to put  quotes around the string values   just a question mark where the value will go  next we call the mysqli statement init function   passing in the connection as an argument  to create a prepared statement object   then we call the mysqli statement prepare  function passing in this object and the sql string this returns a boolean success value  so let's check to see if this is false   and if so we'll stop the script  printing out the error message   as above it's at this point that any  syntax errors in the sql will be caught   now we can connect values to the placeholders  in the sql string which is known as binding to do this we call the mysqli  statement bind param function when we bind values to the placeholders  we specify a type for each variable using   a string of individual characters where each  character corresponds to one of the variables   the characters we can use are i for integer values  d for double values s for strings and b for blobs so let's call the function passing in the  statement variable as the first parameter the second parameter is the string of types so  in this case it would be an s for the name as   it's a string followed by another s for the body  then an i for the priority as this is an integer   and finally another i for the type the rest of  the parameters are the values of these variables   so the name message priority and type variables  the number of parameters has to match the number   of characters in the string otherwise an exception  will be thrown once we've done this to execute it   we call the mysqli statement execute function  passing in the statement object as an argument   finally let's print out a message  saying the record has been saved let's give that a try if i enter some  values that contain single quote characters   and submit the form we see  the record saved message and in the database there's the record  containing the values from the form   note that the value for the id column has  been assigned automatically so this is the   minimum php code you need to safely insert  a database record with data from a form   this is covered in much more detail in the  course linked to in the description below   also in the description is a link to the code  shown in this video along with links to relevant   documentation please don't forget to like comment  and subscribe and as always thank you for watching

Channel: Dave Hollingworth

Views: 158,502

Keywords:

Id: Y9yE98etanU

Length: 24min 38sec (1478 seconds)

Published: Tue Mar 15 2022