17: Query A Database Using OOP PHP | Prepared Statements Using PDO | Object Oriented PHP Tutorial

so in the last episode you learn how to create a connection to a database using object-oriented PHP and in this episode we're going to learn how to fit data and how to insert data just in general how to interact with the database using object-oriented PHP just gonna go ahead and slide this out so you can actually see here so this is what we did in the last step so we went ahead and created a class called DB age which connects to our database and using this connection we're able to interact with the database and actually pull out data or insert data or do anything to interact with the database now what we're going to be doing here is we're not really going to be focusing on the MVC model in this episode we're gonna do that in the next episode because I think it's better that you just kind of see how we interact with the database and then afterwards we can focus on actually doing it the MVC way so for today we're just gonna go and create one other class so I'm gonna go up inside my classes folder I'm going to create a new file and I'm just gonna go and call this one something like test so we're gonna go and say test dot class dot PHP inside here I'm gonna go and open up my PHP tags because we need to actually write PHP code and I'm gonna go and create a class that I'm going to call test now the important thing here is that we will be interacting with the database at some point inside this test class so we do need to be able to access our connection that we created in the last episode so inside where we created our class we do need to extend to our DBH class otherwise we can't access the connection method we have inside that class in there so with this we're actually ready to go ahead and interact with the database now the first thing I'm going to be showing you is how to pull out data from a database not using preparedstatement so you might be thinking well why should I show you how to do without prepared statements well if you don't actually have user input from a user inside a website you don't technically need to use prepared statements since there's nothing that the user can submit to you that it's going to be uncontrolled so if I were to just go ahead and say well I'm going to just fetch something from the database and it's not something that needs to be based on a user input we can just go ahead and do without prepared statements so I'm going to create a new public method here I'm just gonna go ahead and call this one something like get user guys and code get users and inside this method here I'm going to start out by creating a SQL statement that will actually go in and be the statement that we run inside the database in order to fit something from our users table because do you remember that in the previous episode I said that I had a database set up let me just go ahead and show you what exactly I have in here so inside my database called Hopi PHP 16 because there was the previous episode I did not think the name through when I created it because it is episode 17 now so this doesn't really make sense but anyways inside the users table you can see that I already have three uses inside of here and with this information I'm gonna go ahead and take these users and actually show them inside my website so if I go back inside the website here you can see that I cleaned it out I refreshed it we have nothing in here because we have nothing sort of information going on so we're going to actually take our users and display them inside here going inside my document again what I'm going to do is I'm going to go inside my method here and I'm first going to create a SQL statement now we can minimize the code a little bit here but because I like to keep things organized I like to create a separate SQL variable the disorder contains the entire SQL information that I'm going to be then running inside the database so this is just the the preferred way that I like to do things so inside this statement here I'm going to say I want to select all from users select all from users the next thing we're going to do is we're actually going to go ahead and run this inside our database and do keep in mind when we created our connection here inside a DB 8 class you can see that we set a optional parameter that said that ok the fetch mode had to be as associative arrays when we fetch something from inside the database so what we're going to be doing here is we're going to create a new variable which is called STM team I'm gonna go and set equal to and now we need to point to our connection inside our DB 8 class now usually if if I just had the connection included inside this file here at the very top I could just go ahead and say well variable PDO because that's the one that we created here that's the one that I need to reference to but because it's inside a separate class we need to do this a little bit differently so instead I'm going to say that because I'm extending to the class I can say variable this because I'm talking about this class and every class that I extend to and then I'm going to point to a method called Connect which is what I called my method in there and then I'm gonna go and point to a method that is built-in to Optik learn to PSP or just Bill into PHP in general which is something that is called quarry parentheses semi-colon now inside the query statement here we need to include the actual SQL statement so I'm just gonna paste it in here and now the next thing we can do is we can simply go in and actually fetch the data so if I go down to the next line I'm going to create a while loop or a while statement and inside this while statement I'm going to say I want to create a dollar sign row variable and I'm gonna go ahead and set it equal to the statement that we created up here so statement and they're not gonna point to a method called fetch there we go now usually like I said before if I did not have set a fetch mode to associative arrays who would actually need to set that inside the fetch mode here but because we set that default parameter inside our DB 8 class down here we don't actually need to do anything in here which is kind of nice because it saves us a lot of trouble when it comes to the fetch mode now if I do at some point need to use a different fetch mode you can just go ahead and go in there and just write another fetch mode inside the actual method here but for now we're just gonna go ahead and use associative arrays so what I can do now is I can simply go inside this while loop and I could echo out some information so if I wanted to echo out dollar sign row say I want to reference to the table column call users underscore first name because remember we're getting this as a associative array which means that we're simply getting this as an array where we have named indexes which means that the first index inside my array if I were to go inside my actual database here you can see that we have uses underscore ID uses underscore first name user underscore last name and so on these are going to be the names of the different indexes that we get returned as an array so if I want to refer to Daniel then I refer to the column called user's underscore first name so if I go back inside my code that is what I'm referring to here I'm just going to go and add a break here just to have it and there we go so if I were to actually go and stop my website now wait actually we need to do one more thing I need to go inside my index page because right now we're not actually fetching anything inside our database we're just simply setting up the classes so inside the index page I'm just gonna go and open up a pair of PHP tags down here inside my body tag and inside the PHP tag we're gonna go and create an actual object based off my test class so I'm going to say I have a test object just to give it some kind of name and it's going to be a new test parentheses because this is the test class we're instantiating and I'm going to refer to the method that we just created which right now if you go in here is called get users I'm just gonna go and copy the name here and I'm gonna refer to test users now because I'm already echoing out information inside the actual answer here like this is going to be the output this is going to be like we just need to go ahead and just run it but if I just return some data inside this test class here if I return this and not echo dead then I would need to echo in front of here in order to actually get out inside the browser just pointing it out because I have gotten questions about it in the past so with this we can actually go inside our browser and as you can see we get all the names in here so we're getting Daniel Nicholas and Dennis which is what we're trying to do here so this is with out prepared statements which you can argue is now you're safe but again if we don't have an user input then we don't actually need prepared statements so going back inside our code we're gonna just go ahead and copy paste this one time because why delete it when we can just keep it for notes for the future right so just gonna go and copy paste and then I'm gonna call this new one get users s TM T which stands for statements so what I want to do in here is I want to change the SQL statement because this time we actually get a user input from somewhere so I want to say that inside my statement I want to get all from users where users underscore first name underscore first name is equal to question mark because we're gonna fill this out later this is just going to be a placeholder and users underscore last name is equal to question mark okay so with this I essentially left in some blanks and what is happening when we use prepared statements is that we're actually gonna go and take this statement here we're just gonna run it inside the database first and then later on we're going to insert the information because we do it in this order here it allowed for us to prevent that users inject something into our database because it runs the SQL statement first without any input and then after we just fill in the blanks so that sort of avoids us running the the data that is submitted by the user together with the statement inside the database which makes it safer so with DES we're gonna go and do this slightly different down here so inside our statement variable I'm going to still run the connection but instead of running it like running to actual statement first instead we want to go ahead and say we don't want to quarry it but instead we want to just prepare it so we're preparing the actual SQL statement inside the database without the user input so we're going to say prepare and let's actually just go and delete this down here cuz we don't actually need to see that so after we prepared the actual statement we'll run the SQL statement inside the database the next thing we need to do is we need to fill in the blanks because right now just question marks blanks and we're just going to go and fill those ends so I'm going to refer to the same statement that we have up here and then I want to point to a method called execute which we're going to use to actually execute the data that I want to submit into the database at least in order to to run this information inside the database now execute does require a array so I'm going to say want to have brackets in here and then I just want to fill in the information that were submitted to us by the user but hold on where is the information from the user well we need to make sure that we actually send that in to this method here when we actually start using this method so at the top here I'm going to include a couple of different parameters so we're going to say that we want to include a variable and we're gonna call this one first name then I want to include a variable last name and again this is information that we're going to include once we actually need to run this statement here inside our index page so we're not gonna include the actual information just quite yet but it will get included once we refer to this method here so we can actually gonna take this first name paste it in as the first one and do keep in mind this is chronological meaning that the first question mark up here will take the first parameter inside our array down here then the next parameter is going to be the second question mark and so on and so on and so on so we can have as many question marks as you want to you just need to make sure it's chronological inside the execute method so once we've done this we can actually go ahead and say that okay do we want to fetch just the first row or do we want to fetch all the rows inside the database here because when we use prepared statements we have to do slightly different so what I can do is I can go and say well okay I'm gonna go and create a variable I'm gonna call it names and I'm gonna go and set equal to our statement up there so variable is TMT and I want to refer to a specific method now previously we referred to fetch but because we're doing this slightly different using prepared statements fetch is just going to fetch one row inside the database meaning that I can't get all the names spit out inside my website so if I want to get one name we of course disuse vetch and again because we have the optional parameter set inside our database class right here as associative arrays we don't need to fill that out inside the fetch method here now if I do want to get more than just one row inside the results here I can say fetch all instead which will fetch all the different rows inside the the result so let's just go ahead and do that for now so the next thing we can do is we can actually go out and take this information that we can actually spit it out inside our website using a for each statement so I'm gonna say for each parentheses curly brackets and inside D for each statement I'm gonna go and take this variable names and say as variable name and I'm just gonna go ahead and you know sort of write out whatever I want to output inside the website so if I want to echo out the first name of the user I can just simply go in and just like before we can actually go to copy/paste what we have up here that might actually be simpler we just got and copy-paste it out like this so if we were to go back inside my index page then instead of referring to get users I'm gonna refer to get users STM team and we do need to insert two parameters so I'm going to say want to get the user named Daniel and I also want to make sure I include the last name which is Nilson and if it were to go ahead and save this go inside my website refresh well undefined variable row let's make sure we fix that error line of course we can't just copy/paste up here we do actually need to refer to variable name and not just use this first name so like this we can actually go back and set our website refresh and as you can see we get Daniel we could also wanted to just get the date of birth what was that inside our database it's just going to double check that what's referred to as date of birth just like spell out like that it's going to include that date of birth so we're to go again inside our website you can see that we get the date of birth instead but now there's also another thing that we might want to do inside the website which is insert information inside our database or update something or create a table or something so there's a slightly different process there when it comes to doing that it's much more simple but it's slightly different so if I were to copy paste my method here just you know to do it what I can do is I can change the name of it to set users as CMT again just because we are setting information inside the database it is a habit to create either get something or set something and this is when we have to do something that actually interacts with the actual database that we usually tend to use these sort of names here so inside of here I can actually go and just sort of delete all will be happy I'm going to leave everything except for the SQL statement I'm just gonna go and change that one and I want to change this one so that it says instead of selecting insert into users then I want to make sure we include the actual what-do-you-call-it column names of the different columns we have inside our table I actually have them on the side of my notes here so I'm just going to paste them in again it's just users underscore first name users underscore last name users underscore date of birth if you have your own database with different values of course you need to use your values that doesn't make sense to use mine here so afterwards we also need to set some values and these values need to be you know again because we're using prepared statements need to just be placeholders so going in here I'm gonna write question mark comment question mark comment question mark and again the reason we have three different placeholders is because if it were to go inside my database I have three different columns that I need to fill out the users IDs created automatically because it's Auto incremented so we don't need to touch that so going inside our code again what I'm going to do so I'm gonna go down to the next line and just like before I am going to go ahead and prepare the SQL statement I'm just gonna go and do that like so and then we just simply need to go ahead and execute so again copy paste and just need to fill in the information now this time we do also need to include not just the last name and a first name but also a date of birth because you have three pieces of data I'm just gonna go to make sure I include that inside my execute so with DES if I were to go inside my index page and say I instead of doing this want to run a set users statements I need to make sure we include a third parameter let's go ahead and call this next guy John Doe because that's the usual name you pick when you don't know what to choose we're gonna go and set a date of birth it's going to say 1984 and let's just save o3o to save this one now what is going to happen now is as you can see if i refresh my database we only have three entries in here we only have three rows if I were to go inside my website now refresh it you can see we don't have any information in here anymore but if I were to go inside my database and refresh you can see that now we have John Doe so now I inserted that information inside my inside my browser here or inside my database it's cold so this is how we can fetch stuff and how to update the database again it is the exact same process if I were to say I want to update something inside the database or create a table or something it's the same process here I create an SQL statements you connects to the database and prepare the SQL statements and then you execute it filling in the parameters again this is only needed if you do actually need to get user input which in some cases you don't need to so in that case you don't need to use prepared statements but this is how we can interact with the database in the next episode I'm going to take this exact same information and I'm going to apply the MVC model to it which is going to be something that a lot of people requested because they couldn't quite see how the MVC model could be used to gather information like this so we're going to actually apply that and I'm going to try and explain it as well as I can so so with that thanks for watching and I'll see you in the next episode [Music] [Music] [Music]

Channel: Dani Krossing

Views: 67,227

Keywords: Querying A Database Using OOP PHP, query a database using pdo, select from database using pdo, insert from database using pdo, query database using pdo in oop php, php tutorial, php, php classes, load classes automatically in php, use classes in php, link to classes in php, object oriented programming, object oriented php, object oriented php tutorial, oop php database, php pdo, php pdo connection, prepared statements, php prepared statements, Prepared Statements Using PDO

Id: T41SMNgyRrc

Length: 19min 59sec (1199 seconds)

Published: Sun Dec 01 2019