Joining Tables from different Database

Question

I am trying to fetch data from two tables in two different database in a same server. I am using this code

<?php

    $host='localhost';
    $root='root';
    $password='mypass';
    $db=mysql_connect($host,$root,$password) or die('unable to connect database');
    $dbname='BUSMSTR_10';
    mysql_select_db($dbname,$db) or die(mysql_error($db));

    $db2=mysql_connect($host,$root,$password) or die('unable to connect database');
    $dbname='BULIB_Info';
    mysql_select_db($dbname,$db2) or die(mysql_error($db2));

    $sql="SELECT 
        m_Student.Stud_Name_Form AS Stud_Name_Form,
        m_Student.Enrl_no AS Enrl_no,
        m_Subject.Sub_name AS Sub_name
    FROM 
        BUSMSTR_05.m_Student m_Student
    INNER JOIN
        BULIB_Info.m_Subject m_Subject
    ON
        m_Student.Sub_ID=m_Subject.Sub_ID

    LIMIT 10";

$rs=mysql_query($sql);

    while($row=mysql_fetch_assoc($rs)){
        echo $row['Stud_Name_Form']."|||||".$row['Enrl_no']."|||||".$row['Sub_name']."<BR>";
        }

?>

But I am getting error. whats wrong with it.... and how will I fix this??

Error msg----

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/budcc/html/student/PHP/Student.php  on line 25

Who knows? You haven't even told us what the error is you are getting! — Tony Andrews, Feb 04 '11 at 12:29
If the databases are on the same server and you have the correct credentials and permissions, you shouldn't need to make 2 connections, just use the first connection — anothershrubery, Feb 04 '11 at 12:43

score 4 · Answer 1 · edited Feb 04 '11 at 14:10

4

It's really not difficult to join seperate databases (assuming they reside on the same server) Just like you would specify fields using the "table.field", you can also use "database.table.field" Below is an example of a two database join:

$sql="SELECT db1.table1.somefield, db2.table1.somefield FROM db1.table1 INNER JOIN db2.table1 ON db1.table1.someid = db2.table1.someid WHERE db1.table1.somefield = 'queryCrit';"

You simply write you query just like you would if you were working in one db, just use the dot notation to specify your databases as well.

As far as your problem goes i dont think you are adding database names before tables names everywhere.Try that.

edited Feb 04 '11 at 14:10

Aron Rotteveel

81,193
17
104
128

answered Feb 04 '11 at 12:32

ayush

14,350
11
53
100

As you told I tried by adding database name but still the problem is same – Kuntal Basu Feb 04 '11 at 13:28
the error you get suggest that there is probably no row selected by your query. Try an echo of $sql and then do this exact query manually in mysql(phpmyadmin). See if returns any rows. – ayush Feb 04 '11 at 13:32

score 1 · Answer 2 · answered Feb 04 '11 at 14:33

1

You opened two database connection but didn't tell PHP which one to use for the query. In this case the last link opened is assumed. Pass the connection resource to mysql_query() explicitly.

$rs=mysql_query($sql, $db);

answered Feb 04 '11 at 14:33

viam0Zah

25,949
8
77
100

Joining Tables from different Database

2 Answers2

Linked