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.

 

Opening a Connection to a MySQL database

PHP has a lot of inbuilt functions you can use to manipulate databases. In PHP version 5, a lot more were added as well! Here, we'll stay with the inbuilt functions for versions earlier than PHP 5. But if you have version 5, it's well worth researching the newer database functions. A good place to start is php.net. To open our Address Book database, we'll use the following inbuilt functions:

mysql_connect()
mysql_select_db()
mysql_close()

The approached we'll take has three steps:

  1. Open a connection to MySQL itself
  2. Specify the database we want to open
  3. Close the connection

Let's do Step 1 on the list.

 

Step 1 - Open a connection to MySQL

The first job is to actually connect to MySQL. As it's name suggests, mysql_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.

<?PHP

$user_name = "root";
$password = "";
$database = "addressbook";
$server = "127.0.0.1";

mysql_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. If you don't, you can download all the files here.).

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.

Hopefully, you won't have any errors. But the line that connects to MySQL is this:

mysql_connect($server, $user_name, $password);

So you type the name of the function first ( mysql_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:

mysql_connect( '127.0.0.1', 'root', '' );

Or as variables, like we did at first:

$user_name = "root";
$password = "";
$server = "127.0.0.1";

mysql_connect($server, $user_name, $password);

And that's all you need to get you connected to MySQL. But we haven't connected to the database yet. That's Step 2 on our list.

 

Step 2 - Specify the database you want to open

In our 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):

$user_name = "root";
$password = "";
$database = "addressbook";
$server = "127.0.0.1";

mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database);

print "Connection to the Server opened";

You use the mysql_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. You can use some logic to test if the database was found. Change the last two lines of your code to this:

$db_found = mysql_select_db($database);

if ($db_found) {

print "Database Found";

}
else {

print "Database NOT Found";

}

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.

But there's another option you can use for mysql_select_db - something called a resource link identifier. It's just a file handle that you used in an earlier section (opening text files). You use it like this:

$user_name = "root";
$password = "";
$database = "addressbook";
$server = "127.0.0.1";

$db_handle = mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

print "Database Found " . $db_handle;

}
else {

print "Database NOT Found " . $db_handle;

}

So when we connect to the database, we're now using this:

$db_handle = mysql_connect($server, $user_name, $password);

It's just the same as before, except we're returning a value from the mysql_connect function, and putting it into a variable called $db_handle. When we connect to the database, we can use this file handle:

$db_found = mysql_select_db($database, $db_handle);

The resource link identifier (file handle) goes after the name of the database you want to open. You can then use this file handle to refer to your database connection.

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:

mysql_close( $db_handle );

Otherwise, you don't need to bother. It's recommended that you take the file handle approach, though. That's what we'll be doing from now on.

So, we'll add a line to close our connection. Here what your code should now look like:

<?PHP

$user_name = "root";
$password = "";
$database = "addressbook";
$server = "127.0.0.1";

$db_handle = mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

print "Database Found ";
mysql_close($db_handle);

}
else {

print "Database NOT 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.