How to connect to MySQL database using PDO

welcome to today's lesson now in this lesson what i'd like to do is to show you how to connect to a mysql database using the pdo extension as you can see in front of me here i have visual studio code got an index.php page currently blank and in the browser i have two tabs open the first one is just to show the contents of that index.php the second one is phpmyadmin i have a test database created and inside that database i have one table called users and inside that users table two rows so two names sam and peter i'm going to use that to show you what happens when we connect to the database okay well the first thing i need to do is to create the connection so we'll open a php code block and the first thing i need will be to know the host so where is my database stored now for me i am using a local host environment so i can use that localhost keyword there i could also use an ip address if you are using an online environment you'll need to check with your web host provider the next thing i need is the name of my database so database name we'll put that equal to the name of the database which was test the next thing i need will be the username now for phpmyadmin the default username is root the other one i'll need is the password again the default is root for me now again you'll need to check these with your web host provider or if you're using a different system username and password might be different i know on some older versions of phpmyadmin the password was actually left blank the next thing we need is the data source name so dsn and that is equal to the following it's just a string and inside my string the first thing i need is the name of the database driver that is my sql then i need to specify the host which is equal to that variable host at the top and semicolon and then what is the database name and i've assigned that to a variable again which is database name now all that's left to do is to actually create that connection so i will create a database connection and we'll put that equal to a new pdo so this is the pdo class and inside here is what i need to pass along to that class so my arguments i need the dsn i need my username and i need my password and that is all we need to create that pdo connection so if i go across to the browser we'll go across to here and refresh we won't actually see anything on the screen now there could be an error we could have a look at the logs to see if anything's going on in my case here i know that has actually connected but what if we did get an error how can we handle that well the first thing we need to do really is to use a try catch block so we're going to do a try something and we're going to catch any of the exceptions or errors in this catch block and what are we going to try well we're going to try and connect so i'll copy that or command x and pop it in there just close this up a little bit so we're going to try and connect to the database now in this catch block what i would like to do is to catch any pdo exceptions and store those in that variable error here and what it'd be nice to do is to actually display a little message if we are successful in connecting to the database so echo connection successful and obviously if we get any errors we'll echo a little message here so we'll just echo out any errors to echo the error here let's go across to the browser we'll do a quick refresh and there we go we've got connection successful well what if i get an error so let me just change the username to something else like root2 go across and refresh and then we get that pdo exception access denied for user route 2 and a whole load of other information there now ideally we don't want to display all of that information so what we can do is we can actually operate on this and we can say get message we'll go across we'll refresh and that's a little bit easier to read so access denied for user root 2. the other thing we could do instead of saying get message we said could say get code and i would get the error code one zero four five now if you think about it what i could do with those error codes is i could do different tests so if the error code is one zero four five then i could display a specific message so we could set up different error messages for different error codes the last thing i would like to look at are error modes so what i can do here underneath this database connection is i can take my database connection i can operate on that and i can set an attribute and what the attribute i want to set is the error mode so the pdo and it's att are attribute error mode and i'm going to set that to one of three things it's either going to be an exception it's either going to be a warning or silent and i'll show you the way the three of them work so pdo [Music] and this one is the error mode and what am i setting there in mode two well by default it's already set at exception so we'll pop that in and just finish that line off with a semicolon in here now to show you the way this works what i'll do is i will connect to the database i'll go to the users table and retrieve data from the users and just display them on the screen let me just make a little bit more space here so we'll just close this down we'll scroll up a little bit here maybe just zoom in a little bit for you and in here what i would like to do is to set up a little sql statement here so what i want to do is to select star so select everything from users i suppose that could just like the name i suppose i could say in here select name from users we might as well select everything from users now what i want to do next is to take my database connection and i want to run a query on that so query and inside the round brackets what is the query what is that it's that sql statement now once i've run that query i want to store the results as users so i should get a list of users returned and then what i want to do is to loop through that and display the username so for each something i'm going to do something and for each what well for each of those users as a single user i'm going to take each of those single users and i'm going to echo out the user square brackets semicolon and inside here i could say well give me the user name or the user id i could do it as 0 that would give me the id one would give me the name so that's to do with the position in the array i'll just use the name of the field which is nem now just so i can see each of those users on a separate lane let's just do this as an unordered list so list item in here and quotes around that quotes around that we'll grab that user from there and just pop it in the middle here so that will just give us a wrapped username in a list item let's go across the browser we shall refresh we've still got that error so i just need to fix up here that username here so get rid of that go across and refresh connection successful and we have the two names returned well what if we had an error in the actual table name here let's say users 2 we'll go back refresh and we get a different code here we could display the full message so let's take off this go back and refresh and then we've got this horrible long complicated message that we really wouldn't want to be displayed on the screen obviously i can say get message we get something that's a little bit easier to read but the whole idea of this error mode exception and error mode silent and error mode warning is to do different things now currently we have the error mode set to error mode exception which means that the script will stop working from this point onwards we have the error here which means none of this will actually run now if i change this to warnings let me get rid of this i will change it to warning we'll go back we shall refresh we've got the connection is successful obviously we still have an error here but what we aren't seeing is the error that's generated from trying to do this so let me show i'll bring in the error logs so here we go we've got a php error log and in here we can see we have that the users table doesn't exist and in here we've then got this invalid argument on the four each so we've got two little warnings displayed here now let me just actually get rid of that i'll just delete that and show you what happens when i put it as an exception if i go back to here and do exception which i'll just undo that and we'll go back and we shall refresh on here you can see we've got the base table not found here but we don't get anything in the error logs now the next thing i can do is to change this to silent so let me just put in silent let's go back we shall refresh it says connection successful let's have a look at the error message this time now this time it hasn't said anything about the error that's generated here it's remained silent about that particular error however we do get an error here a normal php error because we're trying to use users within this for each loop and obviously that doesn't exist so the three error modes first one is the silent error mode this error here will not be picked up in the logs and we won't see anything on the screen and the script will continue obviously we'll get an error here at just a normal php error here the second one is the warning now for the warning we will get a log entry but the script will continue we'll also get a little normal php warning or a little error here as normal and nothing will appear on the screen and the third one is the error mode exception nothing will appear in the logs and the script will stop at this point here i hope you found today's lesson useful and that you will now go off and try to connect to your own mysql databases and that you'll use those attributes those error modes to help you in identifying what potentially is going wrong with your scripts to check your logs and to use those get messages and get codes to maybe even personalize things a little bit more

Channel: Patrick Morrow

Views: 549

Keywords: php for beginners, learn to code, php, coding, coding.academy, patrick morrow, pdo, mysql, database, connect to mysql using pdo

Id: lo1eZvwrhGY

Length: 12min 37sec (757 seconds)

Published: Thu Sep 30 2021