Home and Learn: PHP Programming Course
Open and Close a connection to a database with PHP
In this section, you'll see how to manipulate the simple Address Book database you've just created. Using PHP code, you'll first open the database. Once the database is open, you can then read its contents. You'll also need to know how to add new records, and delete records. First, though, a database has to be opened, before you can do anything with it.
Open a connection to a MySQL Database
The first job is to actually connect to MySQL. As it's name suggests, mysqli_connect( ) does exactly that. Here's the code we're going to be using. But this is just to get your started. It is recommended that you don't dash off and use this on the internet! This is for learning purposes only. We'll use a different way to connect, shortly.
<?PHP
$user_name = "root";
$password = "";
$database = "addressbook";
$server = "127.0.0.1";
mysqli_connect($server, $user_name, $password);
print "Connection to the Server opened";
?>
Save your work and try it out on your server (this assumes that you have the Address Book database we created earlier, and that it is in the data folder of MySQL.
The first four lines are just setting up variables, and putting something in them:
$user_name = "root";
$password = "";
$database = "addressbook";
$server = "127.0.0.1";
The username we're trying here is "root" and the password is blank. These are the MySQL defaults. You don't need to change these, in most cases. (This is purely for testing on a local machine. You do need to change the MySQL defaults if you're running your own server connected to the internet.)
Hopefully, you won't have any errors. But the line that connects to MySQL is this:
mysqli_connect($server, $user_name, $password);
So you type the name of the function first ( mysqli_connect ), followed by the round brackets. In between the round brackets, you need three things: the name of your server, your MySQL username, and your MySQL password. These can be entered directly, like this:
mysqli_connect( '127.0.0.1', 'root', '' );
Or as variables, like we did at first:
$user_name = "root";
$password = "";
$server = "127.0.0.1";
mysqli_connect($server, $user_name, $password);
Try it yourself and you should see the message "Connection to the Server opened" printed out.
There is, however, a problem with this - your username and password details
are being typed in plain text in the PHP file. That means a hacker could gain
access to this file and read your details.
What'll do instead is to place this sensitive information in a separate file,
and use the PHP inbuilt functions require and define.
Require and Define
The inbuilt PHP function define is used to set up constants. Here's an example:
define('NAME_OF_CAT', 'Tiddles');
In between the round brackets of define you first need the name of your constant, NAME_OF_CAT, in the code above. After a comma, you type the value that you want to place in your constant. Now you can do things like:
if (NAME_OF_CAT == 'Bob') {
Print "Wrong cat!"
}
NOTE: you'd get an error if you set up a constant like this:
define('NAME-OF-CAT', 'Tiddles');
The error is because you can't use hyphens in your constant names.
So, instead of hard-coding a user name and password in every PHP file, we can define them in a separate file.
Create a new PHP file. Call it configure.php (though you can call it something else, if you prefer.) Add the following as the code:
<?PHP
define('DB_SERVER', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', 'password');
?>
We've set up three constants here: DB_SERVER, DB_USER, and DB_PASS. Notice that this last one now has a password set. The values root and password are the ones you use to get you in to phpMyAdmin. From this screen:
By default, the password is normally blank. But you can (and should) set up a password here. Click the GO button to see the following screen:
In the General settings sections at the top, click on change password. Enter a new password for yourself. Be sure to remember your password, as it's hard to recover. For testing purposes on your own computer, you can type something simple for your password. We've gone for the obvious (and easily hackable) password. Needless to say, you should change this to something a little more robust, if your database is going into production!
But that's why define for us says password, and not left as a blank string:
define('DB_PASS', 'password');
Change password to whatever you typed as your password in the phpMyAdmin screen above.
Now that you have set up some constants, it's time to save the file. It's highly recommended that you don't save your sensitive information in the root folder of your server. This is usually the www folder, if you have some webspace on the internet. Save the file outside of the www folder, one folder up should be OK. That way, hackers will find it very difficult to gain access to it. In the image below, you can see the www folder, and the configure.php file:
So the configure file is not inside of www.
The require keyword in PHP is similar to include, in that it is used to include another file in your code. The difference is that require will halt the script when it detects an error and give you an E_COMPILE_ERROR warning. If you use include then the script will continue to execute and just give you an E_WARNING error. You use it like this:
<?PHP
require 'somefile.php';
?>
It's better to put require right at the top, without any blank lines after <?PHP. The blank lines can sometimes generate an error that can drive you crazy trying to solve.
Here's some new PHP code to try out, though:
<?PHP
require '../configure.php';
mysqli_connect(DB_SERVER, DB_USER, DB_PASS );
print "Server found" . "<BR>";
?>
Notice the require line has no round brackets. So we haven't done this:
require('../configure.php');
So we haven't use any round brackets. That's because require is something called a statement, and is not a function. You can use round brackets, if you want, and it will still work. But we've left them out, to avoid any confusion with functions.
Another thing to note is the two dots and a slash at the start. (We've used a forward slash, here, but try a backslash, if you're getting error messages.) The two dots and a slash mean "To find the file, go up one directory from where you are".
Because we've defined our constants in a separate configure file, we can use these between the round brackets of mysqli_connect:
mysqli_connect(DB_SERVER, DB_USER, DB_PASS );
Doing things this way means that we're not hard-coding our details into the PHP connection file. Also, we don't have to keep typing our login details every time we want to connect to the server - just use require at the top of the code instead.
But we haven't connected to the database yet.
Specify the database you want to open
In previous code, we set up a variable with the name of our database:
$database = "addressbook";
We now need to do something with this variable. So add this new line to your code (the new line is in blue text):
<?PHP
require '../configure.php';
$db_handle = mysqli_connect(DB_SERVER, DB_USER, DB_PASS );
print "Server found" . "<BR>";
$database = "addressbook";
$db_found = mysqli_select_db( $db_handle, $database );
if ($db_found) {
print "Database found";
}
else {
print "Database not found";
}
?>
The first new bit of code is this:
$db_handle = mysqli_connect(DB_SERVER, DB_USER, DB_PASS );
We now have mysqli_connect on the right of an equal sign. To the left, we have a new variable, which we have called db_handle. This variable will store a connection to the server.
The next new line is this:
$db_found = mysqli_select_db($db_handle, $database);
You use the mysqli_select_db( ) function to specify which database you
want to open. The function then returns a true/false value. If it finds your
database, a value of true is returned; if your database can't be found
then a value of false is returned. In between round brackets, you need
a connection to the server (db_handle, for us) as well as the database
you want to connect to. We've used an if statement to check the value of $db_found.
A suitable message is printed, depending on whether the database was found or
not. (The two print lines are only for testing purposes.)
Now change the database name from this:
$database = "addressbook";
to something like this:
$database = "addressbook2";
Run your code again, and you should see Database NOT Found printed out (unless you have a database called addressbook2). Change the database name back to addressbook.
Now that we've connected to MySQL, and connected to a database, it's time to close the connection.
Step 3 - Close the connection
Closing a connection to a database is quite easy. If you've used a file handle, as above, you just do this:
mysqli_close( $db_handle );
Otherwise, you don't need to bother. It's recommended that you take the file handle approach, though. Place the close code in the if statement, right after "Database found".
Now that we've got a connection to the database, it's time to look at how you can access the data in the database. We'll do that in the next part.