Re: How to Connect Remote MySql Using PHP

[James Cobban]

I will give you an easy cookbook approach to this.

Since multiple PHP pages will all be accessing the database you should put the identification of your web server in a separate file that they will each imbed. For example create a file "db.inc" containing the following:

<?php
$hostname = "mysqlserver.com"; // DNS name or IP address of MySQL server
$databaseName = "mydbname";
$username = "myuserid"; // MySQL userid
$password = "mypasswd"; // MySQL password

// the URL of the database for MDB2
$dsn = array(
'phptype' => 'mysql',
'username' => $username,
'password' => $password,
'hostspec' => $hostname,
'database' => $databaseName,
);

$options = array(
'debug' => 2,
'portability' => MDB2_PORTABILITY_ALL,
);

?>

Because this file should not be accessible to clients, either place it in a directory that is not part of the web site hierarchy or that is blocked by .htaccess specifications. In my case I put it in a file directly under my user home directory on the server.

A PHP page then contains the following:

<?php
require_once "MDB2.php";
require "/home/youruid/includes/db.inc";

// open a connection to the database
$connection =& MDB2::connect($dsn, $options);
if (PEAR::isError($connection))
{ // error establishing connection
die($connection->getMessage());
} // error establishing connection

...

?>

In this example I am using PEAR MDB2.

You will then need to authorize access to the MySQL server from the web server node. That is the DNS name of the web server must be associated with the userid with a GRANT statement. For example:

GRANT ALL ON *.* TO 'myuserid'@'webserver.com';

In practice this sort of administration is frequently handled by a web interface provided by your ISP.