 Hello, this is Christian. Welcome to episode two of this PHP Stocks application using PDO. Now, let's go and see what our app looks like. Now, previously, this is the one we built. As you can see, we have the layout created. Yeah, I don't like the little thing here on the left side. There's a space here. The buttons are a little bit too not consistent. So let's fix that really quick first. It's a quick fix. I'm pretty sure we can just change the MSS in there. And I realize that this one here should have been outside. Okay, so let's move that outside. That should be outside the main so that it will be left. There's no space on the left. Now, the stocks, let's change the Ally. Let's remove the padding from the Ally. And we'll let the padding be managed by the anchor link. The anchor itself. So put padding here. Maybe like a try 5px and see what that like. And then we'll make it length to be consistent for all of them. I'll just use 100 pixels and see what that looks like. Okay, so let's go over to the browser and see that. So what I did was that basically, if you notice, if you hover this, right, it changes the background color to white. You don't see the text because now I'm hovering over the Ally and not the HD at the anchor tag. So to fix that, you would remove all the padding from the Ally and then you let the anchor tag take over. So you make that fix and you can see that that's good over here. They're now a little bit small now, but we'll change the padding, make it a little bit bigger and change the font size too. You always do this here just quicker. Just make sure everything works. I like my stuff to be nice. Okay, so the padding 5, we'll make it maybe 10. We'll change the font size to be larger. Yeah, larger and then the padding couldn't go a little bit higher, I guess. 15, okay. And the width is 100 and I know. So I'll put them. Oh, you know what? Yep, again, it gets me all the time. You need to change the display to say inline block. Okay, so there we go. Inline block should fix that and maybe the padding was too big. So if you go back to just 5, yeah, 5 should be enough. But the font size, maybe we'll change it back to just large. Well, 150. Yeah, use 150. Okay, so let's do that. We go 150 for the width and then the display needs to say inline block. Okay, so that allows us to control the width and the height of our application. Okay, I think that should solve it. All right, one thing I wanted to just go over really quick. Oh, that's way too big. Sorry. Yeah, back to 100. I want to go over the PDO for a second here because the PDO is a library of extension to the PHP. So if you go over here to php.net and search for just PDO. Okay, and yeah, so the PDO, which is the PHP data objects. Now if you go up a little bit further, one up, the database extensions, you'll see the overall picture here. Okay, so here PDO is one of these abstraction layers. And so down here you have the very specific libraries for each vendor type. So PHP lets you communicate or talk to all these databases here using their own very specific driver. We have been using mostly the MySQL because way back in the 90s when PHP was created, it was open source. And a lot of people were not used to the open source community back then yet. But Linux was open source and then came PHP. And then the MySQL database was open source. And so it was the perfect marriage between Linux and Apache was also open source. And then MySQL and PHP and so on and Pearl too. So that's why you have the bundle of the LAMP stack. It's composed of these open source technologies. And so you see that when you think about PHP, immediately the database system that goes with that is always MySQL. But now as you can see, you can use all kinds of databases including no SQL databases such as MongoDB. So if you are a MongoDB user, this is the library. Now these are very specific. So what that means is if you go to, for example, the Microsoft SQL server, you go here, you see that it has its own set of functions, right? It looks very similar to the MySQL I. Mostly you just basically change the MySQL to MySQL I and so on. Most of these are still the same, but not always. So if you go back to say SQLite 3, you see that it has its own sets of functions, very different, right? And if you know for sure that your program is going to be using a very specific database that you don't intend to change it, then yeah, that's fine, you can go to the very specific one. And that's the advantage of that. Now the PDO is like an operator that talks to about a dozen of these different types of databases. What it does is that instead of going to a very specific vendor, you talk to it through the PDO and then because the PDO is a pretty standard set layer, so it talks to whatever database you connect to. And so what that means is it will use the same functions. As you can see, all these functions will be the same for whichever database you talk to. And the databases it supports at this time is these right down here. So you can see here. So you don't have to go through their specific vendor driver. You just talk to it through the PDO and then that will use the same functions. So when you write your code, you can switch from MySQL server to Oracle to Firebird and so on. And without changing these functions, you just have to change these SQL SQL statements because each server has its own sets of unique statements in addition to the NC standard. But still, you end up with just using the same functions will save you a lot of time. And that's why we want to use PDO for this example. All right, so here you have two very important classes, the PDO class itself and we have a PDO statement class. So we make connections through the PDO class and you can also make executables as well. Here it's executable. You can execute a query. You can also do to prepare and then you also make the query. This one here and execute kind of similar, right? Except the difference is what it returns. So here you said it returns the number of rows affected. Here it returns, it doesn't return that, right? It returns actually a PDO statement. Oops, here it returns a PDO statement object. And so whatever returns you have to kind of decide which one to use. And down here, the PDO statement, these are usually used if you pass parameters to your queries. Like you want to search for a specific ID or find a particular record that has a matching to a certain field of fields. Then you use the PDO statements to do that because you want to, again, you prepare your query. After that, you have to use the statement to bind those parameters, either to a column, to a parameter, to a value, those options, and then you can fetch them back as well. Again, the reason for this is for security reasons, right? You don't want any, what's called the KL injection to take place in your database. And that's really, really dangerous. And what does that mean by PDO? I mean, SQL injection. That's another story. Maybe if you have time, we'll do an example, but that's something we want to avoid. Okay, so all right, let's go back in here and then let's start creating our database. Well, let's go to the config first. I want to put all my database information here in the config file. And again, usually you put that outside of your root directory so that it's secure. For example, we'll just put in here, okay? In the real application, you don't want to put it here. So I'm going to define some constants. The host will be just the local host. And I'm just going to duplicate this, control D a couple of times. I need the, this is going to be the user, the password. And I need a DB for a database. I need a table. So we call it table using stocks or table stock. And then here you have, you need to put a driver, right? We're using a driver. And then the driver here is whatever database you want to use, like Oracle, MySQL server, or MySQL and things like that. So ours is going to be just MySQL. We're using the MySQL driver. We call it stock. This is just the, I think I'll call it her stock, something like that. Phenomenon is taken. We'll check it out. The password will be blank for mine. And then the user, it just root, okay? So let's see. I don't remember if I have it correctly. Let's go to the local host. Then PHP, my admin. Is it running? It must have turned off the, yeah, it turned off the, let's see. I want that to be turned on so we can access the database. Yeah, it was off. Okay. So let's go and try again. All right. So here we go. Yeah, I call it her stocks. Okay. So we have our fields all set up to go. And we are good. Okay. So let's go back. And then now this is done. And then one more thing I want to check. Yeah. Okay. So that's good. Now let's go to the DB file. And this is what we're going to define our class and then our PDO. So let's call it class DB. And inside here, we're going to have a private variable. I'll just call it PDO. It's going to be null at first. And then I have another one for the DSN. All right. This is the DSN for connecting to our database. Just leave that blank for now. And so let's do our constructor. I'm just going to score. And then that will give us a constructor here. We'll do a, we'll set the PDM DSN first. Let's go and create that object really quick. Let me set it together. And we'll do for, well, yeah, both of them is fine. Okay. So when I set the PDO, I'm going to do a connection. Okay. I'm not going to set it here. We're going to call the connection. And then also, well, before we do the connection, we're going to set the PDO, the driver first. So I want to do a set DSN. All right. And yeah, so we set DSN. You can pass in some data here. So initially you want to pass this same information here. So basically this information can be passed to that constructor, just in case. Or you can set it, it doesn't matter, but why not just do it here, right? So we can say driver is equal to driver, the global variable. See the host is host, user is user, the password is pass. And then the, I think that's it, right? The DB. DB is DB. Okay. I'm using the default here. So if you don't provide any of these drivers, then we're going to use the default, which is coming from directly from this list. Okay. And then we're going to pass it to our DSN. For DSN, you need the driver, the host user and the DB. Well, not the user. I'm just the driver, the host and the DB port number and so on, but mainly just all that we need. So I'm going to pass to this setter, the driver and the DB. And I think maybe just those two. Yeah. Well, okay. Let's see. So let's go to our driver function down here. We set the driver. I'm going to pass in, not the DSN, but pass in a driver, pass in the database. Let's call it DB for short. And when you set it, when you set it, I think you need a host too. Yeah. Let's pass in the host as well. Okay. So when you set it, instead of doing this way, I'm going to say, it's going to be the driver. We can coordinate that with the colon. And then the driver would be the DB name. It's equal to, let's just do this with the double quotes. I'm more accustomed to that. Then the DB and then again with the host. And the host would be just the host. Okay. So that's our DSN. And then we're going to return that to the connection. So let's go and write a function here. Function connect. All right. So the connection is going to be going to try. Just make sure we capture all the errors, capture exception, E here. And then we just throw an error. We can just say, something went wrong, could not connect. And echo another friendly message. I said it would be error. So we know what the error is. Okay. So E get message. Okay. So that's done. Now the connection. So now we're going to do this PDO. Okay. Points to equals a new PDO. And then here we have four parameters. As you can see the list here is the DSN. So we're going to get the DSN. We get that. And then we need to put in the user. Yeah. The user, let's put user here. And then this is the password. Let's pass that to this user and password connection. And then password. Okay. And then once we get that, then we check to see if there's any error. So if no error, if PDO has no error, then we are good. That means we can go ahead and, I mean, if this is false, it has an error. Right. Throw a new error. And we just say something like cannot connect. We do that. And then I think that's it. If it's all good, then we are good to go. So that should make the connection. Right. So we pass the DSN, the user and password, and then we can make the connection. And we invoke that automatically when we do a new DB inside that config class. So this purchase amount is done for that one. Now let's go over here and then create a function to, well, maybe we'll, yeah, let's write a function to get all records. We need one for that. We need another function to insert, right? Insert record. And then we'll also need a function to delete. So I'll use the truncate, truncate table. And we need a table name. So you pass that to the table, truncate table function. We'll do that truncation in there. You also need the table to retrieve and the table to insert. Okay. So those two are, I think that's all we need for that one. We insert, you also need the data, right? I'm just doing, you know, as I go. So I think that's what we need for our three main functions. We basically just do those. And then, so let's get a record first, the easier one. The get record is pretty simple. So basically you make the query is select all from the table. Okay. That's all we have to do when I have any other parameters here. So just do that. And then we're just going to return the query, which is going to say this PDO. And we're going to make the query right away. And the PDO is going to call the query. Looks very similar to the mass QLI query. Okay. It will return a statements. And then that's for that. That's it for that one. The truncate is quite simple. And so let's do that first. Truncate is query is going to be truncate the table called table. Okay. Again, the difference between truncate and delete is that you would wipe out the data and you reset the ID field back to one. Okay. If you just do a delete, then the ID field is remained unchanged. So whatever you left off, it's going to start from there. So I want to just basically start from one every time. So again, very similar. Okay. So that is for the truncate and the get record. The insert would be a little bit, take a little bit more effort, but we can do that as well. When certain data, now what data is coming in, remember that data is coming in from the text file, that from the stocks, right? When we read this text file, we're going to read every line, each line comes in, and then we just insert the company name, the stock symbol, the sector, and then the price. We're going to ignore the ID here. This is just for our information here, but we can do that. We don't need to pass in the data. I mean the ID. So we can do it in a couple of ways in here. One is we can have a list of all parameters. This would be like a list of all the keys. So the first one is going to be a null value, the first field, right? Then the second one is going to be the company name. So in this case, if you build a data, I'm going to come in the form of an array where we can get the first element of the data, and then the data of the second element position, or third position, and then data of third, and then this is the data of the price, which is the fourth. That's all the actual variables. All the data you need to pass to the placeholders. So here's the placeholder, it's going to be in here. So the query is going to be insert into the table, and then the values, right? It's going to be here is we put all these parameters. Now you can use two methods. You can use the PDO method using the colon, follow up on the name of the variable name. For example, here would be something like, well the first one is null, so you can put here, it doesn't matter ID, and then followed by the company name like that, right? If you want, that's fine. This has a really good semantic feel to it, because you know exactly what you want. Or you can use the question mark using the minus QLI syntax. So it's shorter, but if you do this way, it just makes you know exactly which one goes which position. So this is similar to the printf function, where you would use a placeholder for that position. So the order here is important. The first value must be whatever this one is, second, and so on. Since I already have this way, I'm going to use the question mark, make my call a bit shorter, and then replace those inside the query, and then the prepare statement when we execute. So we got that, so notice here, right? We don't execute it because when you run it, this is how it's safe to call your code from SQL injection, because it does not execute this right away. It sends the instructions first, and then it sends the data after that. So there's no way for the database to execute if you see these statements here. As you can see, there's nothing, there's no data to insert. So then now we can go ahead and then do a statement prepare. So I'll do here the PDO, prepare, we'll call the prepare, and then I'm going to prepare the query. Once you do that, then we can go ahead and then execute it, and then we're going to return that execution back. So there'll be the statement, and then here just execute. Okay, the whole word execute. This is not the same as the execute in PDO. PDO uses just exec. The statement class uses the execute, okay? And then, so that's a difference to just make sure. I know it a little bit tricky, but just have to get used to it. And you pass in the parameters. Okay, again, you could do it, you could put all these in here if you want manually, but I happen to do it this way. It's just nicer because I know exactly what goes where and your code is cleaner this way. But you could just, you know, copy and paste all these in here to make sure it matches each of these positions here. Okay, so I think that's all we need for this insert statement. All right? So yeah, this is pretty much done for our database. Again, later on when we make the actual query, we'll see if it works. If it doesn't, we'll always come back and fix it. And the last thing we're going to do here is create a config file and then include in here the query. I mean, the database. So let me just put up here. It doesn't matter where, but I like to put it on top here. So it's going to be a new DB. And then we'll not pass any default data to it. I mean, custom data. We'll just use the default here. I think that's it for this one. And so when we make this index over here, we just make sure we, you know, pull the correct formation and so on. So let's just run my code and just make sure everything is still working as before. Okay, make sure there's no error. Yeah, so we are good. Okay, so I'll stop here. And then in the next video, we're going to go and then read a text file and pass it over to the database.