PHP Tutorial (& MySQL) #30 - Saving Data to the Database

all right then gang so I wanted to do one little thing before we moved on and that's just to add the links to these things in the Navar so this should go to the form right here and this should go to the homepage so I've gone to the header already come down to the template and this right here the brand logo I've sent to index.php which is the homepage and this down here which is the button I've sent to add.php so now these things work okay like they should Okay so in this video I want to talk about how we can save data to the database and we're going to do that on the ad page over here so when we enter in the details we're going to click submit then we're going to run this file again when we submit the form and we're already doing that if we have a look in add.php we're already looking for any kind of errors and validating the form and so far all we're doing if there's no errors is we're relocating to the index page but right here this is where instead we want to take that data and we want to save it to to the database so how are we going to do that well the first thing we need to do is actually connect to the database inside this file we've done it inside the index.php right here but we also need to do it in this file because this is where we're going to be saving the data from so what we could do is copy and paste this code right here over here where we connect to the database and then check the connection but that's kind of just repeating ourselves and I don't want to do this every time we have a new file where we connect to the database so instead what I'd like to do is externalize this code modularize it in its own file then just include it whenever I need it so that's what we're going to do first of all so I'm going to create a new folder and call this config and then inside that config folder I'm going to create a new file and this will be saved as dbor connect. PHP because that's what this is doing then I'm going to copy or rather cut that connection script open up my PHP tags and inside here I'm going to paste that back in so that's all this file is going to do connect to the database using these credentials and then check the connection now we need to include it over here in the index.php where we used it before so I'll say include and we want to include SLC config to go into that new config folder then slash dbor connect. PHP okay so that should still work so let me just ref the index over here to make sure it still works okay and we got an error so let me see what's going on over here and this is called dbor connect and that is called dbor connect and it's because we have this forward slash right here we don't need that save this and refresh and now it works perfect okay then so now we can require as well or include this file inside the ad. PHP because we need to connect right here as well so let's do that first of all at the top I'm going to say include and we want to include the config SL dbor connect. PHP file okay and then down here in the if check this is where we're going to save the stuff to the database in this else Clause because at this point we know there's no errors if there are errors this would fire and we're outputting those errors down here in the Dom but when there's no errors we're just relocating them so this is where we need to add that data to the database and then relocate them to the index.php page okay so we have the data that we want to add to the database right we have now an email we have a title in this variable and we also have the ingredients now before we directly save them to the database what I'd like to do is use a function and that function is called my SQL I real Escape string and we're going to pass those values into that function and what that does is escape any kind of malicious SQL characters or any sensitive SQL characters and it protects us from SQL injection from people inserting harmful code into our database right now this is a bit like this but whereas this is what outputting values to the browser this other function I'm about to use is for protecting data going into the database okay now there are other ways to do all this using a PDO approach and prepared statements but since we've not covered PDO I'm showing you this way for now then maybe I will do a series on the PDO approach and prepared statements in the future because it's something you should definitely learn when you become more comfortable with PHP and MySQL but anyway we'll go with this way for now so what I'm going to do is override the values we currently have for the email title ingredients by saying email is now equal to my SQL iore real Escape string like so and then we pass in first of all the connection okay remember we make that connection reference right here and the second parameter we pass in is the actual value that we want to store in the database and that is going to be the email right so what I'm going to do is just copy this thing so post email okay so the value that we get back from the user and the connection and we're storing that now in this email variable so essentially we're overriding this email variable from before okay so then that's the first one what I'm going to do is just duplicate this a couple of times and we're going to change this to the title and the title over here and then we also need the ingredients so let me just control scoll click to do both of those and call this ingredients like so so now we have the data ready that we actually want to input into our database okay so the next thing to do is create the SQL string that is going to go and do that for us so I'll create a new variable comment first of all create SQL a new variable now called SQL and set that equal to a string now much like we used this stuff before to get data from a database this time we're using a different kind of command to add data to the database and what we want to do is use the keyword insert and then we say into to say which table we want to insert data into now we want to insert into the pizz table remember that's what we called our table and the things we want to insert are the title the email and the ingredients so we're going to basically insert into the pizzza table these three things that's what we have right here yeah now then the values that we want to inject or insert are going to be as follows and what we're going to do is enter in these different variables right here because they're the values we want to add into these things into these different columns so we have to do this in quotes so I'm going to do first of all the title and that's not in capitals then the email and then finally we want the ingredients like so and then finally close off the whole string like that so pretty simple all we're doing is saying okay insert into the pizza's table and we want to update these columns and then these are the values for those columns in order now the other two columns remember are created out which is going to get automatically created when we make a new row and the other one is the ID which Auto increments remember so that gets created for us as well so these are the only three we need to worry about adding so that's the SQL that we need now we want to save it to the database and check that it's worked so we'll say save to DB and check okay then now I'm going to write this out then I'm going to quickly explain it so my SQL iore query we've seen this this is the function we use to make a query and we pass in connection first of all and the SQL we want to use for that query and that's nested in an if statement now then this thing right here this is the function like I just said to make a query we've already seen it right here okay we didn't Nest it inside an if statement right here but we still made that query right here we passed in the connection and the SQL which in this case was to select data into that query and that gave us a result now over here what we're doing is nesting that in an if statement now the SQL we're passing through is this stuff to add to the database the table so if that's successful this query this will return an equivalent True Value and right here this would be a success now if it didn't then it would return a false value and there would be some kind of error and we'll Echo that error out so we'll do that first Echo and then a string we'll say query error and then we're going to concatenate to that string and I'll just say my SQL iore error and then pass in the connection okay so this is the error generated if there is one I'm going to Output that to the screen if this fails if this query fails and if there's a success then it means it has saved it to the database and what we do at this point is redirect them okay so we take that from here we don't need this anymore and we redirect them right here to the index page once that's been saved so then let's cross our fingers and hope this all works I'm going to go to the ad Pizza page what we got so far we've got ninja and Mario let's do the Yoshi Supreme so Yoshi at theet ninja. co.uk the pizza title is the Yoshi Supreme and that's going to be cheese tomato and eggs okay submit and now we can see that right here awesome so that has worked so now we know how to save data to the database what I'd like to show you in the next video is how when we click on more info we're going to show the details the extra details of individual records individual pizzas