PHP - PDO Prepared Statements

hello and welcome back to PHP basics my name is Shaun up to this point in my journey of web development I've relied on the MySQL I class for most of my projects however over the last six months or so I found overwhelming evidence that PDO is simply the better way to go PDO has more functionality than MySQL I and it's scalable to other database platforms like Postgres it's really just becoming the industry standard so in this video I'm going to show you how to connect a PDO and how to query your database using prepared statements so let's just jump right in so I have a folder called PDO and in that folder I have my index file and I've also got a DVI Inc dot PHP file I've got both of those pulled up here in notepad plus plus let's take a look at the database real quick I've got a database called test and I've got a table called quotes with a name and then a favorite quote here and this is what we're going to use for example all right so to connect to PDO let's just put in our PHP tags here and it's actually really straightforward and simple I'm gonna create a data a variable called DB and that's gonna equal new PDO and then the first parameter is actually a string and we're going to specify the driver that we're using which in this case is MySQL ax the host is going to be localhost the DB name is going to equal test and then I'm also going to specify the character set which is going to equal utf-8 we can close that string and then put a comma the next parameter is our username and then our password which for me is nothing all right so I'm going to go ahead and in my index page I'm just going to include that file so I'll say include DB Inc dot PHP alright so if I come here localhost / PDO alright so I don't have any errors displaying on the screen which is a good thing however if I were to go in and mess up my database name if i refresh this we're going to get this very long exception here and this is very bad because it's acts showing under the constructor our username and our password and a part of our server name which is very very bad so what we need to do is find a way to handle this exception just a little bit differently and only display the exception itself which is it's an unknown database okay so in order to do that we're actually gonna change this up just a little bit I'm gonna copy this and delete it and we're going to use a try-catch method so I'm gonna say try and I'm gonna put in my DB there my DB connection and then catch we're going to use PDO exception and we're going to assign that to a variable called e for error and now basically what it's going to try to do is make the connection to the database and if it can't it's going to grab that exception and we're just going to display the message of whatever is assigned to this variable here so we can say died if it's not executed successfully and we'll just say connection error and then concatenate this will just say e get message all right so now if we refresh the page it's only going to show us the part of the message that we're looking for which is that it's an unknown database okay so this is it this is the only code that you really need to use when connecting to PDO now if you read the official documentation from php.net it's going to give information about setting different attributes on how to handle the exceptions and setting the error mode the thing is whenever it throws an exception the PD Ike or the PDO constructor will automatically throw this exception here and assign it so it's not necessary to do that we'll always get the same results anyway so I'm gonna go ahead and save that and now let's start looking at how we can query the database with prepared statements and PDO alright so now that I've included the file I want to start preparing that statement so I'm gonna create a variable just call s TM T for statement and that's going to equal a new PDO so I'm just gonna say DB and prepare and I'll simply say select all from quotes and then at this point all we have to do is execute this command so we'll say execute now at this point nothing's going to happen because we've not told it to display anything on the page however if we wanted to count the number of rows returned as opposed to doing num rows like you may be used to doing with MySQL I will just echo St MT row count which will display the same thing so I had two records in my database it should show two records in on my page as soon as I correct my invalid database name let's save that and refresh and here we can see it's actually set to two okay so let's go back and actually display this data on the page all right so we'll get rid of this and I'll just say while row equals s TMT fetch which is a little bit different than the fetch a sock that you're used to using with MySQL I and now everything else from here is pretty much the same so we'll grab the name for each row and that's going to equal row name and then the quote itself is going to be equal to row fav quote all right so now all we have to do is echo this out so we can just say echo bold italicized quote and we'll do a line break here and then the name and then we'll do a paragraph break all right so now if we show this on the page we should see don't cry because it's over smile because it happened by Shawn and then if you're happy and you know it it's your meds which is by Chang changed just a weird dude yeah he smokes pot anyway so there are the records from our database alright so now let's start throwing some conditions in here so we're gonna say we just want to show any records where the name equals Shawn so typically we would do where name equals Shawn and you can do this but the idea behind prepared statements is to avoid passing variables directly in the query itself so we're just going to put a placeholder in for in and we'll just say limit one and below that we're going to do STM t bind Ram and for the placeholder we're going to replace n with Shawn now watch what happens whenever I try to execute this it's gonna say that it can't pass parameter two by reference and that's because you can't pass a string as a variable with prepared statements so what I have to do is come up here and I'll create a variable called name and it's just going to equal Shawn and then I'll replace my string here with Shawn and then we'll refresh the page and let me see what I did wrong obviously I typed the wrong variable name let's try this again alright so now it's only going to show the quote put in by Shawn now if I wanted to do a wild card so typically I would do a percent sign around my variable name right so if I were to do something like this it's not going to show me anything what I actually have to do is put that inside of the variable itself so let me do this and obviously this is only going to show Shawn but I'll go ahead and refresh my page here and change my condition to like alright so we can see that that showed Shawn and now if I just change this to the letter A then it should show Shawn and Chang let me remove my limit one alright so that's how you pass parameters and variables through prepared statements so now let's talk about sending information to the database as well so instead of selecting I'm going to say let's just hold on tight real quick let's do name equals John and quote equals never try never fail all right so this time we're going to do insert into what was that called quotes name fav quote values in for name and Q for quote alright so now I have to bind a parameter for Q okay so what's gonna happen is it's going to execute this and if I were to do something like if s TMT execute equals true then display a message well by then it's too late so what we have to do is actually pass the execution in an if statement anyway so I'll say if and then we'll go ahead and execute that and then we'll say echo success and then else echo failure all right so now whenever i refresh my page it's going to attempt to insert this record into the database and it shows success if I look back over here then I can verify that that was added and it was so this should provide a basic understanding of how PDO works and how to send queries to and from your database using simple prepared statements

Channel: phpBasics

Views: 10,071

Keywords: php, pdo, mysqli, prepared statement

Id: 4kQqC3M3QXs

Length: 9min 49sec (589 seconds)

Published: Thu Mar 21 2019