MySQL Connection

[tangoforce]

Hi

I've been using this code I wrote for a couple of years now and it has been faultless. It uses MySQL not MySQLI so it is a bit on the old side but I figured that many of you may find this useful and adapt as needed even if you only use the principle of it and not the actual code.

First let me explain why I created this. I found that everytime I'd upload my project to the web, I'd forget to change the mysql login details so I'd have to re-edit the mysql code, upload, change it back for my localhost/wamp.

So, I figured that by making use of the $_SERVER['HTTP_HOST'] variable, I could use a technique similar to phpmyadmins config.inc.php file. The code below allows you to configure multiple mysql server connections. When you edit your project locally and then upload everything, you don't need to worry about reconfiguring the mysql connection details. If you have 6 sites, you can share this one file with all of them OR have a seperate version for each configured with the local and remote details. Just be sure not to share the file with your mysql details with anyone else that shouldn't have them.

To connect to mysql in your script you simply do this:

PHP Code:

//Require the file
require_once('mysql.php');

//Open connection and select DB
$MySQL = run_mysql($_SERVER['HTTP_HOST']);

//Or
run_mysql($_SERVER['HTTP_HOST']);

//Need to debug this mysql connection? - Set the second parameter to true.
run_mysql($_SERVER['HTTP_HOST'], true); 


That will allow your script to automatically select the correct mysql login details and then connect and select the database.

Below is the actual code, note that each server configuration can have multiple network names that it will recognise - mysql.php:

PHP Code:

<?php
function load_mysql()
   {
   //Set Mysql connections here:
   //MySQL Server 1 - Localhost
   $Mysql['Name'] = array('127.0.0.1', 'localhost', '<computer_name>', '192.168.0.1');
   $Mysql['Host'] = 'localhost';
   $Mysql['User'] = 'root';
   $Mysql['Pass'] = 'password';
   $Mysql['DB'] = 'your_database_name';
   $Connections[] = $Mysql;
   
   //MySQL Server 2 - The website
   $Mysql['Name'] = array('www.your-site.com', 'yoursite.com', 'sub.your-site.com');
   $Mysql['Host'] = 'localhost';
   $Mysql['User'] = 'your-site-mysql-user';
   $Mysql['Pass'] = 'password2';
   $Mysql['DB'] = 'db_name';
   $Connections[] = $Mysql;

   return $Connections;
   }

function get_mysql($Name)
   {
   $Connections = load_mysql();

   foreach($Connections as $Key => $MySql)
      {
      if (is_array($MySql['Name']))
         {
         if (($Search = array_search($Name, $MySql['Name'])) !== false)
            {
            return $MySql;
            }
         }
      else
         {
         if (strtolower($Name) == strtolower($MySql['Name']))
            {
            return $MySql;
            }
         }
      }
      
    die("Database connection for $Name not defined / established.");
   }

function run_mysql($Name, $Debug = false)
   {
   $Connection = get_mysql($Name);

   If ($Connection != NULL)
      {
      If ($SQL = @mysql_connect($Connection['Host'], $Connection['User'], $Connection['Pass']))
         {
         //MySQL Connection Success
         $Output = "Connected to $Connection[Host]<br>\n";

         If ($Connection['DB'] != '')
            {
            $Output .= "Database $Connection[DB] supplied<br>\n";

            If (@mysql_select_db($Connection['DB']))
               {
               //Table Selection Success
               $Output .= "Selected $Connection[DB]<br>\n";
               }
            else
               {
               //Table Selection Failure
               $Output .= "Database not selected: " .mysql_error() ."<br>\n";
               }
            }
         else
            {
            $Output .= "Database not supplied<br>\n";
            }
         }
      else
         {
         //MySQL Connection Failure
         $Output = "Not connected to $Connection[Host]: " .mysql_error() ."<br>\n";
         }
      }

   if ($Debug)
      {
      print $Output;
      }
         
   return $SQL;
   }
?>