Multiple Mysql Connect Script

W

wxdqz

New Member
I am trying to create a dual mysql connection script, so that I can have two mysql database servers. Because I am running sessions through out the site, the default database with carry the sessions, members information etc. The second mysql server would only be used in sections where required. In my attempts to create a connect script, I am getting many errors and seem to be not getting any closer to solving a solution.

Included is my original script that works for connecting to one mysql server. Which allows for me simple write the following example to query or select from my database.

Example:

$query = DBQuery("SELECT profile_id FROM Members WHERE user_no = '" . addslashes ($Member_Fields ['user_no']) . "' ORDER by primary_id");

This will automatically go to the database server and grab what is required. Now what I am try to achieve is the following, if I write the above example and selected a database it will point to the second server and if no database is enter it will point to the default server.


<?php
// database directories and paths
$DB_Name = "";
$DB_Host = "";
$DB_User = "";
$DB_Password = "";

// connect php to mysql database
$DB_Connected = 0;
$DB_Host_Ptr = 0;
$DB_Database = 0;
$retry_count = 5;

$save_error = error_reporting (4);

while (!$DB_Database) {
if (!$DB_Host_Ptr)
$DB_Host_Ptr = mysql_connect ($DB_Host, $DB_User, $DB_Password);

if ($DB_Host_Ptr)
$DB_Database = mysql_select_db ($DB_Name, $DB_Host_Ptr);

if (!$DB_Database) {
if (--$retry_count)
continue;

if (!$DEBUG_SW) {
mail ($DB_Email_To, $DB_Email_Subject,
"Unable to connect to the $Site_Name Database",
$DB_Email_From . $DB_Email_CC . $DB_Email_Reply);
} else {
echo "<!-- Host: $DB_Host -->\n";
echo "<!-- Name: $DB_Name -->\n";
echo "<!-- User: $DB_User -->\n";
echo "<!-- Password $DB_Password -->\n";
echo "\n";
echo "<!-- Host_Ptr: $DB_Host_Ptr -->\n";
echo "<!-- DB_Ptr: $DB_Database -->\n";
}

// generate html database debugging error
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 TRansitional//EN"
"http://www.w3.org/tr/1999/rec-html401-19991224/loose.dtd">


<head>
<title><?php echo "$Site_Name:" ?></title>
<link rel="STYLESHEET" type="text/css" href=http://www.phpbuilder.com/board/archive/index.php/"/common/styles/common.css">
</head>

<body bgcolor="#ffffff" text="#000000">

<div align="center">
<table border="0" width="100%" cellspacing="1">
<tr><td class="lrgfont">
<font color="#FF0000">
<b>We are currently unable to connect to the <?php echo HtmlSpecialChars ($Site_Name) ?> Database Server</b>
</font>
</td></tr>

<tr><td>
This error has been reported to the site administrator and will be repaired soon.
</td></tr>

<tr><td>
<b>We apologize for any inconveniences this may have caused and hope you return soon!</b>
</td></tr>

<tr><td height="20"><img src="/images/common/pixel.gif" width="1" height="20" border="0" alt=""></td></tr>

<?php if ($DB_Host_Ptr) {
$errno = mysql_errno ($DB_Host_Ptr);
$error = mysql_error ($DB_Host_Ptr); ?>
<tr><td>
Database error <?php echo "$errno: $error\n" ?>
</td></tr>

<tr><td>
Page: <?php echo $PHP_SELF ?>
</td></tr>

<?php }
?>
</table>
</div>

</body>

<?php
exit ();
}
}
error_reporting ($save_error);

// report database errors to administrator
function DBError ($query = "") {
global $DEBUG_SW;
global $PHP_SELF;

global $DB_Host_Ptr;
global $Site_Name;

global $DB_Email_To;
global $DB_Email_Subject;
global $DB_Email_CC;
global $DB_Email_From;
global $DB_Email_Reply;

$errno = mysql_errno ($DB_Host_Ptr);
$error = mysql_error ($DB_Host_Ptr);

if (!$DEBUG_SW) {
mail ($DB_Email_To, $DB_Email_Subject,
"The following error occurred querying the $Site_Name database\n\n" .
"Database Error $errno: $error\n\n" .
"Query: $query\n\n" .
"Page: $PATH_INFO\n\n",
$DB_Email_From . $DB_Email_CC . $DB_Email_Reply);
}
?>

<div align="center">
<table border="0" width="80%" cellspacing="1">
<tr><td class="lrgfont">
<font color="#FF0000">
<b>We are experiencing unexpected problems accessing the <?php echo HtmlSpecialChars ($Site_Name) ?> Database</b>
</font>
</td></tr>

<tr><td height="10"><img src="/images/common/pixel.gif" width="1" height="10" border="0" alt=""></td></tr>

<tr><td>
This error has been reported to the site administrator and will be repaired soon.
</td></tr>

<tr><td>
Database error <?php echo "$errno: $error\n" ?>
</td></tr>

<tr><td height="10"><img src="/images/common/pixel.gif" width="1" height="10" border="0" alt=""></td></tr>

<tr><td>
Query: <?php echo $query ?>
</td></tr>

<tr><td>
Page: <?php echo $PHP_SELF ?>
</td></tr>

<tr><td height="10"><img src="/images/common/pixel.gif" width="1" height="10" border="0" alt=""></td></tr>

<tr><td>
<b>We apologize for any inconvenience this has caused and hope you return soon!</b>
</td></tr>

</table>
</div>
<?php
exit;
}

// free query results function
function DBFreeResult ($query) {
if ($query)
mysql_free_result ($query);
}

// execute database query function
function DBQuery ($query) {
set_time_limit (120);
$result = mysql_query ($query);

if (!$result)
DBError ($query);

return $result;
}

// fetch query number of rows in a query function
function DBNumRows ($query) {
global $DB_Host_Ptr;

$rows = mysql_num_rows ($query);
if (mysql_errno ($DB_Host_Ptr))
DBError ($query);

return $rows;
}

// fetch query number of rows in a query function
function DBGetCount ($query) {
global $DB_Host_Ptr;

$result = DBQuery ($query);
$rows = mysql_num_rows ($result);
if (mysql_errno ($DB_Host_Ptr))
DBError ($query);

DBFreeResult ($result);

return $rows;
}

// fetch the number of rows updated by a query function
function DBAffectedRows () {
global $DB_Host_Ptr;

$rows = mysql_affected_rows ($DB_Host_Ptr);
if (mysql_errno ($DB_Host_Ptr))
DBError ($query);

return $rows;
}

// get last autoincrement value function
function DBInsertID () {
return mysql_insert_id ();
}

// fetch query result as an array function
function DBFetchArray ($query) {
global $DB_Host_Ptr;

$results = mysql_fetch_array ($query);
if (mysql_errno ($DB_Host_Ptr))
DBError ($query);

return $results;
}

// fetch query result as a list of fields function
function DBFetchRow ($query) {
global $DB_Host_Ptr;

$results = mysql_fetch_row ($query);
if (mysql_errno ($DB_Host_Ptr))
DBError ($query);

return $results;
}

// get a result row function
function DBFetchFields ($query) {
return (SetArrayVars (DBFetchArray ($query)));
}

// get a single field result function
function DBFetchValue ($query) {
list ($field) = DBFetchRow ($query);
return $field;
};

// get a single row result function
function DBGetArray ($select) {
$query = DBQuery ($select);
$row = DBFetchArray ($query);
DBFreeResult ($query);
return $row;
}

// fetch query result as a list of fields function
function DBGetRow ($select) {
$query = DBQuery ($select);
$results = DBFetchRow ($query);
DBFreeResult ($query);
return $results;
}

// get a single row result function
function DBGetFields ($select) {
$query = DBQuery ($select);
DBFetchFields ($query);
DBFreeResult ($query);
}

// get a single field result function
function DBGetValue ($select) {
$query = DBQuery ($select);
list ($field) = DBFetchRow ($query);
DBFreeResult ($query);
return $field;
};

// get a single field array result function
function DBGetArrayValue ($select) {
$query = DBQuery ($select);
$array = array ();
while (list($field) = DBFetchRow ($query)) {
$array [] = $field;
}
DBFreeResult ($query);
return $array;
};

// get a multi fields array result function
function DBGetArrayFields ($select) {
$query = DBQuery ($select);
$array = array ();
while ($result = DBFetchArray ($query)) {
$array [] = $result;
}
DBFreeResult ($query);
return $array;
}

// create database variables function
function SetArrayVars ($array) {
global $DEBUB_SW;

if (!$array)
return (false);

reset ($array);
while (list ($var, $val) = each ($array)) {
if (gettype ($var) != "integer") {
global $$var;
$$var = $val;
if ($DEBUG_SW) { echo "<!-- SetArrayVars: $var = $val -->>\n"; }
}
}

return (true);
}

// create an enum list array function
function DBGetEnum ($table, $column, $sorted="unsorted") {
global $Field, $Type;

$enums = array ();

DBGetFields ("SHOW COLUMNS FROM $table LIKE'$column'");

if ($Field && strstr (strtoupper ($Type), 'ENUM')) {
$str = substr ($Type, strpos ($Type, "(") + 1, strpos ($Type, "\)") -1);
$tok = strtok ($str, ",");
while ($tok) {
$tok = substr ($tok, 1, strlen ($tok) - 2);
$enums [$tok] = $tok;
$tok = strtok (",");
}

switch(strtolower($sorted)) {
case "ascending" :
asort($enums);
break;

case "descending" :
arsort($enums);
break;

default :
break;
}

}

return $enums;
}

// get default field value function
function DBGetDefault ($table, $column) {
global $Field, $Default;

DBGetFields ("SHOW COLUMNS FROM $table LIKE '$column'");

if ($Field) {
$default = (substr ($Default, 0, 1) == "'" ?
substr ($Default, 1, strlen ($Default) - 2) :
$Default);
}

return $default;
}
 
You must log in or register to reply here.
Back
Top