[Tutorial] Connecting to an external database to SMF

[juanpons14]

TUTORIAL: Direct from Spanish Board (Created by me and translated by Google translate) Here the spanish topic.

Well, this simple and quick tutorial is sure to fix the lives of many.

Why and for what anyone want to connect to another database other than SMF? So easy. I have now installed SMF on my website and WORDPRESS .. some probably have some other Joomla CMS .. or anything else..
This simple tutorial is for those who used two different DB's in time to install SMF and another application that use Database (WP, Joomla, VBulletin..etc....or people that have more the one website ad want to extract information of an external DB.

In my case i created one DB (wit a sufix _x) for SMF and another different (with prefix _y) to install WP..because i want to use a plugin to integrate my users in both applications.

But one day, I needed to get a user specific field that was used in Wordpress to build a link to the "Manage Subscription" (Newsletters) and I wanted to add a link in the forum, but i don't have this key (different for each user) then i can't.

So I decided to investigate and in two hours I got it, thanks to my exhaustive search.

Let's begin:

First of all you must have very clear in the following fields:

• Host
• DB Name
• DB User
• DB Password

Obviously the name of the DB is not the same as that which has been in SMF because we want to connect to a different one.

Now we pass to the code:

Create a new PHP file with either Dreamweaver or any editing program you use and put in this code:

Code Select Expand


<?php
function Conectarse()

{

$db_host="HOST/IP"; // Host al que conectar, habitualmente es el 'localhost'

$db_nombre="DBNAME"; // Nombre de la Base de Datos que se desea utilizar

$db_user="DBUSER"; // Nombre del usuario con permisos para acceder

$db_pass="PASSWORD"; // Contraseña de dicho usuario

// Ahora estamos realizando una conexión y la llamamos '$link'

$link=mysql_connect($db_host, $db_user, $db_pass) or die ("Error conectando a la base de datos.");

// Seleccionamos la base de datos que nos interesa

mysql_select_db($db_nombre ,$link) or die("Error seleccionando la base de datos.");

// Devolvemos $link porque nos hará falta más adelante, cuando queramos hacer consultas.

return $link;

}
?>


Do not forget to fill in the following details "HOST/IP" "PASSWORD" "DBNAME" and "DBUSER".

Once done, we save our PHP file with a short name that is easy to remember us as db_ext.php and we upload the file to the root of our theme via FTP.

Well, after setting up this part, we go to our theme file where you want to add the information belonging to another DB, that is, where we want make our sql queries to the previously configured DB. (In my case it was the Index.template.php).

And we will put the following code:

Code Select Expand

include('http://www.YOURSITE.com/Themes/YOURTHEME/db_ext.php');

In this way, we call the previously created file and not have to include everything that huge code every time you want to use, with a simple include sufficient.

Do not forget to replace " YOURSITE [/ b]" and " YOURTHEME [/ b]" I think they are fairly intuitive to see which they relate. , D

Once you add the code then add the following:

Code Select Expand

$link = Conectarse();
$sql = " SELECT name, lastname FROM mytable ";
$result = mysql_query($sql) or die('La siguiente consulta contiene algún error:<br>nSQL: <b>$sql</b>');
while ($row = mysql_fetch_array($result))
{
echo '<b><i>'.$row['name'].'</i></b>
<br /><b><i>'.$row['lastname'].'</i></b>';

Now explain that we set in this part. With this code

Code Select Expand

$sql = " SELECT name, lastname FROM mytable ";

Selected as it indicates the fields "Name" and "Last Name" (just an example you can select what suits you for what you want to do) of the Table "mytable". These values must change according to what we have and what we want to do.

Example:
We select the field (like is my case) called "confkey" and it attaches to my WP a "API" to each user.

Then we do the following:

Code Select Expand

$sql = " SELECT confkey FROM wp_mailpress_users ";

So far so good, but as I said we want to filter the field as the user that is ... and as my WP and SMF users are the same we do the following:

We created the variable $username

Code Select Expand

$username = $context['user']['name'];

And we say now that select the "CONFKEY" where the name (from WP) = the username currently connected on SMF.

Code Select Expand

$sql = " SELECT confkey FROM wp_mailpress_users WHERE name = '$username' ";

And then we show the result:

Code Select Expand

echo '<a class="daddy item" href="http://www.planetdescargas.com/wp-content/plugins/mailpress/mp-includes/action.php?action=mail_link&del='.$row['confkey'].'"></a>';

In my case, I have integrated Wordpress users with SMF and user names are the same. What I do is say that you take the field called CONFKEY which is on the table wp_mailpress_users and matches the user name which is connected in SMF $context['user']['name'].

I really hope that I will understand it haha! if someone needs something to get help here and I will try to help as I can.
Much of the information I have been out of here.
And my website is here.

Well greetings and thanks.

PD: And this does not stay in one simple KEY but also you can extract the latest posts from Wordpress or Joomla or any independent application and display it on SMF .... or any statistical from Wordpress ... anything, it's just good to know fields and tables and know where to find everything.

PD2: I think that this is not the right place, but I can not post to the forum tips

[KensonPlays]

Great tutorial, Will keep in mind if needed!

[juanpons14]

Thank you...

I have posted in the spanish topic differents examples for example:

Displaying the latest posts of your wordpress in SMF
Displaying the latest comments of your wordpress in SMF
Getting an specific meta value of your posts (by category or al posts) in mordpress and display it in SMF

etc...you can do everything only you have to know a litle of DB