Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In the last movie, we decided that the database API that we will use, is the procedural version of MySQL. Now we're ready to learn how to use PHP and MySQLi to connect to our database. There are five basic steps for database interaction in PHP. First we create a database connection. That's a bit like picking up the telephone to call somebody. Then, once we've got the database on the line, we're ready to start issuing commands to our database. So we perform our queries, those can be Select statements, they can be Insert statements, they can be Delete, all but MySQL that we just learned. That would be in step number 2.
If we're doing a Select then it would, might return data to us. And we would use that return data. We could add there output it to the screen. To the browsers. So the user could see it. Or you might store it in an array. Or in a variable. Do something with it. The whole point of querying the database is to get back the data. And then we want to do something with that data. Once we're done, then we can release the returned data. That's essentially telling PHP look, I'm done with this now, you can free up the memory that was used to hold that query. So you brought back a thousand records to me. I got what I needed, now you can free up those thousand records.
You don't have to store them in memory anymore. It's always a good idea to do. And then last, is close the database connection. That's a bit like hanging up the phone. We're all done at that point. Now, you're always going to use these five steps. Steps one and five should only happen once per PHP script. Steps two through four, you may repeat many times. There might be lots of different queries that you want to make while you've got that connection open. Which you typically want to create the database connection at the start, do all your querying and using all the data. Release data whenever you can and at the very end, you'll close the database connection.
Say alright, I'm all done. I'll call you back next time when I need something else. And when the user loads a new page, then we would open up a new database connection. Do whatever business we need to do, and hang up at the end. And we're going to learn to perform all five of these steps, but we're going to start out by focusing on steps one and five. Opening the connection, and closing the connection. And the PHP functions that we're going to use to do that, the part of the MySQL API are, mysqli_connect, mysqli_connect and then error number. That's the _errno. And then _error and then mysqli_close and so as you can guess _errno and _error are going to be used to spine connection errors that occur. And then connect and close are what we're going to use to connect and close. Lets see how to use that.
So, to start with lets open basic .html and lets do a Save As. And we're going to call this databases.php and you can keep this for reference to know how to connect a database. We're going to do all five steps right here in this databases.php file and to start with, we're going to do connect to the database. Now I'm going to do it above everything else because it's the very first thing I want to do. I know I'm going to need the database for this page. Let's go ahead first thing. Let's connect to it and then if there's a problem, we can go ahead and stop rendering the page and do something else at that point.
Rather than continue on. So not even going to do any of the HTML til I've got my database connection open. So let's look at the syntax here. Here's the key part, mysqli_connet and then we got some arguments. Now you could drop the strings directly into this. I think most people probably do. I've broken them out as separate variables, just so that you can see what goes in each place. So that you can see the variable as an indicator of what's there. The first thing is the host that we're using. So, if that could be either IP address, it might be a domain, Google.com, lynda.com, that would be a domain. And then, we've got our user name.
So, that's going to be the user that we connect to the database with. We created a new MySQL user called widget_cms and then the password that you would use. So, whatever you use for your password for that database and then the database name. The name that we gave our database is the last thing. Now that last argument being the database name is a key difference from the old MySQL interface. So if you're used to that older version for any reason, notice that this is different. We used to have a separate select database command that we had to issue. But one of the improvements in MySQLi is that we could just tell it what database we wanted right here. We don't have to go into MySQL and then switch to that database, we can go directly there.
Now once we call mysql_connect, the value that it returns is what we're assigning to the variable connection. That value is what's referred to as a handle for the connection. And it's what we're going to use throughout the rest of the page whenever we need to refer to this connection to the database that we created. You can also assign it to something else. It's very common for people to call it db or mysql or mysqli or just conn. You can really call it whatever you want. I'm going to call it connection just so that it's really clear for now. Later, you might start seeing me short hand it to something like db and that's the connection to the database.
Okay. So, at this point, if we run our page it will make a connection to the database, but, we need to check and see whether that connection was successful. We don't know whether it was or not and the way to do that is to use those Mysqli_connect_errorno and error to find out. So here is an example of how you can do that. If MySQL_connect_errorno, or if there is an error number, then we know that there was a problem. And at that point, we can say, well, the database connection failed. This checks for any error that occurred in the last transaction. If there's an error sitting there, at this point, we know that it's from whatever we just did.
And then die. That's a new bit of PHP, but it basically says just give up. It's like saying exit or break. Basically just says forget all other PHP, and completely and totally, fatally quit. So it gets out of there. And it's going to die with this message, database connection failed, and then I'm going to display the actual error using the error version. So mysqli_connect, and then error and then in parenthesis after it, I'll put the error number. Just so that we see that as well. So that's the message that it will die with. I'm just concatenating those strings together. So that'll then tell us did it succeed or not.
Errno, either's going to be equal to the error number that's there or zero. It's better than using MySQL_connect_error. 'Cuz connect_error returns an empty string if there's not a problem. It either returns a string with the error name, or an empty string. So it's a little bit cleaner to write it this way. And this how most people do it. And then the very last step is that we want to close our database connection. That's the last thing that we want to do. So we test that the connection occurs. And then at the end of our script. Let's just say all the way down here at the very bottom of the page. We're going to close the connection. Now, we probably could close it earlier.
We might be able to close it up in here. But maybe we want to make other database calls going on throughout our body, that's certainly possible. So I think it's good to go ahead and close it as the very last thing. So that's what I'm going to do here with mysql_close. And again, using that connection to refer to the handle for that connection that I make so that it knows what to close. Now, in truth, you could probably leave off connection here and it would assume it. And know what you were talking about since there was only 1 open connection. But it's always a good programming practice to make it explicit. So let's just try it and see how this works. Make this page called databases. And let's go into Firefox and for me that the page that's going to be at local host and then ~kevinskoglund/sandbox/databases.php.
Okay, I didn't get anything, but that's okay. I didn't get an error. Let's try it just by changing the connection value here. Let's change my user. I'll just take out a letter there for my user name. Let's try and reload the page. Oops, warning, access denied for... Database connection failed. Access to denied for this user. See my message? 1045 was the error number. So that's the kind of thing we get if we have an error. If there is a problem, this is the big, nasty message that you get. Let's go back and change it. We'll save the page, come back to the browser, and reload it and it works successfully.
So, we're now making a successful connection to our database and we're now closing the connection at the end, but that's not really useful. We need to actually execute some MySQL. So, let's see how to retrieve data from MySQL in the next movie.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 104901 Viewers
56 Video lessons · 116749 Viewers
71 Video lessons · 85976 Viewers
131 Video lessons · 41117 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.