Connect to MySQL Database

· MySQL, PHP
Authors

Opening a connection to MySQL database from PHP is easy. Just use the mysql_connect() function like this-

<?php
 $dbhost = 'localhost';
 $dbuser = 'root';
 $dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');

$dbname = 'petstore';
 mysql_select_db($dbname);
 ?>

$dbhost is the name of MySQL server. When your webserver is on the same machine with the MySQL server you can use localhost or 127.0.0.1 as the value of $dbhost. The $dbuser and $dbpass are valid MySQL user name and password. For adding a user to MySQL visit this page : MySQL Tutorial

Don’t forget to select a database using mysql_select_db() after connecting to mysql. If no database selected your query to select or update a table will not work.

Sometimes a web host will require you to specify the MySQL server name and port number. For example if the MySQL server name is db.php-mysql-tutorial.com and the port number is 3306 (the default port number for MySQL) then you you can modify the above code to :

<?php
 $dbhost = 'db.php-mysql-tutorial.com:3306';
 $dbuser = 'root';
 $dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

 $dbname = 'petstore';
 mysql_select_db($dbname);
 ?>

It’s a common practice to place the routine of opening a database connection in a separate file. Then everytime you want to open a connection just include the file. Usually the host, user, password and database name are also separated in a configuration file.

An example of config.php that stores the connection configuration and opendb.php that opens the connection are :

Source code : config.phps , opendb.phps

// This is an example of config.php
$dbhost = ‘localhost';
$dbuser = ‘root';
$dbpass = ‘password';
$dbname = ‘phpcake';
?>

[/sourcecode ]

<?php

// This is an example opendb.php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(‘Error connecting to mysql’);
mysql_select_db($dbname);
?>

//So now you can open a connection to mysql like this :

<?php
include ‘config.php';
include ‘opendb.php';

// … do something like insert or select, etc

?>

[/sourcecode ]

Closing the Connection

The connection opened in a script will be closed as soon as the execution of the script ends. But it’s better if you close it explicitly by calling mysql_close() function. You could also put this function call in a file named closedb.php.

Source code : closedb.phps

// an example of closedb.php
// it does nothing but closing
// a mysql database connection

mysql_close($conn);
?>

[/sourcecode ]

Now that you have put the database configuration, opening and closing routines in separate files your PHP script that uses mysql would look something like this :

<?php
include ‘config.php';
include ‘opendb.php';

// … do something like insert or select, etc

include ‘closedb.php';
?>

[/sourcecode ]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: