Connecting to a second database

[Mr_Mako2]

I'm attempting to figure out how to connect to a second database to grab information from a table. This second database has a different username and password that the one the website is currently using (it is on the same server however).

I've hunted through the forums for a solution and aside from a large amount of "how to connect to two tables" type situations I haven't found anything that assists me with figuring this out.

I tried a completely separate connection string - i.e. :

// Primary Database Connection //
$connect = @mysql_connect("localhost", "user", "password") or die(mysql_error());
$db = @mysql_select_db($database, $connect) or die(mysql_error());

.... bunch of page code ...

// Secondary Database Connection //
$sec_connect = @mysql_connect("localhost", "alt_user", "alt_password") or die(mysql_error());
$sec_db = @mysql_select_db($sec_database, $sec_connect) or die(mysql_error());

[query to grab data from second database]

... but that fails as further down another query into the primary database fails because it's looking into the secondary database instead.

Additionally, I can accomplish this by closing the primary database connection, opening one to the secondary database, grabbing what I need, then closing the secondary and re-opening the primary - but that's a lot of db connections to open and close and not very efficient.

Thanks for any assistance...

[bradgrafelman]

Can you show us the code you tried "further down" when it failed? You should be able to pass the appropriate connection variable to all mysql-related functions and they will use the correct connection.

[devinemke]

you need to use the correct link identifier in your calls to [man]mysql_query[/man]. for example:

$connect1 = mysql_connect('localhost', 'user1', 'password1') or exit(mysql_error());
$select1 = mysql_select_db('database1', $connect1) or exit(mysql_error());

$connect2 = mysql_connect('localhost', 'user2', 'password2') or exit(mysql_error());
$select2 = mysql_select_db('database2', $connect2) or exit(mysql_error());


$result = mysql_query('SELECT something FROM table1', $connect1) or exit(mysql_error());
// code dealing with this result set

$result = mysql_query('SELECT something FROM table2', $connect2) or exit(mysql_error());
// code dealing with this result set

[Mr_Mako2]

Sure. It's just a simple SELECT query -

$link = "SELECT * FROM link ORDER BY link_id ASC";
$link_result = @mysql_query($link, $connect) or die(mysql_error());

while ($link_row = mysql_fetch_array($link_result)) {
     $link_name = $link_row['link_name'];
     $link_url = $link_row['link_url'];

 $link_list .= "<a href=$link_url target=_blank>$link_name</a>";
}

That's the first query after the secondary database connection is made. The error I get is :

The table "[secondarydb].link" doesn't exist.

[bradgrafelman]

Where is $connect ever defined?

[Mr_Mako2]

$connect is defined in an include file that runs at the top of the page before any other commands or queries run.

[Mr_Mako2]

devinemke;10912760 wrote:

you need to use the correct link identifier in your calls to [man]mysql_query[/man]. for example:

Well - essentially I've done just that. But any query I do after the secondary database connection is made seems to assume - irregardless of how I phrase it pointing to the primary database - that I'm trying to pull data from the secondary database instead.

[laserlight]

Mr Mako wrote:

Well - essentially I've done just that. But any query I do after the secondary database connection is made seems to assume - irregardless of how I phrase it pointing to the primary database - that I'm trying to pull data from the secondary database instead.

I suggest that you show the smallest and simplest script that demonstrates the problem. (That is, do not include things like ".... bunch of page code ...". It should be an example that you can run, and which we could run too if we had the database setup.)

[devinemke]

you need to position you calls to [man]mysql_select_db[/man]. for example:

$connect1 = mysql_connect('host1', 'user1', 'password1') or exit(mysql_error());
$connect2 = mysql_connect('host2', 'user2', 'password2') or exit(mysql_error());

mysql_select_db('database1', $connect1) or exit(mysql_error());
$result = mysql_query('SELECT something FROM table1', $connect1) or exit(mysql_error());
// code dealing with this result set


mysql_select_db('database2', $connect2) or exit(mysql_error());
$result = mysql_query('SELECT something FROM table2', $connect2) or exit(mysql_error());
// code dealing with this result set 

[Mr_Mako2]

laserlight;10912839 wrote:

I suggest that you show the smallest and simplest script that demonstrates the problem. (That is, do not include things like ".... bunch of page code ...". It should be an example that you can run, and which we could run too if we had the database setup.)

Ok, here's what I have.

I have two databases I need to grab data from on two pages. Examples are below :

Primary DB Table (ie, site.link)
---------------------------
link_desc |	link_url
---------------------------
Home	  |	index.php
News	  |	news.php
Roster	  |	roster.php


Secondary DB Table (ie, forum.forum_users)
----------------------------------
user_name   |	user_email
----------------------------------
Bob Hoskins |	[e-mail]
Rich John   |	[e-mail]
Brad Layer  |	[e-mail]

I have an include file that is included at the top of every page that contains the database connection. This runs before anything else runs to open the database for queries down the page(s) :

<?php

// DB Connection to Primary Database (filename - dbcon.php) //
$connect = @mysql_connect("localhost", "$user", "$pwd") or die(mysql_error());
$db = @mysql_select_db($db, $connect) or die(mysql_error());
?>

So, with all that built and explained, the page I'm having problems with is exampled below. The Primary DB connection executes via an 'include' command, then I open a connection to the second DB and run a query to get the user data from it. Then, further down, the page executes a query to grab page link data from the Primary DB and build a directory tree.

<?php

// Include DB Connection (filename - forumquery.php) //
include('dbcon.php');

// Create DB Connection to Second DB //
$fconnect = @mysql_connect("localhost", "$fuser", "$fpwd") or die(mysql_error());
$fdb = @mysql_select_db($fdb, $fconnect) or die(mysql_error());

// Get Forum User Data //
$fuser = "SELECT user_name, user_email FROM forum_users ORDER BY user_name ASC";
$fuser_result = @mysql_query($fuser, $fconnect) or die(mysql_error());

while ($frow = mysql_fetch_array($fuser_result)) {
	$username = $frow['user_name'];
	$useremail = $frow['user_email'];

// Create User Display //
$display_users .= "<div class=user><a mailto:$useremail>$username</a></div><br>";
}

// Create Links Query from Primary Database //
$lnk = "SELECT * FROM link ORDER BY link_id ASC";
$lnk_result = @mysql_query($lnk, $connect) or die(mysql_error());

while ($lrow = mysql_fetch_array($lnk_result)) {
	$link_url = $lrow['link_url'];
	$link_desc = $lrow['link_desc'];

// Create Navitation Link List //
$display_links .= "&middot;&nbsp;&nbsp;<a href=$link_url>$link_desc</a><br>";
}
?>

However, what happens is I get an error that reads :

The table "forum.link" doesn't exist.

Now either I'm missing something that's so simple I just can't see it, or my DB connection queries are not coded correctly. It's still attempting to grab data from the Secondary database even though I've made a call to the Primary DB '$connect' and not the Secondary's '$fconnect'.