Retrieving Data from Two Different Databases

[rotc_rachel]

Hello All,

   

  Ran into another problem and would appreciate some help! I have two databases, publications_db (pub_id, title, author, yr, journal, num_pages) and activity_db(member_id,pub_id,date).  Basically, when a user is logged in, I want to have an option for them to view the publications they have submitted to the publication database. 

 

In pseudocode, I want to display * from publications_db where the session_id (currently logged in user) matches the activity_db.member id AND activity_db.pub_id matches publications_db.pub_id 

 

Below is the code I was writing although I am sure it is completely off because I am quite new to this stuff!  Any help would be greatly appreciated!

 

$dbhost="localhost";

                $dbuser="****";

                $dbpass="***";

                $dbname1="publications_db";

                $dbname2="activity_db";

                $db1=mssql_connect($dbhost,$dbuser,$dbpass);

                mssql_select_db($dbname1,$db1);

                mssql_select_db($dbname2,$db1);

             

    $query="SELECT * FROM publications_db P, activity_db A WHERE $_SESSION['SESS_MEMBER_ID'] = A.member_id AND A.pub_id = P.pub_id;";

 

echo "<table border cellpadding=2>";

               

                    while($info = mysql_fetch_array( $query ))

                    {

                      echo "<tr>";

                      echo "<th>ID:</th><td>".$info['pub_id'] . "</td> ";

                      echo "<th>Title:</th> <td>".$info['title'] . " </td>";

                      echo "<th>Author:</th> <td>".$info['author'] . " </td>";

                      echo "<th>Year:</th> <td>".$info['yr'] . " </td>";

                      echo "<th>Journal:</th> <td>".$info['journal'] . " </td>";

                      echo "<th>Pages:</th> <td>".$info['num_pages'] . " </td></tr>";

                     

                    }

       

                    echo "</table><br>";

        echo "$count_entry Total Entries";

 

    ?>               

[SchweppesAle]

two databases or two tables?

 

 

[rotc_rachel]

I guess I want all of the data from one database, but using information from two different databases

 

For instance, my activity_db logs the member_id and the publication_id they posted.  I want to have a link next to each user's username that states 'View My Publications', which will need to match their username to the username in my activity_db (which assures the search will only retrieve the publications from the logged in user) and then retrieve all the publication_ids from the activity_db (matches member_id to publication_id), that publication_id I want to use to show all of the entries associated with it in the publications_db.

[SchweppesAle]

something like this

 

$username = 'blah';
$password = 'blahblah';
$database = 'name_of_database';

$connection = mysql_connect('localhost', $username, $password);

if(!$connection)
{
die('damn it all'.mysql_error());
}

/*we're selecting 1 database*/
mysql_select_db($database);

 

After that you'll probably want to join the tables(i guess?)

 

Here's a good example:

http://flora-culture.comwww.w3schools.com/sql/sql_join_inner.asp

 

$query = 'read the tutorial';
$result = mysql_query($query);

while($row = mysql_fetch_array($result))
{
/*store your values in an array for later use*/
}