An Introduction to PHP and MySQL

1. Introduction: A website can be used to just show some information to a visitor, like a magazine, a new site, or Wikipedia. However, if you want to get some data from the visitor, like a comment, a search term, or assignment per farm, for example, then you need a farm. Farm allows the user of the site to send you some data. Once you have this data on the web server, at the very least, you will most likely save this data to a database. Knowing how data gets from the user to your database is a critical skill for our web developer. By the end of this class, you'll know how to create a farm in HTML. How to process the data from that farm on the web server in PHP, making sure it's valid. And how to securely save this data in a MySQL database. This using best practices and industry standards for the code. Hello, I'm Dave hauling worth. I've been writing web applications since 1998 and teaching web development online since 2012. This class is for beginners to web development that needs to learn one of the fundamental skills when creating a website. Getting data from a visitor to your website into a database on the server. In the class project, you'll be creating your own farm in HTML, processing the data from that farm in PHP and saving it to a MySQL database. Basic knowledge of HTML, PHP and SQL are recommended. But I do explain all the code that I use along with links to relevant documentation where necessary, where you can get more details. In addition, all the source code shown in the videos is available to download. Plus, if you need help from me directly, you can ask a question in the discussion section. Without further ado. Let's get started. 2. Set Up Your Development Environment: Install Recommended Software: To get the most out of the class, I recommend you follow along by writing and running the code shown in the videos yourself. To do this, you'll need a web server, PHP, and a database server installed. I'll be using the Apache web server with PHP and the MariaDB database server. Mariadb is fully compatible with MySQL. All of these are completely free to download and use. You can install these separately. But the easiest way is to install a package that includes a web server, PHP, a database server, and optionally PHP, my admin for administering the database. For Windows and Linux, I recommend x AMP to install. Simply select the relevant download for your operating system here on the official website, and follow the installation instructions. For Mac, I recommend MAMP. Likewise, just download the MAMP installer from the official website here and follow the instructions. Notes that MAMP is the free version and MAMP Pro is the paid version. The free MAMP version is more than enough though, for the requirements of this class. 3. Create an HTML File and View it in the Browser: Let's start by creating an HTML file that will eventually contain a farm. This is the root folder of my web server. If you're using one of the packages I recommended earlier, like XML MAMP, the default folder for Windows, Mac, or Linux are shown here. Although you can change this to a different folder or use a sub-folder if you prefer. Let's create a new file in here called Farm dot HTML. This is going to contain the HTML for the farm. As an example, let's create a farm that you might have for submitting a support ticket. Before we create the farm though, let's just add the basic HTML code we need to create an HTML document. We start with the doc type, which tells the browser that the code that follows will be valid HTML. We need this because it affects how the browser will interpret this code. Then we'll add an HTML tag, which is the container tag for the rest of the HTML. Inside here, we'll add a head tag and inside here a title tag. This title is what is shown in the title bar of the tab in the browser. We'll put support as the title of the page. Also inside the head tag, we'll add a meta tag specifying the character sets of this document as UTF-8. This will allow us to use characters from non-English alphabets with no problems. After the head tag, we'll add a body tag. The HTML inside here is what will be rendered to be displayed in the main browser window. For now, let's just add a level one header elements containing the text. Submit a ticket. Let's have a look at this in the browser. If I specify that filename at the local host address, we see the content from that file. The title in the tab comes from the title elements. And in the body of the document, we just have the header element. 4. Add a Form with a Simple Text Input and Associated Label: Next, let's add a single input control to the page. First inside the body, Let's add a farm element. Then inside this will add an input elements, both without any attributes. For now. The browser. This shows a simple text input in which we can enter some text. This simple text input type is the default. If we don't specify a type. If we add the type attribute to the input element and specify text as its value, then we see the same thing in the browser. When using text inputs like this, I prefer to specify the type even though it's the default, as there are many different types of inputs available, which we'll see shortly. Next, let's add a label to this input so that the user knows what to type in it. Let's start by just writing some text before the input element. In the browser. We see this text before the inputs, and it's just text. This is fine, but we can improve this. Let's surround this text with a label element. This won't affect how the labeled texts looks, but it does affect how it works. We associate this label with an input by first adding an ID attribute to the input. I'll give it to the value of name. Note that the value of this attribute has to be unique within the HTML document. Then we add a four attributes to the label, specifying the value as the ID of the input. We want to associate this labeled with the browser. 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 farm more accessible for those visitors that are using assistive technologies like screen readers, will see later on another way that being able to click on Labels like this to activate an input makes the farm easier to use. So each inputting the farm needs its own associated label elements so that the farm is easier to use. As an aside, note that this input element has two attributes. As you know, you can specify attributes in any other. However, as the type attribute effects the type of the input that's displayed, I prefer to always put the type attribute first for input elements. As it makes it clearer, if you have a farm with several inputs. 5. Add Different Types of Controls for Entering Text: So far in our farm, we have a single text input. Next, let's add some more inputs for adding text. There are several input types available listed here. We'll be looking at several of the non-text ones later on. We've already used a text input, but there are some others that look exactly the same. For example, tail, number and email. These look the same as a regular text input, but have different functionality. Let's add another input to the farm. This time an email type input for the user to enter their email address. With the name input will add an associated label element, linking it to the input by matching the for attribute to the inputs ID attribute. The browser. There's the input control. And if we click on the label, the input focus is placed in the control. The input looks the same as a regular text inputs and behaves in the same way. The difference is when viewing the farm on a mobile device with an on-screen keyboard, a regular text input uses the default keyboard. However, the keyboard for an email inputs will be slightly different. For example, the at sign is available and also on some devices, a key that automatically adds.com to whatever you're typing. It's similar for a number or telephone inputs. The keyboard changes to make it easier to insert the type of data expected in that particular input. Another advantage of these texts like input types, is that we can validate the contents of the inputs in the browser before the farm is submitted. We'll look at this later on. One thing you might have noticed is that these texts input types only allow one line of text. To add a text input that allows more than one line of text, we use a text area element. Let's add one to the farm that the user will use to add a description of their tickets. As with the other inputs, we'll also add an associated label. Note that this isn't an input element with a type of text area. It's a text area elements. And unlike an input element, you have to have the opening and closing tags. In the browser. The text area is shown with more than one line. And you can press enter when typing in this control to add a new line. 6. Add a CSS Style Sheet to Easily Add Formatting to the Form: Before we look at more types of inputs, Let's add some styling to this farm so that it's easier to see what's going on. Styling with CSS is beyond the scope of this class. But two quick way to add styles when developing a farm 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 elements so that the styles are applied. However, an even faster way of styling of farm, so you can concentrate on the functionality is to use some classless CSS. There are many of these available, and you can use these by simply including the CSS file in the HTML. In this class, we're going to use water CSS. You can download the CSS and serve it locally. I'll include the stylesheet directly from a CDN, which is what we'll do. We'll copy this line of code and paste it inside the head element of our HTML. Now in the browser, the farm has some nice styling without us having to have added any classes, including highlighting when the input control has the focus. 7. Add a Control to Select a Value from a Drop-down List: With text controls, the user can enter any value they want. Let's add a control where the user can pick from a predefined list of values. For a list of values, we use a select element. Each option inside the list is an option element. Let's add three options for the priority of the ticket, low, normal, and high. Before we view this, Let's add an associated label. The browser. The select element is shown as a drop-down list where you can choose one of the supplied options. If I reload the page again, holding down the left Shift key on the keyboard to reset the farm. 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 normal 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. You can have as many options in the select list as you like, as the list will appear with scroll bars if it's too long for the screen. 8. Add Controls to Select One Value From a Range of Options: With the select control, the list of options is only shown in a drop-down list when you click on the control. This allows you to have a lot of items in this list if you need to. For example, a list of countries. If you have a small number of options and you want them to always be visible in the farm. Then an alternative to this is to use radio buttons. Radio button is an input element with the type radio. To add a label to this. Instead of adding this as a separate element, Let's enclose the input inside the label. Will use this control to specify the category of the ticket. So let's add a label with the text of fault. This is an alternative way of associating a label to a control. The advantage of this is you don't need an ID attribute on the control and a four attributes on the label. Let's add another radio inputs just after this one with a label of suggestion. 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 one. Now in the browser, when we select one option, the others are on selected. In a group of radio buttons like this, only one option can be selected at once. If I reload the page again, note that 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 farm 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 elements as we're using one for each option. Instead, we enclose them in a field set elements. Then add a legend element just after the opening field set tag containing the text we want to display as the caption for this group of controls. The browser. This surrounds the controls with a line with the text from the legend element shown at the top. Finally, by default, radio inputs are shown in line, in this case being shown next to each other horizontally. To display each on a separate line. A simple way to do this is to add a line break element between them. Now in the browser, the options are shown vertically. With a select list. You can have as many options as you like, but radio buttons are best suited to a limited number of options as they always visible on the farm. 9. Add a Control to Choose Between Two Mutually Exclusive Options: Next, let's add a checkbox control that lets us select between two mutually exclusive options. In other words, yes or no, on or off and so on. The 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 and closing the inputs and the labeled text inside a label element. Let's add a checkbox that the user has to select to agree to some terms and conditions. The browser. There's the checkbox. We can check and uncheck it by clicking on the box itself, are on the text of the label. You can have as many checkboxes on a farm as you like. And you can place them inside a field set as we did with the radio controls. If you need to. Multiple checkboxes can be used for a value where you can select more than one option. 10. Add a Button and Attributes to Send the Form's Data to the Server: Now we can enter some data into the farm. We need a way to send this data to the web server. First, let's add a button to the end of the farm. Will add a button element with the content between the opening and closing tags being what is shown on the button. I'll just leave some basic text, but you can put in other tags in here if you'd like. I'll also put a line break element before this so that the button is on a new line. In the browser. There's the button. If I press it, referred to as submitting the farm, the farm is redisplayed. What has happened is the form has been submitted to itself. In other words, a request has been made to the same URL as the farm with the data from the farm attached to the request. What has changed is the URL in the browser's address bar. Query string has been added with a key value pair of category equals on. If we look at the various inputs, note that the only ones with a name attributes are the category radio buttons. Only inputs that have a name attribute will have their value sense when the form is submitted. To demonstrate this, let's add a name attributes to the first input with a value of name. If we reload the farm and then submit it again. Now the query string contains an additional value for the name. If I enter a value into the name input and submit it, this value is included in the query string. Let's do the same for the other inputs. Adding name attributes to the e-mail, description, priority and terms and conditions checkbox. Now if we submit the farm, we see the values in the query string for all of the fields. If I enter a value for one of these, then this value is the one that is sent. 11. Add a File to Process the Submitted Form Data and Change How it's Sent: Currently when we submit the farm, a request is made to the same page and the data from the farm is sent in the URL query string. This is the default and can be used if you add some code before the HTML to process the data. However, will process the farm data in a different file. First, let's add a new file called process farm dot PHP. Back in the farm. To make this farm submitted data to this new file, we add an action attribute to the form tag 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, the new file. Let's add the PHP opening tag. The data from the query string is available in the cat super global. Let's just print that out. Now if I enter some data into the form and submit it, the process farm PHP script is requested with the data still in the query string. The body of the response, we see the contents of the GET array. Each array key comes from the name attribute of each input in the HTML, and its value is whatever was entered into the farm. Having the data in the query string like this is fine if the form is a search engine, for example, as it's the sort of URL that you can share our bookmark. If you want to do something with this data, like save it to a database, then we don't want it in the URL. This is because there is a limit to how much data can be passed in the URL. Plus it's a security risk. There could be sensitive data in here, and these URLs are saved in the browser's URL history. The data from the farm could easily be viewed by anyone with access to the browser. Instead, we'll send this data using the post method. To do this, we add a method attribute to the farm tag with a value of post. You could specify get here, which is the default we've been using up to now. Now when the data is sent to the script, it will be available in the post super global. Let's print that out instead. Now when we enter some data and submit it, we still see the same data in the response body. But now the values aren't shown in the query string. 12. Change the Values Sent from the Select, Radio and Checkbox Inputs: Let's have a look at the values that are being sent from the farm. The priority is a select control displayed as a drop-down list. The value sent when the farm is submitted is the text of the selected option. You can do this if you like. But it's common to give each option a different value using the value attribute. Let's assign each option a numeric value from one to three. Now when we submit the form, the value that is submitted is whatever the value attribute contains for the selected option. This is useful if you want to store a different value than just a text label for our fields like this. It's also useful, for example, if you're displaying your content in more than one language. So the labeled texts might be different, but the underlying values are the same. Let's also have a look at the category radio controls. No matter which one I choose. The value submitted is always the string on. This is because this is the default value for a radio type inputs, for the radio buttons to send a different value depending on which one is selected. We need to give each one a value using the value attribute. These values can be whatever you like. They don't need to match the text as we just did above with the select control. Instead of numbers though, this time, I'll just use some simple strings. Now when we choose the radio button, this is the value that is submitted. Next, let's look at the checkbox. If I don't check it, no value is submitted for this control. If I do check it, then the value is the string on. You can add a value to this in the same way as with other inputs, adding a value attributes. Let's add a value of one. Now if I check the checkbox and submit the farm, this is the value that is submitted. If you only want to know if the box is checked or not, then the default value is enough. For our purposes, we will remove it. If you have several checkboxes though, and you want to know which ones are checked, then you can give each one a separate value. 13. Avoid Errors if a Value is not Part of the Form Data: Now we have the values we want coming from the farm. We can get the specific values in the PHP scripts ready for eventually inserting into a database. As we've seen, the values from the farm will be in the post array. Instead of printing out the whole array, let's assign the value for the name input to a variable, then print this out. If I enter a value for the name and submit it, that is the value. Let's see what happens if I change this index to a value that isn't part of the farm. For example, surname. Now if I submit the farm, we get an undefined a Reiki error. Although we won't get this error. If an input with this name is part of the farm, we still need to check if these values exist in the post-survey or not before we try and use them. This is because the farm could easily be bypassed. We'll see an example of this later on. One way to check this would be to use a function like a Reiki exists along with the ternary operator to set the name variable to know if the value isn't in the array. Another shorter way would be to use the null coalesce operator, which does the same thing. Another alternative is to use the filter input function. This function will get a value from the specified input and simply return null if it's not present. Let's call this function specifying that we want to get the value from the post inputs, the name of the value we want to get. Let's try this first with a name that isn't part of the farm surname. When we submit the farm. Now we get null instead of an error. If we change this to name, which is part of the farm, we get the value from the inputs. 14. Apply a Filter to the Form Values, Validating the Data Type and Range: In addition to returning the value from the input and just returning null if it's not present. The filter input function allows us to apply a filter to the value. This means that far the e-mail input, for example, we can apply a filter to validate that the value is a valid email address. Let's call the filter input function again, this time for the e-mail input from the farm. The third argument will specify the validate email filter. Instead of printing out the name variable, we'll print out the email variable instead. If I submit the form with an empty e-mail address, we get false. If I submit a valid email address, then we get that value as a string. If I enter an invalid email address, then the browser validates the field and requests a valid email address without the form being submitted. We'll look at client-side validation in the browser shortly. But for now, let's just disable this by adding the novalidate attribute to the form element in the HTML. Now if I enter an invalid email address and submit the farm, we get boolean false. Again. We only get the email address as a string if it's valid. Otherwise we get false. Some filters have options to perform additional validation. For example, the integer validation filter. In addition to validating that a value is a valid integer, we can specify a range of values. A value that's a valid integer, but outside of this range will return false. The value for the priority field in our farm is an integer 12 or three. Let's add another call to the filter input function for the priority field, specifying the integer validation filter. In the next argument, we specify the options for this filter will specify a minimum of one and a maximum of three. Then instead of printing out the email variable, will print out the priority variable we just added. When I submit the farm with the default value for our priority, we get an integer value of two. For other priorities, we get the equivalent integer value. Note that all values coming from the farm will be strings, but this function will convert those two integers if valid. Test the range validation. Let's temporarily change the maximum to two. Now if we submit the farm with a priority value that corresponds to three, we get boolean false. The value will still be a valid integer, but it's not within the range that we specified. Let's change this back to three, so it accepts all the values from the list. 15. Filter the Checkbox Input and Optionally Filter the Values into an Array: Next, let's look at the checkbox value. As we saw earlier. The checkbox isn't checked. No value is submitted. If it is checked, the default value is the string on. Let's call the filter input function again, getting this value. As all we want to know for this is if it's checked or not. In other words, we're not interested in the value. For this. We'll use the Boolean validation filter. This will return true if the checkbox is checked. Then instead of printing out the priority variable, will print out the terms one instead. If we submit the form without selecting the checkbox, we get no. If we check the checkbox, we get boolean true. Now we're getting filtered values for most of the inputs on the farm. The only one's missing the description and category. Let's add calls to the filter input function for the description. And farther category, not specifying any filters as these are strings, just like the name. Now we have one variable for each farm field that contains the data that was in that field when the farm was submitted ready for further processing. This is one way to do it with multiple calls to the filter input function. There is an alternative way to do this using the filter input array function. This allows you to filter several farm inputs at once and obtain an array of the filtered data instead of separate variables. Let's call the filter in portrait wavefunction filtering the post-data from the farm. For the second argument, we pass in an array with one element for each farm field we want to filter. If we're not specifying a filter type, we can set the value to null to the default filter flag. For inputs where we're specifying a filter, we set the value of the array elements to the filter constant. And if the filter has options like the priority field, we pass in an array setting the values like this. This returns an array of data for the specified farm values. If we print that out instead of the terms variable. When we submit the farm, we get an array containing the filtered values from the farm. The filters have been applied as with the individual variables. But now we have one array variable containing all the values instead of one variable for each firm value. It's up to you which method you use when filtering input data. If you prefer individual variables or an array. 16. Validate the Form Data and Output a Validation Message if Invalid: Now we have the data from the farm, we can validate it. I'm going to work with an array of data, but you can work with individual variables if you prefer. I'll comments these variables out there in the downloadable source code. We want to validate these values before we try and save them to the database so that the data in the database is always valid. Let's start by validating the name. If a value has been entered in the name field of the farm, then it's considered valid. If the field was left empty, then this will be an empty string. If the value isn't part of the farm data than the filter function above will return null or an empty string or null are invalid values. We could compare the value of this to an empty string and null using a comparison operator. But the empty function will return true. If it's either of these. If the value is empty, then it's invalid. To keep it simple. If it is invalid, it will exit the script using dye printing out to suitable message. This validation is unlikely to be seen anyway, if we have client-side validation in the browser, which will add shortly. Let's give that a try. If I submit a farm without putting anything in the name field, then we get the message saying that the name is required. If I enter a value, then we get the array of values printed out as before. Let's do the same for the email field. If this isn't present, it will be no. If it's not a valid email, it will be false. And otherwise it will be the string value. The empty function will also return true. If the value is false, we can just use that again and again will exit the script with a suitable message. Now if I submit the farm with a valid name but an empty e-mail address, we get the e-mail validation message. If I enter a value in the email field, but it's not a valid email. Then we get the same message. If I enter a valid email, then we get the data. The same way we can validate the description, the priority, and the category. The category. In addition to validating that it's present, we can also validate its value. The category will be a string that comes from the values of the radio buttons, either fault or suggestion. Just in case the farm is bypassed. We need to check that the value is one of the strings. The simplest way to do this is to use the inner array function, checking to see if the value from the farm is one of these values. If it isn't, we'll exit the script with a message. To test this, let's temporarily remove the fault item from the array. Now if we submit the farm with the default category selected, we get the category is invalid message. If we select the other category, then it passes the validation and we see the data. Now we've tested that that works. We can add the fault category back in again. Finally, we can check that the checkbox has been checked. The filter will set the terms value to boolean true if it's being checked. Otherwise it will be no. Let's check with this. And if it's not true, will stop the script and outputs a message. If I submit the farm without checking the checkbox, we get the message. If I check it, the validation passes and we can see the valid data printed out. 17. Validate the Form using Client-Side Validation in the Browser: We're currently validating the data on the server, printing out validation messages as necessary. We can prevent a request to the server though, by validating the farm in the browser first. This is also more user-friendly as it gives the user the chance to correct the farm in the places where the data are invalid before it's submitted. Earlier on. In the farm. We added the novalidate attribute to the farm to temporarily disabled the validation. We can remove this now. Then let's add the required attribute to the required fields. Name, e-mail, description, and terms. As the select list farther priority. And the radio buttons for the category have default values. We don't need to add the required attribute to these. The browser. Now if we try and submit an empty farm, we get to validation message for each required field that is empty. What small the validation message is next to each field. And the focus is placed on the field that needs to be changed. As far the email address as this is an email input type. We also get to validation message if the value is not a valid email address. For the checkbox. Marking this as required requires the user to check this before the farm can be submitted. If I check it, then we can submit the farm and we see the data printed out. Now you might be thinking that's if we're validating in the browser, we don't need to validate in the PHP script on the server. We add validation in the browser because it makes the user experience much nicer and reduces the number of requests we make to the server. However, it can be easily bypassed. One way to do this, for example, is to send a post request to the process farm PHP script using curl. Curl is a command line tool for making HTTP requests. So let's use curl specifying the request method as post. And we'll add a couple of foreign values which we add like this. We'll send this request to the process from PHP script on our server. We get the response description is required. This is the message from the server side validation because we've sent a value for the name and the email, but not farther description. There are other ways to farm could be manipulated by using the Developer Tools in the browser, for example. Basically, client-side validation is recommended. But the server side validation is essential even if the user never sees it, because the data are already valid by the time the form is submitted. Code that processes farm data should always validate this data on the server. 18. Create a Database to Store the Data from the Form: Now we have the data from the farm and it's been validated. We can insert this data into a database on the server. I'll be doing this in the MariaDB database server that I have installed locally, which for all intents and purposes is the same as MySQL command shown are valid for either DBMS. You'll have a similar setup if you installed the suggested software at the start of the class. Although feel free to use any relational database you like. I'll be doing this on the command line. But if you prefer, you can use your GAVI of choice, such as PHP, my admin, or MySQL Workbench. Let's start by connecting to the database server on the command line using the root account. Then we'll create a new database, which I'll just call support dB. Then we'll create a user just for accessing this database. So we don't have to do it using the root account. User will have full privileges on all the tables in the new database called the user support DB user. It's on the local host server. And we'll assign a random password. You need to make a note of this password for when we connect to the database later on from PHP. To check this works, we can exit from the console and reconnect using the user and password we just created. Then we can select the database with US. And that selects it with no problems. 19. Create a Table in the Database to Store Ticket Records: Now we can add a table to the database to store ticket data. Let's create a table called ticket with the following columns. An ID column that's an integer, can't be null. And we'll set this to auto-increment to assign values automatically. Then a name column, which is an, a, a 128 character varchar that can't be known. Next, a column for the email, which is also an 128 character voucher that can't be null. Then a description column, which is text and also can't be null. Next column, further priority, which is a non-null integer. And finally a column called category, which is a 32 character var char, that's also count be no. Before we close the parentheses will make the ID column the primary key. Let's run that. That ran with no errors. Let's have a look at the table we just created. And there it is. As mentioned before. You can do this in a GUI like PHP, my admin, if you prefer this SQL to create, this table will be available in the classes downloadable source code. 20. Connect to the Database from PHP: Now we have the database and the table we can connect to the database from PHP. We no longer need to print out the data from the farm. We can remove this line from the process farm script. First, let's create some variables to contain the connection details for the database server. The host is the same as the web server localhost. The name of the database we just created is support DB. And we'll create two more variables for the username and password we just created to connect to the database. We call the MySQL I 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, okay. The MySQL connect error number function returns an error code from the most recent connection attempts. If there was no error, this return 00 is equivalent to billion false. We can check with this by simply using this as the condition for an if statement. If a connection error has occurred, we will stop the script as above and print out a message. We can get a description of the error by calling the MySQL connect our 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. If I enter valid data into the form and submit it, 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. The details are correct again. Once we've connected, we can use this connection to make database queries, as we'll see shortly. After we finished with it, we can optionally disconnect from the database using the MySQL lie close function, passing in the database connection as an argument. This step is entirely optional, as when we get to the end of the request, the database connection is closed automatically anyway. You can include it though if you prefer. 21. Insert a New Record into the Database from PHP: Once we've successfully connected to the database, we can insert a new record. To insert a new record will use an SQL insert statement. Here in the database. If we have a look at the structure of the ticket table we created, we can see that the ID column is an auto-increment field, so we don't need to provide a value for that. Value will be assigned automatically. All the other columns don't allow null values. We need to provide non-null values. Father. When we insert a new record, Let's insert a new record directly in here so we can see the SQL that we need. As before. You can do this in a GUI like PHP, my admin, if you prefer. First, let's select all the rows from the ticket table. And we can see that the table is currently empty. Insert a record. We use the insert into statement specifying the name of the table, which is ticket. In parentheses, a list of the columns that we're going to insert values into. As I just mentioned, 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. I'll just put some example values. Note that string values needs to be enclosed in single quotes and numeric values like the priority column do not. When we run this, we get a message saying it was okay and one record was inserted. And if we select all the rows again, we see the record in the table, including the value that was assigned automatically to the ID column. Now we can do the same from PHP. First, let's remove these last two lines. Then let's create a variable that will contain the SQL. The first part of the insert statement where we specify the columns and the values keyword. Now we need to include the values from the farm which are in the data array. There are several ways we could build the SQL string. For example, variable interpolation, string concatenation, and so on. Let's go with string concatenation. We'll concatenate each value with the next, separating them with commas. The end, we'll close the parentheses. Let's not forget that we need to surround string values with single quotes. We'll add that to the string values, which is all of these except the priority. Before we execute this, let's print out the SQL string to see what it contains. If I submit some valid data, There's the SQL string. Instead of printing this out, let's run it. To execute this SQL, we'll use the MySQL query function, passing in the connection object and the SQL string as arguments. If this fails, it returns Boolean false. So let's check for that. And if it does fail, we will stop the script with a message. We can get a description of the error message using the MySQL error function passing in the connection object. If it succeeds, for now, we'll just print out a message. Let's give that a try. If I submit some valid data, we get the records saved message. The ticket table in the database. There's the new record that we just inserted. The data for this record in the database table has come from the farm in the browser. I won't demonstrate what happens when this fails just yet. As we'll cover that shortly. However, there is a very big security problem with this code, which we'll look at in the next video. 22. Manually Escape String Values to Avoid an SQL Injection Attack: We just saw how this works. If I just enter simple values into the farm, a new record is inserted into the database. However, let's see what happens if I enter a value that contains a single quote. For example, a surname like O'Connor. When I submit the farm, we're told there's an error in the SQL. Let's temporarily print out the full SQL string to see what it contains. Now, when we run the code, there's the SQL string. The problem is here. When we construct the SQL string, we surround each string value with single quotes. However, as this value includes a single quotes, the database things that the string ends here. Following the string, it expects a comma and the next value. However, the rest of the name is there instead. Making this invalid SQL. At best, all that will happen with this is that the SQL is invalid. The user will see an error message and the record. What would it be inserted? At worst though, this could be used to inject some SQL into the SQL string, known as an SQL injection attack. An attacker could put some SQL into the form inputs, starting with a single quote that could delete some values or even retrieving admin password from the database. To avoid this, we need to escape single quotes within string values by prefixing them with a backslash character. You could do this manually by using the string replace function for example. But there is a better way. The MySQL real escape string function will escape any special characters for use in an SQL statement. Additionally, taking the character set up the database connection into account. Let's call this function for each value we're inserting into the SQL, passing in the connection object as the first argument each time. Although the priority isn't a string, I'll call it far that value too. Just to make absolutely sure we aren't inserting an untrusted value into the SQL without escaping it. Now if we submit the farm, we can say that the single quote has been escaped by being prefixed with a backslash. Let's remove the line where we're printing out the SQL. Refresh the page in the browser. Now we get the record saved message in the database. There's the record we just inserted containing the single quote value. Note that it's not escaped in the database. The value there is as it was typed into the farm. Also, note that when we do get a database error, it's more common to have a nicely formatted error page as opposed to just the error message like this. And in a production application, this is something he would do. For the purposes of this class though, we'll just keep it simple, like this. 23. Use a Prepared Statement to Insert a New Record: We just learned one way to avoid SQL injection attacks. And that's to escape any special characters in untrusted values when we create the SQL. However, the problem with creating an SQL string like this is that it leads to code that's difficult to understand and therefore difficult to debug and maintain. It's also very easy to forget to escape a new value if we add a new column, for example. An alternative way to do this that still avoids SQL injection attacks is to use a prepared statement. Let's start by changing the SQL. Will remove all these code where we're inserting the values manually. And instead, for each value, we'll just put a question mark. This is known as a placeholder. Note that we don't have to put quotes around the string values, just a question mark where the value will go. We're not going to run this with the MySQL query method. We can also remove that. Instead, we call the MySQL statement init function, passing in the connection as an argument to create a prepared statement objects. Then we call the MySQL statement per pair function, passing in this object and the SQL string. This returns a Boolean success value. Let's check to see if this is false. And if so, will 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. Next, we connect values to the individual placeholders in the SQL string, which is known as binding. To do this, we call the MySQL statement bind parameter 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. Ai for integer values, the four double values, as for strings and B for blobs. 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 email, another one for the description. An eye for the priority as this is an integer. And finally another S for the category. The rest of the parameters are the values of these variables. The name, e-mail, description, priority, and category from the data array. 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 MySQL statements execute function passing in the statement object as an argument. Notice that unlike earlier, it's not possible to get the SQL that will be generated and print it out in the PHP code. This is because the binding of the variables happens in the database server and not the web server. Let's give that a try. If I include some values in the farm that contain single quote characters. When I submit the farm, we see the records saved message in the database. There's the new record containing the values from the farm. It is slightly more complex to execute an SQL statement using a prepared statement. But the SQL itself is much clearer and therefore much less prone to errors that can be caused by string concatenation, as we saw. 24. Use the Post-Redirect-Get Pattern to Avoid Duplicate Form Submissions: So far we've inserted several records, which we can see here in the database. For this video, I'm going to empty the table using the truncate statements so that it's easier to see what's going on. With an empty table. Let's enter some data into the farm and insert a new record. At the moment, we see the record saved message in the database. There's the new record. However, without going back to the farm, if I refresh the page in the browser, first we get a message asking us to confirm the farm resubmission. This is because what's shown in the browser is the result of a post request. And if we refresh it, that data will be sent again. We confirm this. We see the record saved message again. The database. There are now two records with the data from the farm. Every time we refresh this page, a new record will be inserted. If you've just done something like buy something or send a message, then you don't want this to be easily repeated. The standard way to avoid this is to use the post redirect get pattern after a successful post request. Instead of responding with the content to be displayed, the browser is immediately redirected to another page. First, let's add another page that we can redirect to. Let's add a new file called success dot HTML. The form, let's copy the first part of the HTML down to and including the header element and paste it in the new file. Instead of a farm, Let's just add a paragraph element containing the text. Thank you. And the closing body and HTML tags. The process farm scripts. Instead of printing out this message, this point, we've successfully inserted a new record. Here, will redirect to another page. We do this by sending a location header containing the URL we're redirecting to. And we'll also set the response status code to 303, which indicates we're redirecting to a confirmation page. To send the header, we call the header function, passing in a string containing location, a colon and a space. Then the URL we're redirecting to, which in this case is just the relative URL success dot HTML. To set the response code, we'll use a named arguments to avoid having to set any other required arguments in this function call, setting it to 303. This is the end of this script, but it's generally good practice after redirecting to exit the script immediately afterwards. So we'll include an exit call. Let's give that a try. Now with some different data in the farm. If we submit it, we get redirected to the success page. Now if I refresh the page, however, were not shown any message asking us to confirm the pharmacy submission in the database. Only one record has been inserted. 25. Your Class Project: Create a Form and Save the Form Data to a Database: Now it's your turn. Your project for this class is to create a farm to capture some data and save that data to a database. Start by designing a farm that captures the data that you need. For example, a sign-up form, an artist farm, and so on. Use the relevant input types for each item of data. Next, create a PHP script to process the submitted farm data, making sure it's all valid. Then create a database with a table to store the data from the farm. Use different column types to store each item of data as necessary. Once you have the database table, you can write the SQL and PHP code to insert a record into this table. Finally, redirect to a success page. Once a record has been successfully inserted. You can use these documentation websites as a reference for the HTML, SQL, and PHP code you are writing. Plus the code shown in the class is available for download. If you need help, you can ask a question in the class discussion section below. I look forward to seeing your projects. 26. Conclusion: Congratulations, you've finished the class. In this class, you've learned all you need to know to create a farm in HTML containing inputs for different types of data, how to validate that data in PHP, and how to save the data from that farm into a database on the server. This is one of the key skills for any web application developer. If you're just starting out or you're an experienced developer looking to expand their skill sets, you just learned some important techniques for this much requested functionality. Don't forget to post your projects in the class's project gallery. I look forward to seeing them. Finally. I just wanted to thank you for taking this course. I hope you enjoyed it. If you did, please consider leaving a review. Plus if there's a topic you'd like me to cover, please don't hesitate to let me know. I'll see you in the next class.