Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Accessing other database on same domain

Quote Reply
Accessing other database on same domain
I have a site with the directory part being run by Links, and the user registration/forums run by vBulletin. Both scripts are using separate databases.

I have had a search around but haven't found much information on how to connect to another database from Links. What I would like to have is some kind of plugin or global that can connect to the forum database and extract the information I want (e.g. recent thread titles, number of unread private messages).

If someone can point me in the right direction that would be a good start Smile.
Quote Reply
Re: [aus_dave] Accessing other database on same domain In reply to
Hi,

Have you considered using DBI?

Otherwise, one thing I've done before, is create a setr of .def files, and then use GT::SQL to connect. This is the slightly harder and more long winded option though, as you need to make sure you populate it with all the required fields (and vB doesn't have small tables <G>).

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Accessing other database on same domain In reply to
Thanks for that Andy. Do you know of any good online resources for DBI or if there is any pseudo code floating around here? Would this be best done in a plugin or a global (sorry for the questions - I am a Perl novice unfortunately Unsure).

Alternatively, would you be interested in this as a custom coding job?
Quote Reply
Re: [aus_dave] Accessing other database on same domain In reply to
Hi,

Quote:
Thanks for that Andy. Do you know of any good online resources for DBI or if there is any pseudo code floating around here? Would this be best done in a plugin or a global (sorry for the questions - I am a Perl novice unfortunately Unsure).

What are you actually trying to do? =) Might help me try and come up with an example for you :)

Quote:
Alternatively, would you be interested in this as a custom coding job?
Sorry, I wish I could (I'm bogged down at the moment as it is ... must be the time of year :/).

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Accessing other database on same domain In reply to
Ok...

Say in the left sidebar include, I would like to have a tag available for a logged in user, call it <%unreadpms%>.

To get this value we would need to:

1)Connect to vBulletin database
2)Run the appropriate SQL query on the vB user table
3)Return this number back to Links for use as <%unreadpms%> in the template

It sounds simple when it is laid out like that but I have no idea where to start!

Last edited by:

aus_dave: Oct 4, 2005, 12:24 AM
Quote Reply
Re: [aus_dave] Accessing other database on same domain In reply to
Try this Smile

Code:
sub {

my $user = 'user';
my $pass = 'pass';
my $host = 'localhost';
my $db = 'database';

my $current_user = $USER->{Username};

my $query = qq{SELECT COUNT(*) FROM pmtext WHERE .....};

my $dbh = DBI->connect ("DBI:mysql:host=$host;database=$db",$username, $password,{ PrintError => 1, RaiseError => 1 } ) || die "ERROR: Can't connect";

my $sth = $dbh->prepare($query) || die("$DBI::errstr");
$sth->execute();
my $pmcount = $sth->fetchrow;

$pmcount ? return $pmcount : return '0';

}

You need to set the database details near the top, and also tweak the $query, so that it actually gets the correct count (afraid I can't remember the correct table properties ATM).

Hope that helps :)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Accessing other database on same domain In reply to
Thanks Andy, that looks like more than enough to get me started Wink.
Quote Reply
Re: [aus_dave] Accessing other database on same domain In reply to
Andy, that worked nicely. Table names are different in vB 3.5 and the query was pretty straightforward in the end. Now I need to be careful I don't go query crazy and slow the site down too much!

For anyone using the code above, not that $user and $pass are only shorthand for $username and $password i.e. make sure you use the same variable name in all instances Wink.
Quote Reply
Re: [aus_dave] Accessing other database on same domain In reply to
Glad to hear it :) Would you mind sharing the query that was needed? (I didn't complete it, but I'm sure someone else will be happy for you to share <G>)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Accessing other database on same domain In reply to
Might as well post the whole global:

Code:
sub {
my $user = 'vbdatabaseusername';
my $pass = 'vbdatabasepassword';
my $host = 'localhost';
my $db = 'vbdatabasename';

my $current_user = $USER->{Username};

my $query = qq{SELECT pmunread FROM user WHERE username='$current_user'};

my $dbh = DBI->connect ("DBI:mysql:host=$host;database=$db",$user, $pass,{ PrintError => 1, RaiseError => 1 } ) || die "ERROR: Can't connect";

my $sth = $dbh->prepare($query) || die("$DBI::errstr");
$sth->execute();
my $pmcount = $sth->fetchrow;

$pmcount ? return $pmcount : return '0';
}

Call this global 'unread_pms' and place the <%unread_pms%> tag anywhere you want user information to appear (dynamic site only of course).