Adding a Second DB Connection in Forum Code

[thall89553]

On my server I have another database I'd like to establish a connection to. Essentially I will have a bunch of websites on the same server all linking to one domain name's forum. Based on a url variable (?shop=23) I want to establish some session variable that will define the look of the forums header and some other criteria. So saying I have a mySQL database on the same server as the forum database how and where would I specify this connection to the second database upon entry to the site? I hope someone can help me out.

[Elmacik]

Open your Settings.php before ?> add this:

Code Select Expand


$db2_name = 'your_db_name';
$db2_passwd = 'your_db_pass';
$db2_user = 'your_db_user';


Open your very first index.php before ?> add this:

Code Select Expand


// Connect to the MySQL database.
if (empty($db_persist))
$db2_connection = @mysql_connect($db_server, $db2_user, $db2_passwd);
else
$db2_connection = @mysql_pconnect($db_server, $db2_user, $db2_passwd);
if (!$db2_connection || !@mysql_select_db($db2_name, $db2_connection))
db_fatal_error();


Then go to your Source folder and open Subs.php before ?> add this:

Code Select Expand


function db2_query($db_string, $file, $line)
{
global $db_cache, $db_count, $db2_connection, $modSettings;

// One more query....
$db_count = !isset($db_count) ? 1 : $db_count + 1;

// Debugging.
if (isset($GLOBALS['db_show_debug']) && $GLOBALS['db_show_debug'] === true)
{
if (!empty($_SESSION['debug_redirect']))
{
$db_cache = array_merge($_SESSION['debug_redirect'], $db_cache);
$db_count = count($db_cache) + 1;
$_SESSION['debug_redirect'] = array();
}

$db_cache[$db_count]['q'] = $db_string;
$db_cache[$db_count]['f'] = $file;
$db_cache[$db_count]['l'] = $line;
$st = microtime();
}

// First, we clean strings out of the query, reduce whitespace, lowercase, and trim - so we can check it over.
if (empty($modSettings['disableQueryCheck']))
{
$clean = '';
$old_pos = 0;
$pos = -1;
while (true)
{
$pos = strpos($db_string, '\'', $pos + 1);
if ($pos === false)
break;
$clean .= substr($db_string, $old_pos, $pos - $old_pos);

while (true)
{
$pos1 = strpos($db_string, '\'', $pos + 1);
$pos2 = strpos($db_string, '\\', $pos + 1);
if ($pos1 === false)
break;
elseif ($pos2 == false || $pos2 > $pos1)
{
$pos = $pos1;
break;
}

$pos = $pos2 + 1;
}
$clean .= '%s';

$old_pos = $pos + 1;
}
$clean .= substr($db_string, $old_pos);
$clean = trim(strtolower(preg_replace(array('~\s+~s', '~/\*!40001 SQL_NO_CACHE \*/~', '~/\*!40000 USE INDEX \([A-Za-z\_]+?\) \*/~'), array(' ', '', ''), $clean)));

// We don't use UNION in SMF, at least so far.  But it's useful for injections.
if (strpos($clean, 'union') !== false && preg_match('~(^|[^a-z])union($|[^[a-z])~s', $clean) != 0)
$fail = true;
// Comments?  We don't use comments in our queries, we leave 'em outside!
elseif (strpos($clean, '/*') > 2 || strpos($clean, '--') !== false || strpos($clean, ';') !== false)
$fail = true;
// Trying to change passwords, slow us down, or something?
elseif (strpos($clean, 'set password') !== false && preg_match('~(^|[^a-z])set password($|[^[a-z])~s', $clean) != 0)
$fail = true;
elseif (strpos($clean, 'benchmark') !== false && preg_match('~(^|[^a-z])benchmark($|[^[a-z])~s', $clean) != 0)
$fail = true;
// Sub selects?  We don't use those either.
elseif (preg_match('~\([^)]*?select~s', $clean) != 0)
$fail = true;

if (!empty($fail))
{
log_error('Hacking attempt...' . "\n" . $db_string, $file, $line);
fatal_error('Hacking attempt...', false);
}
}

$ret = mysql_query($db_string, $db2_connection);
if ($ret === false && $file !== false)
$ret = db_error($db_string, $file, $line);

// Debugging.
if (isset($GLOBALS['db_show_debug']) && $GLOBALS['db_show_debug'] === true)
$db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));

return $ret;
}


Now anytime you need to run SQL queries in your other DB, just type this:
db2_query("SQL QUERY", __FILE__, __LINE__);

This will also show and log the errors if any occur.
I just copied the strings from originals.

[thall89553]

Hey thanks for taking the time to show me that, I really apprecaite it! If you don't mind may I please elaborate a little more on what I am trying to do. I have a series of websites all on the same server. I am going have one domain house the forum - www.primarydomain.com/forum   On the other websites I am going to have a link on each domain such as this - www.secondarydomain.com/forum/index.php?shop=23.  When that url is called what I would like to do is to connect to a table in the 2nd database called "shop_members" where the id field equals the shop variable, in this case 23. Once I execute that sql command ( SELECT * FROM directory where id = 23 ) I want to set some global variables such as url, telephone, address, etc. Where does all this take place? I tried doing it in the default template file index.template.php but I get an error.

[Elmacik]

Hmm.. Just after you use the function, define the variables first.
Like this way:

Code Select Expand


<?php
$my_second_db = db2_query("SELECT * FROM shop_members WHERE id = '23';", __FILE__, __LINE__);
while ($result = mysql_fetch_assoc($my_second_db))
{
$some_var1 = $result['id'];  // This points to 23, remember? :)
$some_var2 = $result['item_name'];   // This may be the name of that item.
$some_var3 = $result['column_name'];     // Column name.. Thats the column name we are typing inside the single quotes. ID is a column name, item_name is a column name, in the table.

// Or you can build an array..
$some_vars = array();
$some_vars['id'] = $result['id'];
$some_vars['item_name'] = $result['item_name'];
}
?>


So when using in another file or another place, just do this:

Code Select Expand


<?php
// If you built an array, use it this way:
global $some_vars;
echo ' The item you selected was ', $some_vars['item_name'], ' Please confirm..';
// If you didnt build an array use it this way:
global $some_var1, $some_var2, $some_var3;
echo ' The item you selected was ', $some_var2, ' and its ID is ', $some_var1, '... ';
?>

Or another option might be using session ( $_SESSION) but I am not good at with it

[thall89553]

Thanks for your help. Ok, check this out. At the very bottom of my index.php page I have this code:


if (empty($db_persist))

$db2_connection = @mysql_connect($db_server, $db2_user, $db2_passwd);
else

$db2_connection = @mysql_pconnect($db_server, $db2_user, $db2_passwd);
if (!$db2_connection || !@mysql_select_db($db2_name, $db2_connection))

db_fatal_error();

$sql_row = mysql_fetch_array(db2_query("SELECT * from Directory where id = 2", __FILE__, __LINE__));
$some_var1 = $sql_row['shop']; 
$some_var2 = $sql_row['url'];   
$some_var3 = $sql_row['street']; 


I am able to run this with no problem. Now look at my main forum page - www.1stautorepair.com/forum  What I want is to have these variable values used to insert the information that  appear at the top of that page where you see the address, navigation, telephone, etc. I tried putting the variables into the index.template.php file where I modified the code but that throws an error. Any suggestions?

[Elmacik]

I answered this question in other topic of yours.