How to connect to a MySQL database with PHP

From Web Services Wiki

Jump to: navigation, search

Contents

Problem

You want to add database capabilities to your PHP application and connect to Stanford's MySQL database.

Solution

Register for the MySQL service

Register your group, department, or service website for MySQL access. Move on to the next step once you have acquired the name of your database and your account information. Please note that at this time Stanford does not support MySQL accounts for individual users.

Create a PHP script to connect to the database

It is recommended that this code be kept in its own file and included on all scripts that need access to the database. It should not be made directly web-accessible. Store this file outside of the web root.

// Two options for connecting to the database:
 
define('HOST_DIRECT', 'mysql-user.stanford.edu'); // Standard connection
                                                  // Only username and password are encrypted
 
define('HOST_STUNNEL', '127.0.0.1');    // Secure connection, slower performance
                                        // All data is encrypted
                                        // Use '127.0.0.1' and not 'localhost'
 
define('DB_HOST', HOST_DIRECT);         // Choose HOST_DIRECT or HOST_STUNNEL, depending on your application's requirements
 
define('DB_USER', 'mysql_username');    // MySQL account username
define('DB_PASS', 'mysql_password');    // MySQL account password
define('DB_NAME', 'mysql_db_name');     // Name of database
 
// Connect to the database
$db = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
 
if(!$db) {
  // Handle error
  echo "<p>Unable to connect to database</p>";
}

Include code that connects to the database

Save the code above in its own file (such as database.php) and include it on each script that needs database access.

require_once("../config/database.php");
 
// Database connection object created in database.php is called $db
// $db equals FALSE when a connection could not be established

Discussion

Prevent others from accessing your database credentials

Since the MySQL credentials are stored in plain text in this example, we strongly urge you to store the database connection code in a non-web accessible location and use AFS ACLs to protect the directory it is in.

Visit AFS at Stanford for more information about using AFS ACLs. Read How to protect your cgi-bin directory on AFS to see an example of how to prevent others from accessing your files.

Deciding between stunnel and standard connection

Stunnel is a GNU program that encrypts arbitrary TCP connections inside Secure Sockets Layer (SSL). Applications which handle highly private information may use this connection method to encrypt all traffic. For most applications, a direct connection is sufficient and encouraged, as it is less resource-intensive. Using either method, no traffic that connects to the database from your application passes through the web, and login credentials are always encrypted.

phpMyAdmin

  • phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the web. You may find it useful in administering and debugging your application.

References

Personal tools