PHP, PDO, MySQL, and phpMyAdmin for Beginners | Coding Academy | Skillshare

PHP, PDO, MySQL, and phpMyAdmin for Beginners

Coding Academy, National Award-Winning Teacher

PHP, PDO, MySQL, and phpMyAdmin for Beginners

Coding Academy, National Award-Winning Teacher

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
26 Lessons (2h 34m)
    • 1. Introduction

    • 2. How to create a database in phpMyAdmin

    • 3. How to retrieve data from a database table

    • 4. How to crate a database table in phpMyAdmin

    • 5. TASK 1: Creating the Teacher Table

    • 6. TASK 1: Creating the Teacher Table - SOLUTION

    • 7. How to update records in phpMyAdmin

    • 8. How to make a PDO connection to the a MySQL database

    • 9. How to use a try catch block

    • 10. How to create a database record using PHP

    • 11. How to retrieve database records using PHP

    • 12. How to update database records using PHP

    • 13. How to delete a database record using PHP

    • 14. TASK 2: The Book Store

    • 15. TASK 2: The Book Store - SOLUTION

    • 16. How to integrate PHP and HTML

    • 17. Adding an edit icon

    • 18. How to create the edit page

    • 19. How to populate the edit page

    • 20. How to use fetch

    • 21. Error trapping

    • 22. How to update a database record via a HTML form

    • 23. TASK 3: Deleting a Record

    • 24. TASK 3: Deleting a Record - SOLUTION

    • 25. TASK 4: Creating the form and script to INSERT a record

    • 26. TASK 4: Creating the form and script to INSERT a record - SOLUTION

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

In this course you will learn how to use PHP, PDO, MySQL and phpMyAdmin to create, read, update, and delete records in a database.

You will learn how to:

  • create a database using phpMyAdmin
  • upload a SQL dump
  • create a database table using a SQL dump
  • populate a database table using a SQL dump
  • create a table using a CSV file
  • populate a database table using a CSV file
  • CRUD
  • create, retrieve, upload and delete records using phpMyAdmin
  • create, retrieve, upload and delete records using PHP
  • create a HTML form 
  • use Bootstrap to style your form
  • use Font Awesome icons
  • use a HTML form to edit a record
  • set user permissions

If you are a beginner to using MySQL then this is the course for you.

I will guide you through the basic steps involved in securely creating a connection to a MySQL database.

Meet Your Teacher

Teacher Profile Image

Coding Academy

National Award-Winning Teacher


Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.



1. Introduction: If you're watching this video, then obviously you're interested in this course. Now this course covers PHP my sequel PDO on PHP. My admin and it puts them all together it with some very simple projects for you to complete. Not, for example, here we have a little bookshop on. There's a list of books here we can click on to edit one of the books we can click in. We can change some details we can click on to update the record. We can go back and we can click to delete. When I click on delete, I get a little message to say that record has been deleted. We will delve into the backhand. We'll look at PHP. My admin will manually create databases and fields and records, etcetera. We're then going to the front end. We start writing some scripts. We look at Haiti. Mao, we look at PHP Were Isom sequel queries on We talk a little bit about security as well. Actually throw in a little bit of front awesome with some of the icons that we use Always a little bit of Twitter. Bootstrap Aziz. Well, if this sounds like the course for you then make sure you enroll today. And as a little teaser, I've opened up some of lectures for you to actually watch. If you like what you say you will of the rest on. I hope to see you in the course. 2. How to create a database in phpMyAdmin: in the last lesson, we learned how to a secret a server to create a folder within that server and then to create a file. We had its, um, pitch p to the Fail and then tested that in the browser to see that actually worked. I want I want to do this lesson is to actually connect to PHP my admin to create a Dan abyss and actually populate that with some data. But before I do that, I thought would be nice to go into preferences. Could a user and general. And if you recall, we set up the auto save on one thing that happens is they actually plays a little ding you might have had in the previous video, and I want to switch that off. So in Sands here, I'm going to slide that Slater off Onda click on Save. They'll stop that little ding. Okay, so let's move on with the main focus of this lesson, which is to connect to PHP. My admin. So I'm going to click on my server Tab Onda. What I want to do it won't just go back to the route off the server. In other words, there's no forward slashes and no Section one on test PHP on the end here on will change this year. Out forward slash and type in PHP my admin percenter. And here we are at the Larkin screen off PHP. My admin. Now the default using him is route on. The password is just blank. Now I know that's not secure, but for the time being, we're going to leave it as that click home. Go now. If you never use PHP Miami before it can actually seem quite daunting. And I could actually produce a whole course just on PHP. My admin now we don't want to go into every single detail in PHP may mean we want to some quick winds. So into the words I want to set up a database. I want to populate that data bit. I don't want to start grabbing the data and manipulating and playing around that data busy as quickly as possible. In little lessons, we will come back to this and we're going to a little bit more detail of some of the features on what we can actually do with it. So the first thing I want to do is on the left hand side. We're not going to touch these three databases here. What we want to do is to actually click on this tab here databases, and we're going to create a database called Company and Click on Create. It's not databases being created, and we can see on the left hand side here. That's a much in my company. I have some users, so what I want to do is we want to create a user table so factory can company. What I could actually do here is accurately creates a user table, specified the number of columns click on, go on, then start to populate it with some user data. I don't want to do that. What I want to do is I want to go for that quick winners I said. So way to go to a website called generate data dot com. So now that we're in here, the first thing I want to do is to click on and select a country. I'm gonna go for United States Here. The column name for the 1st 1 Well, I want the first name for the 1st 1 The data type will be a name on the example would be any gender so male and female. The 2nd 1 Let's have surname and again the data type will be. A name on this example would be a surname. I think it's nice to have the date of birth, date of birth, brother on the date of birth. We need to have a debt and the type I'm gonna go for my sequel, debt time, the from and to days that says, like something reasonable. So click in the year. Let's go back to learn about the 19 fifties or so. Look at that there and we'll go to the early nineties and click out under the debt. Now the format of that day is saying the year, the month of the days, which is what we want. But the hedge I and S is for hours, minutes and seconds. I want to delete that. So just year, month and day and in the 4th 1 is going to be email that a type will be email on no examples available. That's fine. Next section we don't want to HTML we want s Q l on the database table name. I'm going to call it users the database titmice equals correct include a create table query . Well, yes, We do want to create a data table. If you recall in pitch peed my admin. We did not create the table, so I said, we're going to let this do it for us. Do you want a drop table query? Well, no, we don't because we create a table, then drop it so we'd get an error, so we don't want that. So next thing is this. Add default auto increment column. Now, if you're not gonna what that is basically, that will allow us to i d every single record as an individual record. So the first column in our table would be an I D column. It would have an auto increment ing number. So statement one, then two, then three, etcetera. So, yes, we do want to have that. And again, I'll go into more detail about that later. Next thing, how many rows will I want? 100 Rose and I want to prompt, download and generate. That's going to generate 100 record for us is gonna go into my download folder. So that's it done so I can go back to PHP madman. I have my company database selected. I click on import. I need to choose the Fail, so choose the file. It's in my downloads folder. There it is. Double click on that. All of these default settings are all fine. Second, just click on Go, and there we are. The import has bean successfully finished. If I go over here on the left hand side, I can see I've got my company database. And in there I have some users click on users, and there we can see the 100 records. So that's it for this lesson. It's a nice, easy way to get some data into that database. So we get ready to actually query the database from the front end on, actually pull some data and display in the browser. So just as a quick recap, we can actually create a data biss within that database. We know that we can actually create a user table or any table on actually populate it with some data. Now, as I said earlier, I will be going back to pitch. People had mean, in a little bit more depth on will be manually creating a database tables and actually entering some data as well. I think at this stage, though, it's important to have that quick win, as I keep calling it so we can actually get to the front end, connect to the database, pull some records on, actually start to build a little interactive page. If you have any questions, please feel free to use the Q and A section, or you can send me a private message. Now this stage there could be a few hours creeping in, but if you go back and watch both videos again, just make sure that you're following every single step correctly and you should be good. 3. How to retrieve data from a database table: Okay, so we left the last lesson. We'd actually created our data. Best called Company. The creditor table on would actually populated that with some users by using generate data dot com Now that said, the reason for doing that is we just want to have that quick win who want to test everything, make sure we're on the right lines, that everything's working. And then we can progress in a little bit more detail throughout the rest of the course. So without remained, the next thing is to actually retrieve the data from the data bests by using the front end . So what I want you to do is go to Section one right click and creates a new fail, and we just call this quick win. A quick dash win on dot PHP is the extension on the end, and there's my quick win. Fail somebody open a PHP code block, so PHP. Now I want to start to connect to the database and then retrieve those records, so there's a few variables I need to declare first. Now the 1st 1 is the database host DB Host equals now. This is the location of the database itself. and it's actually on the local host. You can also put an I P address in there. So local host, the next thing we need to do is to provide the name off the actual database itself. So the data best name and the database was called company. The next thing will require is the user name and password to actually access the data bets . So we'll need a DP using him. And if you recall the user name Waas route on the database password Well, I was actually just blank. Now, these variables have actually credit. You can call these anything you wish, but it does make sense to actually using them actually makes sense. And to me, this doesn't make sense. Now the next. Any takrit is the data source. Name on. If you've been playing around with PDO, you might have actually seen that around R d s. And so the D s end the data source name is gonna be equal to the following Now we are using my sequel. So my sequel is the driver and the host. What is equal to the date of its host? So Devi underscore host. Then we use a semi colon and then we need the database name, which is equal to, well, db Nim. So db underscore them and then we can actually use a semi colon to finish that lame. That's R D S n taken care off. Next year I want to do is to actually create the database connection. So the database connection on again I'm using a variable name. I'm making up so deviant to score connection that's equal to a new P D o. That's the class. It's an inbuilt class, and we can actually passing that some parameters. We're gonna use three parameters here, the first being the D S. N. The data named Source, the second being the using him and the third being the password. So the 1st 1 is the D S N. The 2nd 1 is the using them, which we've got as d. B using him. And the third is the password that is DB password and finish with a semi kulla. So what that line is going to do is gonna create a new PDO connection. He's in these parameters here, and it's going to store it in DB connection. That's going to be an object that can then use The next thing I want to do is to actually creates a query. And this query is so I can pull the records from the database. So going to use available called query again. You can call it anything you wish on. My query is going to be a sequel. Command in here. Now. What I want to do is I want to select so in capital letters doesn't have to be capital. But I like to put mining capitals. So select all the records and we use that with a stars. The star means, like all from now where are we selecting them from? Well, we're slating them from the data best table, which is called users. Now, this one here is not actually doing anything yet. What am I? She dinners? I'm just storing this query Select star from users into this little variable here. What I then need to do is actually run that query on the way I do. This is I say devi underscore connection. So I'm using that object. We going to operate on the objects and we're going to run a query that's an inbuilt function or are method I should say. So we're gonna run a query on the perimeter. We're gonna pass is what I've just written above. It's the actual query. Now we're gonna get some results from that and the results I want to store in results. So results is equal to take our object. We're going to run a query on it. What query are we running? We're running this query here. So, like star from users. Now, what we're gonna do next is Russia going to close the database connection? So the data best connection we're going to say, equal to know. And it's a good habit to get into that practice of opening and closing your database connections. Now, we're going to assume that we did retrieve some records from the database, and now we want tracks the loop through those records. So if you notice if you're familiar with PHP, I haven't done any sort of error trapping at all at this stage. We're keeping things very, very simple. We will progress lead, run through the course and look at some sort of try catch block. But for the time being, let's just keep things in lace and simple. So I'm going to shoot. I do have a set of results here. So results on what I want to do next is to actually loop through those results. I'm going to use a for each loop. So for each something, I'm going to do something. Now, if you're familiar with my last course, we've been through that many, many times, and you should be familiar with it. Now. These run brackets I want to say, Well, I'm going to take the whole results set. And for each one, I want to story in result. And then in the curly braces, this is what I want to do for each one I want to echo. I'm going to take the result. And in square brackets and some tick marks, I'm going to choose the actual column in my data bets. So we've got first name surname, got dead of birth of the email on which one die what she want to show. Why does she want to show the first name now? Is it gonna be looping through these? I do also want to echo at a little break. So put in a little break here and a semi column now save That's a command or control s go over to my section one. There is the quick one. Quick win, brother. Click on there and there we see a full list of all the first names. If I wanted to retrieve the email, I'll change first name to email, save. Go back and refresh. Now Hopefully that work for you as well as I said, this is a quick win section. We're going through things in very basic detail here. We're just using coward. You might not understand that you might have made one or two errors with commas and semi colons, etcetera. But if you have go back to lesson one, watch the first video watch the second video. Make sure you use exactly the same names I have on this code should work. Now if you find the things just aren't working for you, I have actually attached this code into the resources section as a little text file, download the text file, copy the code and pest it in. If you're still having issues, then obviously there is the Q and A section on. You can also send me a private message. They're moving on the Section two We're going to go into a lot more detail in PHP, madman. We're going to be applauding some CS fee fails. We're going to be manually creating data best tables and actually populating records, making changes, etcetera. 4. How to crate a database table in phpMyAdmin: so welcome to Section two on in this section. I want to delve a little bit more deeply into PHP. My admin. In the previous section, we had our quick win. We created a database called Company, and then we used CSP import to actually create the table and popular it with 100 records. We then went to the front end. We credits, um, code, which would actually read the values from the database and actually present them in a browser. That was a quick win. We've tested everything we know. Things work, so now we can take a step back on going to a little bit more depth. Now the first thing I want to do is to just do a little bit more practice on credit those databases and actually manually creating a database table and manually inserting some records. So let's go ahead on. We'll create a new database, so click on the home icon here in the top left and then click on the databases tab. Now the database I'm going to create is a little school database. Let's imagine we've got some pupils and teachers in there, so let's have a my school database doesn't matter what you call it and click on. Create That database has being created. We can see here on the left hand side, on in here as well. My school, We click on that. So the first thing I want to do is to create a new table. And I'm gonna call this one pupils. I'm gonna put four columns in there and play con Go. So there's my table is being created, but we've got no information in here yet. We've got these four columns on. The first you need to do is to have an i d. Now the i d is my way of uniquely identifying this particular record. It's going to be an interview field on over On the right hand side, a underscore. I stands for auto increment. This is going to be a unit number that is assigned to each record, starting with 123 etcetera. No, my sequel. What? She take care of that for us on? The next thing I want to do is just like this as the primary key and I'll discuss that later. The next field is going to be first name. That is going to be var char available character on the limits of, say, 50. The next one is going to be surname. We're gonna have the same thing. A var char with limit of 50 variable character on the last one is dead of birth, which will be a debt field. That's those selected now. You can see there's lots of different options here. We won't be going through them all, but I will cover some of the main ones in future lessons. So click on Save That's Murray Table created. I can see on the left hand side here under the my school data. Beth. Obviously, there are no records in there. If I click on the pupils table, I can see we've gone empty. Result set. But what I will draw your attention to is that my sequel Cold here, and it's quite a good habit to get into to actually read this and it will help you in future lessons. You can see it says select star from pupils limit 0 30 So if I had 30 records arm or what it would do, it was like all of the records and display them in this based down here. Now we have come across something similar. If you flick back to our quick wind fail, we have a query, which was select star from users and that's liked it. Every record from the user's table so quickly We're building up a way to actually write Cem sequel code and then use it in our PHP fires later on. The next step is taxi inserts from records, and we can see here we've got the insert time, so click on there and we've got this insert form now the I. D field. We have said that is an auto incriminating field, so I do not want to enter any value in there. The first name. Let's have Peter, Sir, Nameless have Smith on Let's Like to date of birth, but just trains the year to say 2000 and nine and click on Go so we can see that being successful, we can see the sequel code here we're inserting into the pupils table. We've got these four columns and the values of no Peter Smith and the date of birth. Obviously, the reason that was no, we left that blank, but if we click on Boro's, we can see that that value has actually bean filled in for us. There we go. I d off one. Let's go ahead and insert a second record So again, leaving the I d. Blank the first name. Let's have Sam and Sam Jones and Sam Jones another date of birth here. That's like the debts and change the year to 2000 and seven and click on Go and again that has bean successful. Now, as a developer, you would not want your client to be accessing this backhand here so they would not be inserting their records from this system. They would actually have in late for intend and night form filling the data press admit that would be sent to a PHP script which would then populate that data. Beth now for client isn't using a database currently, then they probably have some sort of list system, or maybe even a spreadsheet on. That's what I want to look at very quickly. Now I'm going to use a Google spreadsheet. You can use Excel or numbers or any of the very you have got back quickly. Can new click on Google sheets and credit spread she here. So I'm gonna give my spread she a title of pupils on, then fill in some headers here Now we had four columns, which were I. D. We had the first name. We have the surname and we have the date of birth. I know I've got two records in my database currently. So the idea I could put in here next would be three and the one after could before. But if somebody's was to come along and inserts from records in the meantime now actually be duplicating this and then end it with an error. So what? I wanted to leave that blank on. Let the system take care of those numbers for me on in the first name. Let's have gems. Let's up James Green. And he has a date of birth off 2007 0201 and we'll put one more in. Let's have Jen this of Jane Smith 2006 10 10. Now I could carry on entering some more records, and if you want to do that, you can. But I think you get the idea. So the next thing is to actually export this so file download as and CS V or comma separated values. So that's downloaded. I'm gonna go back to my data. Bess, make sure I'm in the pupils table and I'm going to import that fail. Going to choose the Fail There it is peoples they can see straightaway. It's recognized that as a CS V file on the format has changed to see SV. And just above that, we've got this partial important. This is important. It says. Number of rows to skip now say, I want to skip that first row. I do not want this first row to end up as a record in my database table, so I'm going to skip one row. The defaults can be left as they are, and I can click on Go. And that import has bean successful. So factory Cohen, browse. I should know have four records in my table, and indeed I do. NASA said. These air no ideal methods for actually inserting records. We want a front end. A nice form user comes along, enters the data and submits on. We'll be taken care of that in future lessons. But just as a quick recap, we have created a new data. Beth manually created a table, and I looked at two ways to actually insert some records manually through this form here on the insert tab and then by importing a CS fee, which wasn't export from a spreadsheet. 5. TASK 1: Creating the Teacher Table: Okay, so it's time for a quick recap on. We're going to do this through a little task. Now, what I want you to do is to assume that being approached by the school, who want you to add a new table to their database and this table is going to be for the teacher data. Now, if you go to the resources area, I've actually uploaded a C S V file off the teachers and it we've got the first name, surname, date of birth, email on the start debt. And, of course, the I D field will be auto incremental. So what you'll need to do is go to the database the my school database. You need to create a new table, cold teachers, and then upload that See SV file. Now this tax should be fairly straightforward, and I'll go through solution in the next video 6. TASK 1: Creating the Teacher Table - SOLUTION: So how did you get on with that challenge? Let's look at the solution on how I would actually proceed with this. Now, the first thing to do is to download that CSP on the money to create the table. And I'm gonna call this one teachers. Now, I know there are five columns in this one on Click on Go. So here my five columns down the 1st 1 we know his i d and that is auto incremental. So take that and it will be our primary index. Next, When we had waas first name of our char off, say, 50 surname again. Variable character of 50. We also had the date of birth email. Andi, start debt. So it actually got six columns here. So it's good that I've made that error because you can see what I need to do to fix that. So the next one is dead of birth. You put that in. So we've got date of birth. We know that is a debt. And we had the email address. Let's use var char on again. Let's just put 100 in there. I shouldn't need much more than that. I want to add one. Mawr columns appear of God. One column Let's get Congo on the next one is the start debt, which again will be a debt and click on Save. There we go. There's the teachers table has being created on. Obviously, there's no data in there. The next thing I want to do is to import that. See SV. So click on imports. Choose the C S V file. Teachers see us further. It is. We want to skip that first rose. That's an important step. Leave these as they are on Click on Go, and that's being successful. So weekly column that brows. And there we have the data for the teachers. So I'm hoping you had similar success without if you didn't obviously use the Q and A section or indeed, send me a private message, remember, I'm here to help. Now, if you're feeling brave and you want to extend yourself, the next thing you could do is go back to what we did in the quick win. Have a look at this and see if you can actually print out the first name surname, email address, etcetera off each user on Perhaps even put it into a little table and get old formatted. Now that is an extension task. If you can't do that, don't worry, we will be covering that in later lessons. 7. How to update records in phpMyAdmin: in this lesson. I just want to look a little bit more at PHP. May I mean, before we go into more PHP code on? What I'd like to do is just look at a few more of the options within PHP. My admin. So I'm going to click on the home button here. They want to do that. I get different tabs along the top. The 1st 1 databases we've actually used already. This is where we can create a database. We could enter some sequel in here. We can check on the status. We can look at users now that she uses one here, we can see the root user on. We can see where the password is not set. Now. Obviously, if these was a live environment for a client, we would not want that. We would want some security options in there. If I click on edit privileges and if I scroll down, we see the password section. We can change the password on. We can actually generate a password. Now, if you choose to actually generate password and use it, make sure you keep it safe. You would click on go to change the password I am not going to do that. This is only an online course. There's nothing important in here on, just in case. I do forget the password. I don't want to lose everything. I can actually export my databases. I can imports, change settings, etcetera. Most of the tabs over here are for advanced users, so we're not gonna actually go into those. But the next thing is, we'll click on my school and we'll look at the tabs on the top for the actual database itself. Now we've got these structure which is showing the tables we can see for each table. We can browse the structure, weaken, surge, insert empty, etcetera. Now, by emptying, we are removing all records, and that's different from drop. Drop means to icy delete the pupils table again. We've got some sequel that we can answer Weaken, search query. We can export the database we can import, and we've got different operations and privileges as well. If I go into the privileges, you can see that the root user has all privileges on this database. Now that's not something we probably want. If this was a life environment, for example, if we just had a system where we were reading records from the data vests, then we would not want to give all privileges to that particular user, so it actually tightened the security on the database system. If I go into the tables, we can actually browse the data. We've seen that already. We can look at the structure off the tables. Sequel again is just entering some secret commands weaken search. We can insert records. We've seen that we can actually export the data from the table. That's quite handy on we can import as well. We've used that again on the operations. We can actually rename the table. We can copy it across to a different table. We can copy this torture only, or we can copy the structure on the data. That's actually quite handy. If here messing around with something, you're making some changes and just want to keep a backup. Let's go back to the browse section and have a look at some of these records with the records. We have the option to edits copy or delete. Now, with the edits. Here there are two as we Canadia record. The way I prefer is just to double clicking the cell, and I can change the name here to play a different spelling and save that so that in lane method is my preferred method for editing records. The other way we can click on at it, and then we get a little form we can fill in now because this is a beginner's course. I don't want to go too much in depth on PHP in my admin. It is a useful tool, but it's not a very complex to, Melissa said. I could run a full course just on PHP. My admin. Now what I want to do next is to use PHP to create read update on delete records in a database. 8. How to make a PDO connection to the a MySQL database: in this section of the course. We want to look a little bit more at PHP and how we control records in the database. In particular, I want to look at how we create We read or retrieve Howie update on how we delete records. Now, before we delve into those four areas, the first thing we need to do is actually connect to the database. And if you recall from section one, we did that with the quick win. Now I want to look at that in a little bit more detail. So let's just pop back and have a look at the quick win. We can see the connection part is this bit here? So let me go ahead and create a new section over here. I'm going to right click credit you folder call this section three. And in section three, I'm gonna create a second folder on this second folder. I'm gonna call W W W. And the reason I'm doing that, I'm trying to replicate what would happen in a life environment. Now if you do have your own server, if you're on some like see panel etcetera, you'll see that when you are given access, you get access to a root folder, and in that folder you will have probably a www folder or a public HTML folder. And that's the one that is front facing. Now. What we shouldn't do is store any database credentials in that front facing folder. They should be in a root folder so the public can get access to them now, believe it or not, even though this is a PHP file need, it should be fairly secure. And we shouldn't see this, For example, a user name and password. Sometimes the server can fail. I've had it happen to me once, and the user name and password can actually be viewed online, so we don't want that to happen. So in section three, I'm gonna imagine that this is a folder that the public can't actually see. They can't get to that. I'm gonna credit when you file in there. And this is where I'm going to store my data base configuration. I'm gonna call it D be underscore. Come, fig. No, PHP. Does that file created? So go back to quick win on. I'll just copy this section here. I would just pop that in so if you recall. We studied with our database host and that was local host. We're on the same server and said we could use an I P address in there. We've got the database name, which currently is company will change that to my school, cause that's the last one we will working on the database. User name was roots and the password waas blank. Now we would never leave a password Blank, Indeed. I probably even used the user name root for in PHP. Madman. I'm going to go to the my school database. What I want to do is to create a user specifically for this data vis. I'm gonna go to privileges. I can see the route to user here. I'm gonna add a new user, give the user name. I'll just call it Patrick. The host will be local host on the password. Would like factually generate a password here, so I'll just copy that. And before I forget it, I'll go across to here on the password or just paste in here. Change that user name to Patrick. Let's go back to hear their things set upon here for it. Database with the same name no grant. All privileges, no grant. All privileges on the database. My school? No, I don't want to grant all privileges. What I want to do in terms of the data is the user can select the data they can insert update on delete that I don't want to affect any of the structure of the data bits. In other words, can they add new fields? No. Or indeed, administer the database. First click on Go. Okay, so now we've added that new user. Let's go back to PHP. My admin so that I have created my db conflict fail. I know I can actually connect to the database. What I want to do next is actually show some records from the database. Just test things a little bit. So it might. Www folder. I'm going to create a new fail. I'm gonna call it create dot PHP. This is where I'm going to insert some new records into the database. Says my new file. Now, before I creates a new records, I just want to test that everything is working. So PHP on what I'm going to do, I'm gonna go back to my quick win. I'm going to grab this query for Lloyd down there with Copy that, and we'll just paste it in here. Now, remember, we set a query. We put unequal select Star from users that will select all the records from the user's table now because we're using the my school table so that my school database rather we have two tables in there. The pupils and the teachers from his pull all records from the teachers. So that has set the query on Then what want to do. We want to run that query, and we're storing those query results into this verbal here results. So we're grabbing the connection to the data vests were running a query. The query were running. Is this one here? Then we're setting the database connection to notes were closing the database connection, and then we're going to loop through and present some results. And the results were going to present is the email address off each of those staff members . Now, this actually won't work. So if I go across and have a look, we've got sexual warm and Section three manage your quick refresh click in section three in section three. We've got that d be conflict fail and if you recall, that will be hidden that is not front facing. But the WD legally folder is our front facing folder and then there we've got create dot PHP And if I click on that, we get undefined variable db connection. That's because that file does not know currently about the connection details. So we need to tell it where to grab the information. So in here, we need to require once on what do we need to require while we need to require this db conflict? Fail now the Devi conflict file is one folder back. So what we're going to do is we're going to say go back one folder and then grab the db conficker dot PHP file. We'll save that going to hear and refresh. And there we have the list off email addresses. So just as a quick recap, we have created a new section. We've credited www folder, which is our public facing folder on Within that we've credit little file cold, create dot PHP Now, currently, what we've put in there is just a quick query just to pull some records to show that things are working so We're going to remove that from there in the next lesson. And we also created a DB conflict Fail, which stores all the details of our lugging for our database. It actually then connects to that database. So in the next lesson, we're going to look at how we can actually insert records. 9. How to use a try catch block: in this lesson. I just like to talk a little bit more about that d be conflict fail I would have done here of Graham two db config file and I've also created another one called Try Catch on. All that's in there is just that require ones db conflict. If I go to my browser and refresh, you can see I get a blank screen, which is what I want. Now what I want to show you is, Well, what if there's an error in this conflict file or something happens on the server? And to show that I'm just going to change the name of the database here to my school to doesn't matter what we call it, save that go back and refresh on we get a fatal error. No, this era is a real concern, because if he noticed down here, what we have is the user name and the password for the database on. We do not want to present that to the user. So what do we do? Well, we do something called a try catch block or it's one method we can employ anyway. So my db conflict file, where I might seem making the connection, which is here. I'm going to say Try something. And inside here is where I try that connection. Grab that and put that in there. Otherwise, we're going to catch the error. I am here now. The error we're going to catch is what we call an exception. So exception. And we know gonna give it some sort of variable name on. Usually we just use a or you can put the word exception in. No. In here is where we present a message So we could just echo something like they waas a failure. Obviously, that is gonna be a little bit better than receiving what we did receive here. So now let's give that a try. Let's save that. Go back and will refresh. And there you go. We've gone error now in here because I've used that. I can actually pull a little message as well. I'm just put in this and will concur. Captain it on e. They're going to operate on that and say, Get message. No. If I said that, go back and refresh. So there was a failure and it's saying unknown database my school too. So that is a little bit better than what we saw last time. Obviously, on the front end, I wouldn't even show this. I would just give the user a little message. Now, that's all well and good. We presented the user with a message, but what about as how do we know that that failure has occurred? Now we'll see. We can check the logs in the back end, but if we are dealing with a client site, we need toe have instant access to that information. So what you could do is we could set up a little email system here. To actually notify is off that error? No. I thought this was quite a useful tool for you to know about these. Try catch blocks. They are something you can use in other areas of your code. And I think it's something you might want to investigate in a little bit more depth 10. How to create a database record using PHP: in this lesson. I want to look at how he actually inserts a record into the database. Now, we've already established a connection. In the last lesson, we created DB config we've created our create file on. We know we can actually connect to the database and actually retrieve records. So now I want to do is actually write the query that will actually insert a record. So let me get rid of all of this. We will leave the require once cause we do need a reference to our DB conflict fail. But now I want to do is to actually insert a record. All right, the query to actually do that. So we're going to have a query which is going to be equal to something on What's it gonna be equal to? Well, if you're not sure what you can do is head over to PHP my admin on. Actually, click on insert. Now, make sure you're in the teacher's table. We go into insert and it brings up that little insert form. Now, if you recall the i d Field will either as blank or no. The first name. Let's just popping here. Peter again, Let's have Peter Smith and instead of birth is 1987. Let's have 0506 His email that should put Peter at my school don't come early. Start date. Let's have 2000 and nine and 10 10 Weekly can go. That will insert the record. We can see one row inserted, but underneath. We can actually see the my sequel code now under here. We can also see the code codes. Let me just zoom in, scroll down a bit. There we see what's actually happening. It says Insert into its identifying the database and then the actual table. And then it's identifying the fields we want to insert into. Then it says which values air Going into those fields had any brackets Again, we've actually got those values. Zoom back out, head over to come anywhere. I let try and write that query. So it was insert into and we're inserting into the teachers table and then we identify the fields. Well, the first field is I d. Then we have the first name. Remember that camel kiss? Then we have the surname. We have the date of birth. The email. Hundley, start debt. I'm just going to press enter a few times here, return on in here. I'm going to put the values. So we're inserting into teachers, those of the fields, and then we're into starting the following values. If you recall the I d Feel leave as no than the first name. Well, what we put in the first name. Let's have gems. And for his surname. Well, let's have Jones. He's dead birth. Well, let's for the date of birth. Have 1980 eights. Let's have the 11th of the 11th. His email address. Let's just have James at my school. Don't come, honey. Start. Dallas put start dates off 2000 and nine, and he started on the ninth off the ninth. OK, so that's our query written. Now we know we have to run that query on the way we do that is we have to reference the database object, So database connection, and then that doubles connection. What we need to do is actually execute that query command. So exact is the fact we're gonna use on what are we running? We're running this, Queary, Let me column. And the last thing to do is actually close that database connection. So we'll put that equal to know. Okay, we'll save that. We'll go across to our page and we'll just run that create query. Now we get a blank screen so we don't know by looking at a screen. If we've been successful or if we have a failure, let's go into PHP. My admin click on the teachers table. I will just sort by I d in reverse order here. On that we can see James Jones and that record has Bean inserted. So wouldn't it be nice? Therefore, if instead of getting this blank screen, we could actually present a little message and we can do that quite easily on the way we do it is using a simple, if statement. So if something is true, we're going to do something. So in here, well, what needs to be true? Well, this needs to return a true value. So if I could that from there, Onda pissed it in. We know that if that runs successfully, we will get a true value. So in here, what we want to do if it is successful, well, we just want to enter a little success message or present a little success message. However, if it doesn't succeed so else, we need to do something else. Let's put in here under the message. Did just says, Failed. We'll save that. Good to hear will refresh on the received success go across to here will refresh that. And there we see. We've got James Jones again. No cities, no ideal to have two records in there, but obviously weaken. Delete that later on or delete from here. So just to refresh, we have created our query. We are inserting into the teachers table. We identify the fields. We then say which values were actually entering, remembering the I d. Feel needs to be set to know because it's an auto incriminating field. We're executing that query, and we're actually doing a little test that, if it is successful, present a little message. If it fails, present a little failed message, and then finally, we are closing our database connection. Now, if you have any issues, make sure you watch the video again. Check your syntax. The errors usually stemmed from meeting semi colons. Usually the field names are incorrect. Maybe didn't put a capital n or maybe use something else so just be careful with that syntax. If you do have any questions, use the Q and A section. Or indeed, send me a private message. I'm always happy to help. 11. How to retrieve database records using PHP: in this lesson. I want to look at how we actually retrieve or read records from the database. Now we know we've established a connection to the database. We know it works, we can create records. And we've seen previously that we can actually retrieve records from the teachers table. Let's extend on that a little bit on. The first thing to do is to go across to our folder on We're going to actually create a new fail on. We're gonna call that read dot p h p. Now this read file, we're going to start with the opening PHP tag and no need to reference are db Conflict fail . And if you recall how we do that we require once on what are we requiring? Well, re require the db conflict fell, which is one folder back, and it's devi underscore config dot PHP. Okay, now we've done that. We can set up our query so the query string is gonna be as follows. So what are we going to do? We're going to select star or asteroids, so select all from teachers that will slight all the fields from the teachers and then we need to execute that on? How do we do that? Well, we need to reference the DB connection, first of all, so this is the database object on within that we're going to run a query. And what query are we going to run? We're going to run the one above. That's our query string there. And if you put in brackets in my actually help So I run that query string now that is going to return a result set which I'm going to store in results that's going to be equal to that . So now that we have those results, what we need to do is loop through and actually present them on. How do we do that? Well, I like to use a for each loop. So for each something, I'm going to do something well, for each What? Well, for each of the results, we're going to take each one of those, and we're going to have it as result. So we might have many records in here in my have one, but we're going to take each one of those and story in result, and then we're going to echo at that result So echo. So I'm going to take the results. Which part are which feel that we're going to present, Let's say the first name. And then I'm going to pop a break in between each of the results of public break in here close that on. The last thing to do is totally close that database connection. We're gonna put that equal to know with a semi column. So now we can save that. Go to our browser. We'll refresh that section. There's the read fail Click on that, and those are the first name is presented. Now we can change this first name to surname. Or indeed, we can actually can captain it it on here. So let's come, Captain it on here, let's put on a brake First of not break a space on banking Catholic on results on DNA result we want is the surname Save that back to the browser and refresh. And then we have the first name and the surname presented. Now what I want to do is to look at this select statement in a little bit more detail because we got here Select Star. I was like the little asterisk that means select all now it doesn't means. Like all records, what it means is select all fields. Now we're only using the first name, and surname feels so it doesn't really make sense to retrieve all fields. So therefore, let's just retrieve the first name comma, and the surname will save that goodly browser refresh. And as you can see, we still get the same result. Obviously, I can add more fields in here or indeed just select everything. Now, one thing I'd like to extend this with is, Well, what if there are no feels to actually retrieve? I would be presented with a blank screen so it would be nice taxi present, a little message, And that's a bit like what we did with create here we actually tested. If the execution of that query waas true and if it was re echoed success else, we echoed, failed. So what I'd like you to do is to try something similar here. If we do retrieve a set of results that obviously present results else, I want you to say something like no records retrieved. So let's pause the video here, have a go at that, and then once you've done come back and I'll show you how, actually, solve that little task. Okay, so welcome back on. This is how I would actually solve this. So we've got this result set here. Now, what we want to test is if it did actually return any results. So just appear I'm going to say if something is true, I'm going to do something. One of my testing is true. Well, I've got tested. This has returned. A result set. So cut that pasted in here. I just get rid of that little semi cold on there. And what do I want to do if we do get a set of results? Well, I want to loop through this. So cut that from there, and I'll paste it in there on just Elaine these brackets and things. So I'll just in dent that code. There we go. Just cleaning things up a little bit. So this bracket here, this curly breast belongs to this one. So I know that if it is true, this is what I'm doing. Els. It's something else. What else? We're doing that well. If it's not returned to see the results, I want to echo no results to display or something like that or no results retrieved. There we go. So if I just get rid of these little lines here, just tidy up a little bit, Save that on. Do a quick refresh on that should still work, which it does. But let's say I wanted to retrieve something that doesn't exist. Let's say there was an end debt. So when did the teacher leave? For example, if I say that, I know they're not gonna be any results. So what I should be presented with is no results to display. If I refresh. There we go. We've got no results to display. Okay, Time to recap. So, in this lesson, what we did is we retrieved some records from the database. We looked a difference between using select all which was the asterisk on. I see selecting individual fields we couldn't invade, released like the i d, the surname etcetera on. Then we looped through those results and presented them on. Finally, we did a little test to see if the war a set of results. And if that weren't to display a little error message on this 1000 lane here is just to close that database connection. So I just save that and I'll see you in the next lesson. 12. How to update database records using PHP: in this lesson. I want to look at how we actually update a record. Now, this is going to be a little bit of a contrived lesson on. Do you understand why when we come to actually using a hasty male form and then updating the database that way, now we know we can read records. We know we can create a connection. So how do we actually update a record? Well, the first thing let's create a new file so it might double you deleted the folder and going to credit. You fail. I'm going to call it update dot PHP. There's the file and opening that code block PHP code block. And we know we have to require once and whatever acquiring we're requiring that d be. Come Fick, remember to go back one folder D b config dot PHP Now let's say I don't know how to update a record or for gotten Haven't used it for a time. What you can do is go into PHP madman on Just simply update one of the records. Let's choose this one. I d 82 will changes name from Smith to Jones Press. Enter on. We see here we have the sequel command that does that. It's something in the my school teachers table. By setting the field surname equal to Jones, where the I d is equal to 82. That seems fairly straightforward. Let's have a go at that. So in here we're going to structure our query, and we're gonna put it equal to the following. So the first thing we had to do was to update the teachers table. I'm gonna put this on new lanes up here. I'm going to say it's update the teachers table. I want you to set the following. I want to set the surname I wasat the surname. Equal to what? Well, that's it. The surname equal to green. So in tick marks will put green. If you recall, we have to set under the command here. We have to say where the I d is equal to a number. That's the unique I. D number, and that's equal to 82 in this case so we can close that off. Okay, so that's going to that's our query structured. But now we need to execute that query. So we need to reference the database connection going to operate on that on. We're going to execute the above. So going to execute that we're going to execute our query. Let me say that on. I'll go over to the browser. Let's refresh here. So we get that update there it is on refresh, and we get a blank screen. It's that either means it's worked or it might have failed. So let's go to pitch P madman. We'll refresh here. We should get that. It's changed a green, and indeed it has changed to Green. Let's worked now in previous lessons. What we've done. We've run a little test because it's not very nice to receive this blank screen. I don't know if anything's worked. So once again, I can do a little if statement here. Now, if you wish to pause the video, pause the video, have a go yourself, see if you can do that. Otherwise I'm gonna go ahead and show you how it's done. So just here we're going to say, if something is true, we're going to do something. What are we gonna test? Well, we're testing this here. Let me put that from their property in those brackets and get rid of that semi Colon. Now, if that's return true, we're going to echo just popping little success message. Els. I will say that the update failed, so Echo Update failed just on the and here. Let's just close that data. Best connection. So put that equal to know we'll save that. Um, let's set the surname to something else. Let's say Brown save go back to here and will refresh that. We got the success message. Checking here will refresh and we can see it's actually changed, too, bro. That's all worked, so hopefully found that useful is fairly straightforward, but let's just extend it a little step. What if we want to update multiple fields in one Go? Let's say we're setting the first name. I'm gonna put that equal to Sam on. We also want to update the surname we'll need to do is place a comma between those so the first name is gonna be cool to Sam and the surname Let's play back to Jones. We'll save that will refresh. We've got the success message. Let's check will refresh, and then we go. We've got Sam Jones Now. The last thing we need to do is to learn how to delete a record, and we'll do that in the next lesson. 13. How to delete a database record using PHP: The final lesson in this section is where we delete a record from the table. Now, what I'm gonna do to make things a little easier here, I'm just going to make a copy of this update file here. So what I can actually do I can actually right click on this and actually clone it. So it called Update alcoholic, delete and click on. OK, so there we have the file created. I'll click to open that fail. And again, let's assume I don't know how to delete a record. As you know, we can go across to PHP my admin on. We can actually use that to help us form at the sequel command. So what I can do is actually click on delete on any of these records. I'll pick record number one here, click on delete. Do you really want to execute delete from my sequel where that is? Yes, I do. But before I do that, we can see this is the sequel command here. It's delete from the teachers table where the I D is equal to one. So it's actually quite straightforward, actually, can't OK and that record has now gone. Let's transfer that cross to our little script here. So in the delete file, we don't want to update. We want to delete from teachers on. All we need is the where. So let's put where the i d. Let's just have the I d equal to two and again we'll leave that little Messi's there. So if that is gonna be successful, will get little success message. Otherwise, we'll get that the deletion failed. Let me say that. Go back to here on. We'll go back into our section and refresh. There's a delete firewood political Matt on. We get a success message and we can check that has actually worked by Refreshing here on record. Number two has gone so over the series of four lessons there. We have created records. We have read or retrieved records updated and deleted. Now that acronym crowd, you may have come across before C R u D. Now that we know those four basic things, we can actually use that to implement some more complex scripts on before we do that, we're going to start integrating hates T mail on the PHP in a little bit more detail on will take care of that in the next section? No, I am a great believer in practicing what you preach or, in this case, pricing what you learn. I think it's really important to build that solid foundation of those skills you've just learnt. And you have learned quite a lot. You've learned to create, to read, update and delete credit. Remember that. So what about dear? Set your little task and the task will come up in the video. Next, have a girl, see how you get on and, as always, I will post a full video solution. 14. TASK 2: The Book Store: Hi there, and welcome to your first extended task. Now this one is longer than the previous task and involves a little bookstore on. What's happened is the client has provided you with a C S V Fail, which is a list off books. It includes the title, the offer, the genre, the height of the book and the publisher on what the claim wants you to do is to create a database so they can easily create records. Retrieve records update Onda delete. Now, I have provided a C S V fell for you. So if he checking Resources folder, you'll see one called books dot C s feet. So what you need to do is to create a new data, Bess. Within that, you're going to create a table cold books on. Obviously, you'll need to populate the correct fields on the correct data tapes. Now, one thing you might notice is that when the client provided you with the CFC file, they didn't know include an I. D number. They didn't need to. So what you need to do is when you create the database is you need to think about how you're gonna get that I D number in there. Now, I don't want you to import it into a spreadsheet and actually put it in that way. I want you to import the data. I then want you to go to the structure of the table and then actually amend it by putting the I d. Field in on. You should get something like this. They want you to go to the PHP side of things on. You're going to create a new folder, Cold books. Within that, you're gonna create a publicly accessible folder. Www. Our public HTML. Wherever you wish on, obviously your DB conflict fail is gonna be outside of that public folder within the www folder. You're gonna create those four files like we created last time. Want to create one to read or retrieve the records, want to update and want to delete. Now, obviously, at this stage, we don't know any hitched email to actually embed in there, So we're gonna keep things fairly simple. We're just imagining the client is gonna visit that page and it's just going to update those records. So what I want you to do is when you do your creation fail, you're just going to create a single book entry, and you can populate that with whatever details you wish for the read. I want you to retrieve all the records and show all of the fields. In terms of the update, you will notice that when you go into the database, some of the publisher fields are missing. I want you to do is to update record 5 11 and change the publisher to Penguin. And lastly, I want you to delete on I want you to delete record number 512 The other thing I wanted to do is to consider those little success messages that the user would actually see when they run those scripts. So it's important to see either a success message or a failure message. Now hopefully have. From with this, you might hit one or two issues. If you do, then obviously review some of the previous videos. It's a really good idea to get a very solid foundation before we move on to the next step. If you get really stuck, then we always have the Q and A section, and it's nice to share ideas with students. And if you're still stuck, then obviously send me a private message. Remember, I'm here to help 15. TASK 2: The Book Store - SOLUTION: So how did you get on with that challenge? I hope it wasn't too difficult than might have been one or two steps you weren't sure of. But that's a good thing. It's always good to try and explore and to look things and try and figure things out for yourself. I found what I'm doing face to face teaching that if I tell my students everything, they don't retain that knowledge. So it's a good idea to explore on, try things now. What I want to do here is I'm going to show you one of two ways to actually deal with the data to get it into the data, Beth. Then I'll tree the PHP files actually wrote. The first thing is to go through to pitch P madman on. We need to create a new database. I'm gonna call this one my bookstore and create that's created the my bookstore database on Within that I need to create a table and regional college. One books I would put six columns in Now you should have realized when I said that there's only five columns in the data here on the one is missing is the I. D field that auto incremental field. Now there are a few ways we can deal with this. We could actually create a five column table and click on Go or do I'm doing here? We do a six column table. The first is the I. D Field. We have the title of the book. We had the author of the book, then the genre at the Height and the publisher. The idea is an interview field, which is auto incremental ing, and it's our primary index. Vouch are for the title that's put in there. 50. The author again, sampling 50 on. Same for the genre. The Height will be an integer and the publisher for Char again at 50. Let's go ahead and save that. That's creating that database table, and we click on books. Some. We can see that everything's fine, but there's nothing in there yet. We need to import the data, so click on import. Choose the Fail. There it is. Bookstop, see SV scroll down Now, hopefully remembered about this partial import. We want to ignore that first header row. It is C s V format. Now, at this point, you probably went ahead and click tone go, at which point you would receive an error and let me show you that there's the error invalid column count on. The reason for that is there are five columns in my data, but in the database itself, I created six. Now, if you credit five and you still tried to import it, then everything should be fine. The only thing would be you haven't got that I d field, so this is one way I can deal with it. I can click on import, choose the file, choose books, scroll down again, ignore that first column and down here it says, column names. I want to identify those names that are being imported. Obviously, the I D isn't The ones are our title off the genre, height and publisher. So now when I click on Go that will import that data and there we go. We can browse and we can see all the data is there. So I'm gonna go ahead and create a second table. I call it books, too, and I put five columns in, just as we have in the data. First column was title. 2nd 1 was author third WAAS Genre. We had the height and we have the publisher for this Will have Archer will set that at 50. Author will be far charge under live Arch are the height will leave his interred. Your watch are again I'm there And 50. We'll have 50 on the bottom. That shouldn't be 50. But that voucher there should be 50. There we go and click on Save and that will create our second books table. They're clicking on that books. I'm going to import my data into their This is just gonna be a very voice straightforward import. And we just want to ignore that first row and import, and that should be fine. And there we go. We've got the data in there now. Everything is when eyebrows that you can see the I. D field is missing. I've got the title first. So if I go to the structure of that table, what I can do down here, I can add one column. I can add one column either at the end of the table, the beginning of the table or after a specific actual Rome column, brother. So I want to add this at the beginning, off the table So I click on this one and click on ago, and I want to add the I D Field, which is an integer, which is auto incremental ing. And it's the primary key not actually can save that will actually populate that. I d field for me with all the values. It's when I click Ambrose. There we go. We've got the A T field in there, so that takes care of the data import. The next thing I asked you to do was to create four files for create, read, update and delete, so I'm gonna hadn't done that. So within this man folder here, he's close. A few of these. I have credited folder for books and in that folder I've got my www on in that I've got my four files. My db Conflict Fail is outside that public folder. Let me just check the DB. Come pick first and the D. V. Come fig. We didn't need to change the database name to my bookstore or whatever you called it. Your database user name and password. If you chose new values, obviously had put those in the data source name on the connection. They stay the same. So everything is fine and dandy there. In terms of creation, we had to require the DB conflict file our query we had to insert into books and then put the fields in. There is weak I d title author genre heightened publisher on the values Orsino into i D. But then we've got my book, Patrick Horror 235 and Penguin leaving in the messages on running that that would actually insert that into the table. Next, we want to read something again. The difference here is select star. So it's like all of the fields from the books table. We got a little check here, So if we do get some results, we're going to loop through and we're going to echo it here. I'm just echoing the title and the genre. If you wanted to echo Atmore, then you can concoct in it on the end of that. Now that isn't going to look very pretty. If I go back here, let's just go back and see if we can pick things up. So we've got create, read, let's become read. They can see it is quite difficult, actually read that. Normally we put those into some form of table that involves hate email on that's coming in the next section. So that takes care of the read. We then had the updates so similar again, we've got the query. We're updating the books on here. I'm actually updating the title on the genre. I think ASIO update something else the publisher and change it to Penguin. I think it waas. I've done that on my D number 11 it. So again, fairly straightforward. The last one is the delete on here. I'm deleting from books where the idea is 102 on You can delete from whichever idea I said to delete from hopefully get the idea. The big challenge here, really was that importing of the data into that database. Once you get that, sorted it straight forward from there. Now, if you still have questions, feel free to hit that Q and a section or again, send me a private message on. I'll be happy to help 16. How to integrate PHP and HTML: Welcome to the next section of the course where we start to integrate hates team l on PHP in order to former our output to the user. Now we've seen where we can get data from the database, and we can present it to the user when we actually read the records. But when we actually look at that such, for example, here we can read the four month thing isn't all that nice. Normally would have the insulin form off table. The tabulated data would be much easier to read. So that's what we're going to attempt in this lesson here. So by putting the data into a table, we can see things a little bit clearer so we can do that. And then we can actually a place, um, stay Ling to end it with something like this. So how do we do that? Well, let's have a look now. The first thing I want to do is to credit and you file in here on. I'm going to create file and only call it list books. Don't pee Head P. Now, what I like to do is just start with plan. Hitched him. L I'm going to design the table. I'm gonna put some study data in there just to see the way things lay, and then I'll actually bring in some of the PHP. So let's go ahead on actually set up a basic HTML page Now, A little hint here. I don't know if you're aware off limits, but it is preinstalled in code anywhere. And when you look at the basic structure overhasty male page, you will see the HTML tag. You'll see the head tags, the body tags, etcetera. I don't want to start typing over in all we need to do in em. It is type in a single exclamation mark and then press the tab key on that sets it for us. You can see were highlighted in here in the documents and my title is a list of books. Now what I can do here in the body, this is where I'm going to set up my table. So it only to do intimate is take the word table and press enter. I've set up my table there. Let me break that apart Now with in my table, I'm going to have a table head section within that I'll have my first table Row on within that I'll have my tea hedges. I'm going to have six of those. If I press tab there, have credit my table head. Now the first table header is the i. D Field. Still type I D. In here. The next one was the title of the book. We then had the author. Then we had the genre. We then had the height of the book. Come see the publisher. If I save that, I'll go across to my browser. Let's just go back and refresh here. There's list books are click on that and that is the start off my table. Let's go back now. After this table head, I'm going to have my table body so t body on within the body. I will have table rose and will have table cells. I'm only gonna do one will have a table row and some table cells. The table sales are going to have six of those. So that is the former for Emmett for writing that and a press the tab key on there that is done for me. So my first TD, that's gonna be the idea. The book. So I have one in there. The next TD would be the title, etcetera, etcetera. Now, this is where the live data is going to come in. Let me just fill these TDs with title. So I just put tightly in here in lower kids. So we know we have a title in there. This is what the author's name would go in here. We would then have the genre of the book. We have the height. That would be a number like 312 and then we'd have the publisher. Something like Penguin would go in there. If I say that, go back to my browser and refresh. You can see it start to fill the daddy. No idea. Where were over 100 records? I would not want to do that 100 times manually, but that's where the power of the database comes in. Let's go back here and let's think about what we need to do in order to get that data into here. Well, the first thing is, before the haste him out, I'm going to open a PHP tag. I'm going to close that PHP tag. Now we know the first thing we need to do is we need need our DB conflict. So we need to require once What are we requiring? It's going to be the DB conflict. Need to go back One folder on D B config Don't PHP on our semi colon to finish. We then need to run our query. So the query statement itself less design. That's the query is that we're going to select everything's all the fields. So it's like star from books. I no need to run that query on if you recall. The first thing is we need our DB connection on the object. We're going to run a query on what query are we running? We're running this query above Semi colon to finish that lane. That query should pull in a set of results and I'm going to store that in results. So results equals that now, once we have those results, we need to loop through them. So for each something, do something in here. We're going to take those results as and pull them out one by one. So result and then in here, this is where we'd start to echo at those results. Now that has to go in down here, So I wanted to start in. Here's this table. Row on all these TDs. This is where the records will be. So in other words, this section here, I'm going to have to take that and drop it down here. Let me just cut that. Let's bring it down here. So after my table body, this is where I'm going to start my loop. But my loop needs to be within AP HP tag. So open up, PHP How money to close that PHP tag here so we can see the colors changing as we do that. Let me just indented that Now I know that this here is going to be repeated for each of these results. So cut that from there on, what I'll do is I want to paste it in here. We can see those colors of change because I want to exit out of PHP on re exit are re enter . I should say back into PHP here. Now there are several ways we could actually do this. The first way would be to place an echo statement in front of each of these lanes On the second way would be to exit out of PHP. And then just where we have the data, we would re enter into PHP on just pull that result set for that actual value. So either way in this begin, of course, is gonna be acceptable for us just to get into the rhythm of what we're doing. I think in this example, what I'm going to do cute things. License simple. I am going to exit out of PHP here, and I would need to re enter here, so hopefully that makes sense. We're going to start our loop here. We end it here on in between. This is where I've got my HTML. Let me just in dent things a little bit. So intent that on Dial in Dent, these TD's There we go. Now, this value here, that is going to be a life. Are you from the data best. So I need to delete that number one and then I'm going to enter back into PHP. I'm going to exit PHP. I would have to do that for each off the TDs in here. I am going to echo. I'm going to take my result. And in square brackets and with tick marks, I'm going to put the field name in here that I want. Now, this is the I. D field. So I need to do that for each off those fields. Now, to save a little bit of time, I'm going to copy this. Going to select these? I'm just going to paste in at T D Hoops stood again. 345 and six. Now the i d. Here that will be the title. This will be the author. This one here is the genre. This one That is the height on this last one that is the publisher. So if I say that, go back to my browser here on the refresh there we have a full list of all of the books. The last thing I want to do is actually stale This a little bit more. Now, this course doesn't really cover CSS, but I think it's important for us to throw a little bit in there just so we can see the possibilities. And to make things a little bit easier, I thought would actually be nice to use Bootstrap. Now, bootstrap is a very quick way of actually stealing a page. So I go to get bootstrap dot com Click on Get started I've done here. It gives us a link to copy for the CSS so everything is built in here. So just copy that. Go back to my fail and go to the head section under the title on Just Paste That in. The last thing I need to do is to go to my table and give the table a class. So classes like a little rule. So the class we want to apply is equal to is just equal to table. If I say that, go back and refresh, we can see we've got a bit more styling. Now that's the full width of the page. I want to just bring it in a little bit. So what I'll do next, Here's after my body. I'm gonna put in a diff tag with a class off container. Now there's a nice, easy way to do this in them again. We just tape dot con Taylor impressed Tab, and it creates that little Div with container. Now this closing div going to cut that and that needs to go before the end body tag. So way down here after this table tag here we're going to pace that in. I just pull that back a bit. Okay, so that's the Dave done for their this whole table section. I'm going to grab this whole table section, and I'm just going to invent that. I'm going to save, go to my brother and refresh, and hopefully you can see that's just moved in a bit. It makes it a bit narrow. It's easier to read, but it still looks a lot nicer than the initial data. It looks like this, so I hope you found that useful. There is a lot to learn in terms of PHP and integration with HTML, but as you can see, we can get some beautiful output. It looks a lot clear than when we started off here. I also hope you learn a few little tricks there by using em it. It is a beautiful till two years and love little plug in on some of little short cuts Really do save your time Now. If you have any questions, please use the Q and A section. If I was good, let's move on to the next lesson 17. Adding an edit icon: in the last lesson. We finished off with credit. This nice little table. All results for the books. Now what I want to do. This lesson there's extended a little bit further on. Imagine the client wants to be able to edit some of those records. No, one way we can do that is by presenting the table like this on having an edit column. We have this little Eddie icon. Here is a link we can click on the link, and it brings us to an edit Pidge. Now what we're doing is we're sending an I D. Off the book. So that's the data base I d. Of that record, sending it to this at its script on this edit script is them picking up that I d. So let's go ahead and start to style this form so head over to code anywhere. And before I go any further, I just want to tidy things up a bit in this directory here. This is the books folder that we've been using, and that was in section three. So I'm going to copy that. I'm just going to paste it into section three. Okay, that's done on a secret. A new folder. Call this section four. That's that done. An attack books folder again. And make another copy of that on DPA. P into section four. So this is the folder that we're gonna be currently working in. Section force. Let me just close that now, in section four in the books we have are DB config in in our public folder. We've got these here now the list books too, And the edit filed. That's the one I've been using just to show you what we're going to do. So what I'm going to do, I'm going to get rid of this one. So delete that, and I'm going to delete this that it file as well. Okay, so this is what you should have currently, if I click on list books, this is just what we did in the previous lesson. Let me just have Ah, look at that. So go back. A few folders on would go back to the root folder and you can see we've got a section four now. So going to section for go into books, going to the public folder. I know I can actually list those books. And there goes. That's where we're starting off. The first thing I want to do is in this table header. I want to remove a few things. I want to remove the I d. So click there and delete that. I want to leave the height and I want to add under their table header for edit. So it's probably in table header. I Let's put at it in there. So if I say that, go back and refresh there, we can see we've got the head of sorted. But now, obviously, we're still actually presenting the A D on. We're presenting the haters Wells. Let's deal with that. So down here we can get rid of the I d. We can get rid of the height and then here I need to enter another table, sells another TD, and this is where we're just going to be able to edit that records. Just save that. Go back to the browser and refresh. And there we go. Things are looking a little bit better now. What I want to do here is replaced this word edit with a little icon and I'm going to use font. Awesome. I don't If you've used it before, but go to font awesome dot com. And then once you're in front awesome dot com, we're going to click on how to use. And if you scroll down a bit, we've got this atl English stale sheet, which we can copy his little copy icon. He as we click on that, that's copy that code. Now we can go back to our Fail go to the head section and just after the previous CSS link , we're going to piss that in, and I just invent that and pull that one back. Okay, so that's not tested in there Now. It's not going to do anything yet because we need to get the icon, the icon. We're going to replace this at it. Word here. So going back to front. Awesome. Click on icons. Now we can actually search for a Nikon we want. I'm going to use a little pencil. I cancel type in pencil on the one I want. Is this one here? This little icon here for for edit. I'll click on that, and there we can see the icon and done here is giving is the code to grab some? Just copy that to my clipboard, Go back to code anywhere and just pay studying. I'll save that. Go to my browser and refresh. And there we have that little pencil edit icon now. Currently, we can't click on it. We haven't gone ankle tag on there, so let's go and put an ankle tag on that. So after the T D, we're going to put an anchor with a headdress equal to just put some double quotes in at the minute and then move that closing tag just before the closing T D. We'll save that refresh. Make sure things working there would go on. We've got this now as a little hyperlink. Also want to click on it? Nothing's going toe happen. So in the next lesson, what we're going to do is we're going to create a little hyperlink, and we're going to create the edit file on. We're going to send that little click to the edit file and then take things from there. 18. How to create the edit page: in this lesson. We're going to finish off this little edit icon now. We left things off where we credit the icon and we turned into a hyperlink, but it doesn't actually link anywhere. We want to go to an edit fail, and we want to send a query string in Are you Earl? Let's see how we can actually do that. The first thing is, I want to create a new fail here, so I'm going to right click on www crit a file going to call it at it dot PHP. So we created at it. Fail. I didn't hear Let's open their PHP code block on the first thing Will we need to require once Andi, you should know this require ones we need that d be conflict fail So db underscore config dot PHP No one actually using that yet, but I might as well put it in as a starter, and we'll come back to that in a future lesson. Let's go back to the list. Bookstop. PHP Now in here in this hatred section here, just in between the quotes. This is where I want to put the link on. I'm sending it to reddit dot PHP But I also want to pass in that query string. Now we use a little separator, a question mark on passing the I d. So the I D is equal to and it's equal to a number and that number is actually pulled from the database. It's the result i d so in here. I need to open PHP tag and close it. And then in here I want to echo at the result on the result. I'm actually Queener is the i d. So if I say that but my brother and refresh, if I just hover above that you should see in the bottom left pill in your browser, I can see the idea in mind Fact flick on it. We are being sent to the edit page and I can see that query string with the I D equal to five. Let's go back to the edit script. So what I want to do here is I want to pick up that I d on the way we do. That is by using the get method. So we get now. What are we getting? We're getting the I d. So this I d. Here refers to the I D that's being passed in this query string. In this case, it was number five. Now that gets I D. I'm going to pass into a little variables. We can use it literally. Wish so I just call it I d equals. Let's just echo at the ideas. Let's just echo I d. We'll save that. Go to my page. Let's go back here and let's click on another one that to come Number two and there we can see we've got number two here. That's all working. We seem to be able to send that query string to the edit file on the edit file is able to pick that up. Now, in the next series of lessons, we want to be able to use that to actually identify which record we want to edit. We need to present a little form for the client. They will enter details into the form and actually click submit, and that will be sent to the data vis. So I'll see you in the next lesson. 19. How to populate the edit page: in the last lesson, we ended things where we had the list of books on. We had the little edit icon over here on the right on When we click on the icon, we've actually retrieved the i d from the query string at the end of the year. L now I want to do this lesson is to use the i d to retrieve the record from the database and present it in a little form with a little button on the bottom. The client can then come along, edit the fields, click submit, and that data is sent to the database and saved there. So in this lesson, we're going to take care of that particular form. This is our at it dot PHP fail and you can see we've got that echo I d. That's where you finished off. So what I need is a hasty male form to add on to this page here. So I closed my PHP code lock now because this is a course on PHP and my sequel PDR, etcetera. I don't want to be taping lots of haste here, Mel code. So what I've done for you is have produced the hasty Mel in a little text file, which is in the resources area. So if you go to the resources download edit page html code dot txt. I'll give you this card here, just download it. Copy, and then we're just going to pay studying. That is the code for the hates TML form. Now what I'm going to do here on coat anywhere, I can actually close some of these little dips here. So the little area here can close that. Dave, we're gonna close those six main dibs. Andi pro recognize? Those six will be for each of the six fields. And the reason I've done that is I just want to show you the structure of what's going on here. So what we have is we have a doc tape declared We've got the language being English here. We've got the head section. Got little title there on. We've got a link here to the bootstrap CSS that reviews before down here. We've got a body tag and we've got the Dave container here. Within that container, we have a form. The form has a maternal submit bun, which says update record. And in here, we've got these six. Steve's here. So just to open one of these will have a quick look. We have a level on It says it's the I D level. So it's the label for I D. And it means for this input field here with an i d off i. D. This is the input field. It's a type of a number. It's a read only field because it's the idea it needs to be read only We don't want the user actually changing the idea of that particular record. Currently, there are no values stored in here is what we need to do is actually trees the values from the database on Pop them in here. Now let me save that. Go to the browser. And if I accept, come with these books. Number five. There we can see the table and how it's going to look here. We have the hidden field, and we're going to populate that with some of these books. If I go back on, let's have a look. If you don't know bootstrap, we've got in here. We've got different classes, like dip, different rules. This has got a column small to now. If you're not sure. I never use bootstrap before boost that works on a 12 column grid. So over ST two columns for the level and we've assigned 10 columns for each of the inputs, so this would be to go back to that one. We've got two columns for the level and 10 for the input, so hopefully that makes sense. As I say, I don't go into too much detail on this Now. The form itself has a method off post, and it's got action, which is sending this form to the update dot PHP script. I've already written that you can see it here and I'll go through that in the next lesson. Let's go to the top. We've got this echo I d We can get rid of that. We don't want an echo. I d. There. Now, what we want to do is want to take this I d on actually use it to retrieve that particular record, and we start with a query. This is our query statement where we're saying select, uh from books where the I d is equal to that particular I d. Now, this is not a particularly secure way of doing this. One of the first things we wouldn't need to do is to check that this I d. Is valid. I d is an interview value. The user might have changed it to letters, and also it's coming directly from the U. R L So we do need to do some checks on that. Now, we're gonna cover that in the next lesson in this lesson. I just want to take care of this Dead of it's cold here, and we're going to use something called a placeholder. Now there are several ways to do this on the way I prefer is to use a colon and then give it a name as well. Call it I d. No. That query, once it's executed, should return one record from the database. But just to be sure, I'm going to add a limit off one. And that will definitely only retrieve a maximum of one record from the data. Beth. Now that we have the query statement we've seen before, the sort of thing we've done, we've done like a DB connection. Then we've done an operator on that. We've done something like query, and then we've said, Well, run that query. And then we finished with a semi colon. Now, because we're using place holders, what we want to do is we want to do something called preparing. The statements were going to prepare this query statement here. Now, the results of that we're going to put into result the Realtor result is equal to that. And what we need to do next is we need to execute that. So we're going to take that result going to operate on that and we're going to say we want to execute. And in round brackets now we're going to execute an array are arraigned. This particular case has only got one value, which is going to be the I D. But I prefer to use this method so there's only one method would need to learn. And we can use this for all queries. So in here will have square brackets are going to split open those square brackets on. What I need to say is, we're going to take that place holder, I d. And we're going to have seen it this particular value here from the Urals for the i d. Now, by doing that, we've kept the actual query statement on this particular value here separate so that value never actually goes into our database call as such. The next thing I want to do is to actually fetch the result. So result, and we're going to operate on that and we're going to say Fetch, that will fetch result from the data. Beth, just save that. Now that we have that array of results, what we can go down to is down here to the value we can actually retrieve the result for the I. D. So because we need this in ph. People need to open a pitch PICO block, and then we're going to close that code block knowing here, I need to echo out the following gonna take the result on going to echo at the I. D. If I save that back to my browser, we'll click families books than before. We have an error here, and that's a good thing because it lets you see what way we can actually fix errors. I know the era is somewhere after the I D labels. Let me go back and have a look. Now it's probably something I've done here. Nothing so case there's no ever there. So it must be further up. And there it is. Look, so in here have actually called that fetch, but I haven't actually assigned it to anything. So need to assign that to results. A result equals that Save that good. My president refresh again, and there we go. We've gotten him before a period, Going back to code anywhere on. The next thing is we need to do that for all five of those dibs. Now, if you want to pause the video here, feel free to do so. You can try and fill in. Those next five should be fairly simple to do. All you need to do is go to the value and passed in something very similar to that. If you just falling alone the video and you don't want to do it yourself, then feel free just to watch as I go through and do that. So I'm just going to copy this. Copy that. I'm going to close this, Dave, and I'm going to open the next one. I'm going to piss that value in there now. This is going to be the title, and I can close that one open the next. That's going to continue all the way down. The author on this one, there was an open on we've got next one is genre. Next one is height. Unless one is for publisher. I want to get this last one in. We should be able to save and have a look and make sure everything is working. Say that good, my brother and refresh. And there we have all of the details for that particular book. Now we do have a problem in that. If we remove this i d. From the end of the barrel, I'm press enter. We can see this undefined index i d. And that's because the beginning of our script appear. We're actually looking for this, get I D and it doesn't exist. What we need to do is we need to do a little bit of error trapping and start to validate some of those inputs before they even get to the query on will take care of that in the next lesson 20. How to use fetch: in this lesson. I just want to delve a little bit deeper into that fetch command that we used in the previous lesson. And I want you to understand what's actually going on in terms of the data that's being retrieved from the database. So what I've done is I've credit a separate fail on this file here sort of replicates what we're doing in that previous lesson. So what of God's is also I'm just pulling in that d be conflict file, but I'm manually setting the idea and I've just chosen any number of 56. I then have my query string same as before. So I'm pulling all of the fields from the books table where the idea is equal to that I d there on a cautious limiting that to one record. I'm then preparing that statement and storing the results in this object here result. And they were going to execute that on my place. Older becomes that I d and you recall what we did is we took that result and we fetched all the data from the database. So I want you to know what that fetch actually does. The next thing is seriously show some output on the way I can do. That is I could just say print I can print our on going to print are on the result set so that if I say that, go to my browser and refresh. You can see we get this array. No, it's quite difficult to read the minutes. So what I'm gonna do, we gonna go back on just before this prince, I'm going to echo at the following on going to equity. A pre tag on that were just tidy things up a little bit for me. Say that. Go back and refresh. Now it definitely makes things a little bit clearer. We've got the field and we can see the idea was 56. We've got the title of the author, but everything is repeated. No one is doing is putting the field. It's also pulling the position. So it's the index set position zero. We've got 56 at position one. We've got the title of the book, etcetera. Now, we don't need all of that because what we did in the last lesson is I'm just going a little break here on. We did something like this we said that we want to echo and we took the result and we said I want to echo what? The title that makes sense That's nice and easy to retrieve. Save that go back, refresh And there's the title. So it seems the part of the array that we're interested in is this part here, the one with the I. D in the title of the author. We don't need this index so we can actually deal with that by looking at this fetch here and in here. We can say P d o to Coghlan's cities commonly call a static method and we can say, Well, we want you to fetch, But we want you to fetch the associative array. So that's the ones with the title and the author, etcetera. If I save that will go back and refresh. You can see this time it has no see duplicated and pulled those index values and as well. But what if we did want the index values and not these values? Well, we can simply change this and for in here, numb. Save that now. Here we will get error because it doesn't know about the title. It knows about the number but doesn't know about the title. So the index yet we know about that, but not the title. Now you can see undefined Index title. Now, if I put a number value in here so the title would be at position one. If I put that in and save it and refresh their I get the title now. To me, that doesn't make sense. In this case, I would actually pull in the associative array and then change that back to something I can actually remember which is the title. I don't want to be remembering positions in the database for that array. So this makes sense to me say that Go back and refresh. And there we go. Now we can pull in both. So we'll go back on. It will just say both Save that we can refresh, and then we go back to both again. But both is the default position. So if we leave it blank, we will still get both on this particular case. We don't need both. So I'm just going to do a p d o and fetch underscore a sock for fetch associative seven. Go back. Right. That and refresh, and they were back to that associative array. Now, to me, that makes sense to do that. It doesn't make sense to pull all those values in hopefully got something at this lesson. You understand a little bit more about what's going on in terms of that fetch on what data is actually being retrieved from the database and the former that it's actually in. 21. Error trapping: in the last lesson. We left things such that we had a list of books in our list Books page. We could click on the edit button over here and when we click on at it, were presented with the record from the database. Now the issue we had is when we click on update record, we get an error, this undefined index, i D. And that's because in the u. R L the I. D part is missing. So we need to fix that. So head on over to reddit dot PHP And the first thing I'm going to do here is just credit a little bit of specially conceived what I'm doing. So I'm going to create an if statement. So if something is true, I'm going to do something, and in here we're going to have a little test. Now what we want is the i d. Part of that query string to be present. And if it's not, then we want to give a little message or even redirect back to the list of books page. So if not, that's the exclamation mark is set. What is not set? Well, if not set get I d is not set. So if that's not set, what do you want to happen? While we could echo a little error message so ever and then what you want to do, we want to stop the script at that point. So die. We saved that. Go back to the browser on Let's just try that. So going to edit dot PHP? Let's take off the i d Part of the question Martin's press enter on. We do get that little error now. We don't just want to present an error. We actually want to redirect the user toothy list of books page on the way we do that is with a header. Redirect. So we type header Rome brackets. I didn't tick marks here with a capital L. We take location colon on. We redirect him back to list books dot PHP. Now I'm going to leave in the day. Part here is well, sometimes this had a redirect can actually fail, In which case we would not want to continue the script done here. We want to cut that script. At that point, let's try that. So I'm on it. It that PHP will try that. And there I am redirected back to list books dot PHP Now, if there is an I d set, what do you want to happen next? Well, else we want to do something else. And what do you want to do? Well, the i d. It doesn't necessarily have to be a number they could have typed in some letters. Let's try that. If I go back to here and let's pick a book So a good book number four here bless. Change that to say asd I get a blank screen. Why dealing that kiss? I also want to redirect them back to the list of books. So in here we have to run a little test. So if something is true, I'm going to do something now. This time What I want to do while I want to check if that I d is an interview value on just down here, actually tape it in So we run what we call a filter var. This is an inbuilt function in PHP and it works like this. So filter underscore var that in here What I want to test Well, I want to test that Get i d underscore get I d on what don't want to do on that. Well, I want to run a filter on that filter is to validates on one of my validating and validating if it's an interview value. So if that is not an interview value, then so if it's not and into your value, then what I want to do well again, I want to do that. Had a redirect and day, so I'll grab that Copy it. I'm pasted in here, so that takes care of that. So if that is not going to work, then I'm going to redirect if it is going to work. What I want to do, then. Well, what I want to do is I want to run through all of this. So I just grabbed that and cut it from there, curious on those spaces. Then we just pop that in there. Now I've got a little bit more to do with this, but that will do for the time being. So we'll save that. Go to here. So now we've got this idea is a s day, so it's not an interview, or if I press enter, I should be redirected. And there we go. We're redirected back to that list of books that has worked as well. The next thing I want to do is this a d they have set here. I also want to validate that. So I'm just gonna run this filter var rannell that copy that I'm going to run that on that . Get I d. Get rid of that. In the end, that will filter the I. D field. The I D Field, then is being passed into the query on the database on, then were retrieving the results. So that should be a little bit. Say, first, let me just say that. Go back to here. Let's click on a book. There we go. We've got book number six. Let's click on Update record, in which case I should be redirected and there I am, redirected. That's like on another book appear. Let's put in something like Press enter and there I'm redirected. That's a little bit safer now. This is a beginner's course, and in terms of security, Emily showing you I'm even just scratching the surface of what we need to do. If you are considering developing a site to go life, then you really need to know your security on, I would highly recommend you find a course either on you Tamir elsewhere that deals with security in mawr depth and what I'm showing you here I am by no means covering everything. Now, in the next lesson, what I want to do is actually look at this form and how we can actually change some of the data in the form on Send it to a script which is then going to update the data best. 22. How to update a database record via a HTML form: in the last lesson, we took care, some filtering and some validating off the i d. Now, just before we move on to look at the update script, I just want to tidy something up. I noticed. Here, I've got this I d where we're doing. The filter of our It's almost a repeat of this lane here. So just to tidy things up a bit, I'm going to cut that from there. Just tie typically spaces here on. I'm gonna pop it in here just in this else here. So just in line above Which means then that I can remove this to that point there, and I can just put in dollar I d. Okay, so the logic is still the same. It still doing the same sort of thing. I've just taken out some of that repetition. Just take it apart a little bit. Now I want to do is want to move on to the update script. So if you open your update script that you have, you should remember we did something like this earlier on in the course where we were just updating the books. We were setting the Taito on the genre where the I d was equal to 118 and then we echoed at a little success or failure message. Now, what we currently have is we have a form. Here's our form on when we update the record, we want to send this information here to update script. I want to retrieve that from the update script. We want to test everything is valid before sending it to the database to actually update the record. So what I'm going to do, I'm going to remove all of this. I'm just gonna have a little think about the logic and the steps involved. Well, the first thing is, I need to see if that submit button is actually being pressed on. The way we do that is follows. We say if something is true, we're going to do something. And what are we testing was saying? Well, if is set something on what was being set, it's gonna be the post square brackets and tick marks is gonna be that Submit button. Now the submit button if we go back to our edit script, Didn't the bun the button has a name off update record. So company that goodbye to my updates. Krypton just paste it in there. So in other words, if we've pressed update record, but then we can actually follow through with the next step. If we haven't, then we do something else now. What I want to do is I want to reverse that logic a little bit and say, Well, if they haven't actually hit that update record button, then I want to redirect them back to the edit script. Now we've done this before. It's called ahead of redirect. So it's header redirect on what we redirecting, too? Well, we've got a location, which is the edit scripts. So at it dot PHP else. Well, if they haven't hit the update record, but we're going to read right there else. We're going to continue with the script now If you recall last time I said that had a redirect could fail, So we need to put a die Wayne in there as well. On that. Will I see stop the script? That's a little bit of additional security Now. The next thing I want to consider is well, we'll need an I d number. If we don't have an I d number, then There's no much point in continuing any further because I won't know where to send any details to the data. Beth. So I need another if statement. So if something is true, I'm going to do something. Well, what about testing? I'm testing If there is no is set, what isn't set? It's the post square brackets. Tick marks. I d. So if we don't have a post, I d then what I want to do? Well, I want to redirect again. I want to do this. So it's gonna grab that co pay it. I'm just paste it in there. Martha is a post i d. So else then I can actually continue. The next thing I want to do is I want to actually sanitize each of those inputs. So the i d I'm gonna put a equal to the post i d. So that's just taking the information that's being posted from the form and assigning it to that variable there. But we need to actually clean that. So I'm gonna run a filter var on that on. What I want to do here is I want to run filter sanity is number int That will take that input and actually sanitize it for me. I want to do a similar thing for each of the other inputs. So we take the next one. The next one is the title. So that is gonna be another filter of our what are my filtering here? Well, I'm going to fill to the post and it's gonna be the title now, This time it's going to be a filter. Sanitize strength. There's a string input now will be doing a similar thing for each of the other string inputs. So I'm going to duplicate that now in code anywhere. Compress shift command and D r Shift control D. If you're on a PC on then in here, this will be the author. And are you in there? That would help shift command E again. This would be the genre on shift command d again and we've got the publisher now. The other number input. We had waas for the height, so that means copied this once of command d on instead of I d will put in here right now, I just noticed that I've actually spelt sanitize wrong here, So just change that to a new E on either on Dhere is well, And this last one Now I'm ready to actually build up my query. So my query is going to be the following now, this is when we're going to update. So we're updating books we're setting while we're setting the title. First of all, we're not setting the I d were using the I. D. So the title is going to be equal to now. I'm not going to use title here, even though I facilitated it. We're going to use a placeholder still, So it's going to be title now just to make this a little bit clearer to see I'm going to split this onto several lanes. So going to set the title equal to Tate a little. We're going to for the common here, first of all, and we're going to have the author going to equal to author. We then have the Jonah that's equal to genre. We then have heights that's going to be equal to heights on then we have publisher that's equal to publisher. That takes care off those inputs, but we have to have a wear statement. So where the I d is equal to I d I've just noticed again of missem commerce off here. Something to prop those in? No, I do not need a calmer on the last one. If you put a come on, the last one. This will not work. Now the next day Peas. I want to prepare that query. So we're going to have our DB connection and we're going to operate on that and we're going to prepare. Well, then what are we preparing? We're preparing our query now that they're what we can do here is we can assign that to result. We don't actually need to do that, but let's just pop it in any way because we used to do in that. That's going to be equal to that on then. What we need to do is we need to execute that. So we're going to take a result. We're going to say, I want you to execute what I want you to execute. You going to execute an array so square brackets, and then we're going to split them open? No, in here. This is where I start to assign everything. So the first thing is the title, and we're going to assign that to the title coma. The next one is the author, and the author is going to be assigned to the author. I'm just gonna put some space down here. Exes get a little bit annoying, so just so you can see a little bit easier. So we got the author. The next one is the genre, and we'll assign that to genre. Then we have the height that's going to heights on healthy. This is fairly obvious that the next one is going to be the publisher, and we're going to explain that to publisher on That's the last one I've got the I. D. So just don't forget the I D. So Heidi on the end here, assigned to I D just to tidy things up a little bit. Let's just Elaine these. It's easier to read, and there we go. Now just make sure that it's saved, and then we can go across to our form so not formless. Change something we're I Denham before here's let's change that to, let's say, to 10 and click up update record. Now it says here, and define variable authors. You're under a height and publisher and again, that's a good thing we can see what areas we have and try and fix them. So let's have a look at what's going on. Right? So screw up here. I can see what it is because I duplicated things and it changed in here. I didn't change in here, other stupid of me. So this one here waas the height we had the title. Then we had the author in a way is good that you see in these areas so we can I see work through them and see how to fix things. And last thing here is publisher. No, If I save that and go back, let's try that again. So I changed it to 10. Click on Update record, and it looks as if that's gone through. So let's go to the database and a quick refresh. And there we go. We've got to 10 in for the height. Let's try something else. Let's go back and back and let's choose book number one. Let's change it from Hope. Eternal to my book, the author will be Patrick. The genre. Let's have coding. Let's have 100. Andi publishes me. Well, I submit that record again. Looks like that's gone through. Let's just do a quick refresh And there we go that has Bean updated so quite a long lesson here and quite a lot to take in. I do suggest you go through and watch a couple of times before you move on. Look at the details in terms of filter of our like the details in terms of these redirects how we actually stretch to the query on how he actually did this little ray here. No, she is still some work to do. We need a little message here to say it's being successful, but we can take care of that in future lessons. 23. TASK 3: Deleting a Record: Okay, so time for me to have a break and time for you to do a little bit of work. I'm going to set you a little task and hopefully you've got enough knowledge not to be able to complete it. What I want to do is to go to the list of books and to add another column. And in that column, we're going to have a little delete icon. That icon comes from front. Awesome. If you search for delete, you'll finally like on, and you can copy the code for it there. When I click on that little delete icon, it takes me to another script. It takes me to the delete script I am passing in the I. D. It's recognizing the I D. And then just deleting that record and telling me that the record has been successfully deleted. Now a couple of clues for you. You do have a delete script. This will form the basis off this one for you. So you just need to delete from books where I d equals that is going to be equal to that I d that you are passing into the u R. L Now you do have enough information from the previous lessons to actually do this. Please have a go. In the next lesson, I'll actually show you how it's done. 24. TASK 3: Deleting a Record - SOLUTION: So how did you get on with that little challenge? And I hopefully you might to complete it successfully if you didn't Let's run through it together through this lesson here. The first thing I want to do is to add the extra column to the list books dot PHP fail. So down here where I have the table after the edit, I'm gonna do a shift command and D just to duplicate that try again on I just want to change that to delete and then down here, I need to add another cell in here, so I'm gonna duplicate that now. This time it's not going to go to edit. It's going to go to the delete dot PHP script and over here I need to find the icon for the trash cam. Good affront. Awesome. Look for the trash can. Here it is. So it's f a trash old. I just copped that little bit there instead of edits. I'll just pissed that in there. I'll save that good my president and I'll just refresh just to make sure if they're still working, everything is toe working and you can see I've got that link working as Well, now, the next step is to our secret, the delete script. Now you're delete script looks something like this currently, But the main part is this Delete from books where the idea is equal to and it's gonna be equal to that idea that we pull from the R l Now we've done that once already. We did it when we did the edit. So what I'm gonna do, I'm going to copy this section here for the from there all the way down to their just copy that. Go across to my delete script. I'm gonna get rid of all of this. Just paste that in as a little starter. What are we doing here? We're saying, if no is set, get i d. So we're getting the i. D. From the girl. If it doesn't exist, they don't want to redirect the user to list Bookstop pH piece. That's fine. We're then going to kill the script. That's fine as well else. If the i d does exist, then we're going to filter and check if it's a valid interview and we're sending it to that variable their i d. Now, if that's not a valid integer we're going to redirect to list books again and then kill the script. If it is a valid A, then we can go ahead and actually delete that record from the database. So what are we doing here? Well, we're not going to select. We're going to delete. So we are deleting and what we're deleting. We're deleting from Vicks where where the i d is equal to I d on you again. We're just limit that to one. So what I want to do here again? We've got the result. We want to prepare that query. We don't want to execute it. But instead of fetching this here what I want to do this time, I want to see if that delicious has actually taken place on There is a way we can do this in pitch P. Now, you won't have known this, but I'll show you know, it's actually doing a row count. So if I say something like Rose deleted and put that equal to the following what I want to take the result on going to operate on that and say, Give me a row count. So what? That will do it. Let's go cross go through this statement here will execute it and say, Well, yet I have to be did one row, and here we will retrieve the number of rows deleted. So if we have deleted a row, we can then present that little message that says yet one row has been deleted. So I'm gonna keep this very simple to start off with by saying, if something is true, we're going to do something. And in here, if Rose deleted is equal to one, then we're just going to echo at a little success message. Els, what we're gonna do, we're going to echo just a little failure message. I'll save that. So let's give that a little try. So on my list of books page just a quick refresh. Gonna click on the 1st 1 click on delete on there, get a little success message. So that has gone through. Let's go across to the database and do a quick refresh on. There we go. Those records have been deleted, so the drunkards walk should be the 1st 1 So if I go back and do a quick refresh, there we go. Drunkards. Walk is the 1st 1 Is that delete script is working? No. My example. I did show where I'd actually use bootstrap, touristy prettify that little message on If you're interested, I'll show you next how to do that. If you know, you can move on to the next lesson. First time Look down here. So I'm just don't create some space down here because I want to create some Hey, html. So I'm just going to close off that PHP there for a time being on, gonna press my exclamation mark on press tab. And if you recall from an early lesson I said, that's using Emmett. It's a really good to really good plug in for you to have a look at. So the title of my document I'll just put delete in my body. I'm going to have a container, so dot com tenor. Now what I need to do is well, I need to have the bootstrap CSS just in here as well. So I'll go to my list books l scrolled up and just grab that CSS for bootstrap, which is that one goodbye to my delete page. And in here I'm just going to pace that in on save that so I'm not about my container in my container. This is where the messages are going to appear. So, in other words, we're going to take this section here. So what I want in here is I want that little alert box. Let's do that first. Go back. Teoh. Bootstrap. Look at the alerts. And we're just going to copy that little Dave there. I'm just paste it in the right spot when she is just in there. I was going to take it up a little bit, so it just says they're a simple premier. Like to check out. We're gonna change that text to record. Has Beene deleted? Okay, Now that that needs to go within this eco statement here. So you notice I've got if rows deleted equals one. So what we're going to do is we're going to drop back into PHP and echo out of PHP. So I close Ph. D. P, I should say, and then in here, what I want is I want that condition. So if if what? We're going to do something. If Rose deleted is equal to one, what are we going to do? Well, we're going to echo. We're going to echo this. So I want that in here. But I know to do that, I'm going to have to close the PHP tag and then open it Just here again. So PHP. And now I can pop that in here, Just tidy things up a little bit. And then that and in debt that So if rows deleted, then we're going to do that. Els, we're going to do something else. Well, what else do you want to do? We want tohave a div that says the record hasn't been deleted. So copy that. And in here, Obviously going to close the PHP tag and then reopen one again and we're gonna drop that in there. That's a record Has not being deleted. If I save that goodbye to my list is to quit. Refresh the drunkards walk. Let's delete that one. The record has been deleted. And if I go over here and refresh, we can see the drunkard walk has being deleted. No, I didn't notice. We've still got a little success. Messi's there, so I'll just remove that. So up here we no longer need this section here so we can remove all of that. So How do we check if the record is not being deleted? Well, let's go back to here. We can see we've got the i d of it here. So if I just do that again and press enter there, we see record has not being deleted. So hopefully that all made sense. You can see how we're building up these little scripts bit by bit and how we can actually reuse things now. The only thing left to do is to actually create a new record. Now, if you think about it, the correct a new record will be very similar to the update now, because these are very similar. What I'm going to do, I'm going to set you another challenge on the next challenge is for you to actually create that script. Now you can use this update as the best is for it is variously identical. You'll have to also think about the edit function here on the form that you need to use. So it's a bit of a challenge to bring that one in together with the update one. But you have enough that you should be able to do it. Have a go. If you can't do it, I'll be supplying the solution in the actual solution Video. Good luck 25. TASK 4: Creating the form and script to INSERT a record: now, just in case you did skip the end of the last video at the end, I said that what I would like you to do next is to actually create the script on the form that will insert a record into the data vis. No, it's not as hard as you might think, because we have actually done most of the code and we can reuse a lot of it. We have created a form for the edit or the update record script, and we can actually reuse that. The only thing we will not have an i d in here we will have these five feels on. We'll have a create record button. If you go into your edit scripts here, you actually pick up most of that code. Now, what you can do next is we can go to the update script on this update. Script will be virtually identical to the create script. The only difference, really is. Instead of updating books, we're going to insert a record now hopefully has given you enough to go on, have a go, see how you get on if you need to use the Q and a section of the forms before you start looking at the next video on the solution. Okay? And good. Look on this one as well. 26. TASK 4: Creating the form and script to INSERT a record - SOLUTION: So how did you get on with that little task? Well, here is the solution in front of you see the form that have created and it's just a duplicate of the one we created earlier. Now what I can do in here, I can enter a title for the book. I'll just call it my book. Author is me Genre. Let's have general height to 29 on Publisher Penguin. If I add record, go to my database and refresh over here and then we can see that record has bean inserted correctly. Okay, so how do we do that? Well, you do have your create script, and this is what we had last time on. The important bit is this bit here, insert into books, we have the fields, and then we have the values that the values are going to be placeholders on. Then we're going to need to prepare that statement, execute it. No, We could even pop a little success message if you wanted to know if you notice I didn't bother with that, but it is fairly easy to do so My first step is I'm going to get rid of all of this and I should recall. I said we can copy a lot of what we've done previously on. I'm gonna go ahead and do that. I've got my edit script here, and this is where I had the HTML. So I'm going to copy that Goodbye to my create script. Going to close my PHP on, just pissed that in ready. Next thing is, I'm gonna go to my update script. I'm just going to copy this top section here, grab that go on my script and that needs to go to the top up here. All right, let's start with the haste email. First of all, so in the html, what I don't need is the i d. So I could get rid of that. Let's delete the i. D. I do need to title, but what I don't want tohave is that value being being populated. We will get an error. If it did that, I need to get rid of that one as well. Scared of that one, I need to get rid of all of these and finally for the publisher. Let's get rid of that one. My, my button, it says update records. That should change that to add record? No over here. Let's change that to have a record as well. That takes care of the hasty mail on this side of things on the pitch. P Let's have a look at the logic. It says, if not post update record, then we're going to redirect. Well, I'm not going to redirect here. I'm going toe, have the form on the pitch pee in the same script. So just is a little reminder for myself there. I also need to change this. So in the form where I've got method post the action, I'm not going to send it to another page. I'm gonna send it to itself. I'll show you have it works. So in here, where we've got, if not is set, I will change that, too. If is set post and record, then we're going to do all of this. Well, no, all of it. Want to delete some of it? We don't need to sort the i d. We get rid of that one. We do need to filter the height, title, author, genre and publisher. The query is not going to be update books. It's going to be insert into books And what do we inserting? Well, in brackets we put the fields. The first field, obviously, is the title of the book. Then we have the author, the genre at the height and the publisher. Then after that, we get rid of these. After that, we insert into those fields we inserts from values on. The values are going to be placeholders. So what I need to do in here, I need to say Total, we'll have the author the genre height on the publisher. So that finishes off the actual query statement. The result is gonna be will connect to the database. We're going to prepare that statement and we're gonna execute it. The everything we don't need. We don't need this. I d here so I can get rid of that on the last thing. Just check on the closing curly brackets here. So that opening Carly Brackett finishes here, so I don't need that one. I'll go ahead and save that. Go across to my page here. I have my create forms I'm going to create to this one I just created. So the pace looks to be okay there. Let's see. Even put something in for the title Fundamentals of Wave. Let's let's put in all of this and Penguin unless at a record it seems to have worked. Let's go to here on the refresh and just check it. And there we have. That record has been successfully entered now. Hopefully, that made sense. We did just reuse a lot of code because already there's no point in read retyping all in again. Hopefully, the structure of it makes sense and the logic behind it. What you might find a little bit different or a little bit strange, actually, is that we using this form and we're sending it to itself. Now. If you've never done hit paste email before, you've never dealt with forms. That might be a little bit strange, but it is something we can actually use within this on. The reason we get away with it is because we're testing. If the button is actually being pressed before we do any of this now, we could extend this a little bit. We could put in a success message. We could put in a failure message, etcetera, and to do that again, we just used these bootstrap alerts. Just copy that, Dave and just pissed in the relevant section with a little conditional statement