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.

.medrectangle-1-multi-102{border:none!important;display:block!important;float:none!important;line-height:0;margin-bottom:2px!important;margin-left:0!important;margin-right:0!important;margin-top:2px!important;min-height:250px;min-width:300px;padding:0;text-align:center!important}