getting data from a 2nd table, based on a mutual field value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gopanthers
Forum Newbie
Posts: 9
Joined: Fri Nov 05, 2010 5:00 pm

getting data from a 2nd table, based on a mutual field value

Post by gopanthers »

I've got membership data stored in 2 totally different databases. Database A contains most of the data but each member's email address is stored in a totally different Database B (a WordPress DB on the same server). Member data in each table can be linked together because the field "wp_user" in database A matches a field "wp_login"in database B.

I am displaying data about a specific member by putting their id# in the URL, so that their info would be displayed on a page like http://website.com/info?id=68. I can display all that user's data from Database A easily but I can't figure out how to display their email address, which is found in Database B.

My normal code for data found in Database A only is this

Code: Select all

$server = "localhost";
$username = "XXXX";
$password = "XXXX";
$database = "XXXX";
$con = mysql_connect($server, $username, $password);
mysql_select_db($database, $con);

$id=$_GET['id'];

$sql = "SELECT * FROM table_from_DB_A WHERE id=$id";
$result = mysql_query($sql);    
$myrow = mysql_fetch_array($result);
And then I'll display just the data from that user.

But what I really need to do now is get the email address from database B based a mutual field value, but still based on the id value found in the URL.

For a plain example:

database A, table A
id=1 | firstname=John | lastname=Smith | age=28 | wp_user=JSmith
id=2 | firstname=Mike| lastname=Johnson| age=24 | wp_user=MJohnson
id=3 | firstname=Dan| lastname=Jackson| age=33 | wp_user=DJackson

database B, table B
wp_login=MJohnson| wp_email=MJ1234@gmail.com
wp_login=DJackson | wp_email=DJ1234@yahoo.com
wp_login=JSmith | wp_email=JS1234@msn.com

In this example, if the website URL is http://website.com/info?id=3, I need to display Dan Jackson's email address (DJ1234@yahoo.com) based on the fact that "DJackson" is the value for both wp_user in database A and wp_login in database_B. How do I do that???

I don't know if this is the right or wrong track but here's what I've tried and it didn't work:

Code: Select all

<?php

// query the WordPressDB
$server = "localhost";
$username = "prefix_username";
$password = "XXXXXX";
$database = "prefix_dbname";
$con = mysql_connect($server, $username, $password);
mysql_select_db($database, $con); 

// query the WordPressDB
$wp_server = "localhost";
$wp_username = "prefix_wordpress";
$wp_password = "XXXXXX";
$wp_database = "prefix_wordpress";
$wp_con = mysql_connect($wp_server, $wp_username, $wp_password);
mysql_select_db($wp_database, $wp_con); 

$id=$_GET['id'];

$wp_sql = "SELECT prefix_wordpress.wp_users.user_email FROM prefix_wordpress.wp_users
            JOIN mainDB.brotherhood ON prefix_wordpress.wp_users.wp_login = mainDB.brotherhood.wp_user
            WHERE mainDB.brotherhood.id = $id";
$wp_result = mysql_query($wp_sql);    
$wp_row = mysql_fetch_array($wp_result);

echo "this user\'s email address is ".$wp_row["user_email"];
?> 
Like I said, I don't know if this is even close to being right so if somebody has a totally different way of doing this I'm all for it. Anybody know what I'm doing wrong? Thanks!
Post Reply