Gossamer Forum: Products: Gossamer Links: Discussions: Accessing other database on same domain

Oct 3, 2005, 11:39 PM

aus_dave

User (276 posts)

Oct 3, 2005, 11:39 PM

Post #1 of 10

Shortcut

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

Oct 3, 2005, 11:54 PM

Andy

Veteran / Moderator (18436 posts)

Oct 3, 2005, 11:54 PM

Post #2 of 10

Shortcut

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

Oct 4, 2005, 12:05 AM

aus_dave

User (276 posts)

Oct 4, 2005, 12:05 AM

Post #3 of 10

Shortcut

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?

Oct 4, 2005, 12:16 AM

Andy

Veteran / Moderator (18436 posts)

Oct 4, 2005, 12:16 AM

Post #4 of 10

Shortcut

Re: [aus_dave] Accessing other database on same domain In reply to

Hi,

Quote:

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

Oct 4, 2005, 12:24 AM

aus_dave

User (276 posts)

Oct 4, 2005, 12:24 AM

Post #5 of 10

Shortcut

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

Oct 4, 2005, 12:36 AM

Andy

Veteran / Moderator (18436 posts)

Oct 4, 2005, 12:36 AM

Post #6 of 10

Shortcut

Re: [aus_dave] Accessing other database on same domain In reply to

Try this

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

Oct 4, 2005, 1:15 AM

aus_dave

User (276 posts)

Oct 4, 2005, 1:15 AM

Post #7 of 10

Shortcut

Re: [Andy] Accessing other database on same domain In reply to

Thanks Andy, that looks like more than enough to get me started Wink

Oct 4, 2005, 6:47 AM

aus_dave

User (276 posts)

Oct 4, 2005, 6:47 AM

Post #8 of 10

Shortcut

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

Oct 4, 2005, 7:47 AM

Andy

Veteran / Moderator (18436 posts)

Oct 4, 2005, 7:47 AM

Post #9 of 10

Shortcut

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

Oct 4, 2005, 5:06 PM

aus_dave

User (276 posts)

Oct 4, 2005, 5:06 PM

Post #10 of 10

Shortcut

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).