retrieving data from two databases

Question

I have two databases and tables in each. Am reading the renewal_date of DB1 table 1 and taking the renewal_date of the current month and domain_name for that record.

enter image description here

then am trying to retrieve the d_due_date from DB2 table2 for the domain_name selected from DB1 table1.

then i need to display domain_name, renewal_date,d_due_date in one table.

I can do this by joining the database with INNER JOIN.

what i need is to write separate select queries and display.

     $sql = "select domain_name from table1 where MONTH(renewal_date) = '06'";

    $result = mysqli_query($link_id,$sql);
    if(!$result) die(sql_error());
    $DoNM= Array();

   $sql1= "select d_due_date from domains where d_domain IN  ('abc.com','akaaasa.com')";
   $result1 = mysqli_query($link_id1,$sql1);
   if(!$result1) die(sql_error());
   $DoNM1= Array();

  echo '<table>';
  while(($row1 = mysqli_fetch_array($result1,MYSQL_ASSOC))&&($row = mysqli_fetch_array($result,MYSQL_ASSOC))){

   echo "<tr>";
   echo "<td>" .$DoNM[]=  $row['domain_name'] . "</td>";
   echo "<td>" .$DoNM[]=  $row['renewal_date'] . "</td>";
   echo "<td>" .$DoNM1[]=  $row1['d_due_date'] . "</td>";

   echo "</tr>";
   }
   echo '</table><br />';

I have hardcoded the domain name in $sql1. what I want is to get that from $sql. how can I do that.

[Documentation](http://php.net/manual/en/mysqli-result.fetch-fields.php) — Tensibai, Jun 24 '15 at 08:29
I think this is your solution. http://stackoverflow.com/questions/5698378/mysql-join-between-tables-in-2-different-databases — milan kyada, Jun 24 '15 at 09:17
@milankyada No it does not cover the tables being in 2 different databases — RiggsFolly, Jun 24 '15 at 09:58

RiggsFolly · Answer 1 · 2015-06-25T08:58:50.380

So all you need to do is process through the first query results and build the array, then convert the contents of the array to a comma delimited list

$sql = "select domain_name, renewal_date
        from table1 
        where MONTH(renewal_date) = '06'";

$result = mysqli_query($link_id,$sql);
if(!$result) die(sql_error());

$db1= Array();
$InList = '';
while( $row = mysqli_fetch_array($result,MYSQL_ASSOC) ) {
    $InList .= sprintf("'%s',", $row['domain_name']);
    $db1[$row['domain_name']] = $row['renewal_date'];  
}
$InList = rtrim($InList, ',');

$sql = "select d_due_date, d_name 
        from domains 
        where d_domain IN ($InList)";

$result = mysqli_query($link_id1,$sql);
if(!$result) die(sql_error());

echo '<table>';

while( $row = mysqli_fetch_array($result,MYSQL_ASSOC ){

    echo '<tr>';
       echo '<td>' . $row['d_name'] . '</td>';
       // find the db1.renewal_date matching d_name from db1 array
       echo '<td>' . $db1[$row['d_name']] . "</td>";
       echo '<td>' . $row['d_due_date'] . '</td>';
    echo "</tr>";
}

echo '</table><br />';

RE: Your comment

So now I have saved the data from db1 into an array you can use the get the db1.renewal_date from in the output phase. Also I added the db1.d_name to the second query so you have the key to the array containing the db1.renewal_date

RE: Using more fields from table1:

Sure, thats not a problem. This will mean that you have to store an array i.e. the $row as the data so you have the complete set of columns saved in the $db1 array.

$sql = "select domain_name, renewal_date, f3, f4
            from table1 
            where MONTH(renewal_date) = '06'";

    $result = mysqli_query($link_id,$sql);
    if(!$result) die(sql_error());

    $db1= Array();
    $InList = '';
    while( $row = mysqli_fetch_array($result,MYSQL_ASSOC) ) {
        $InList .= sprintf("'%s',", $row['domain_name']);

        $db1[$row['domain_name']] = $row;  

    }
    $InList = rtrim($InList, ',');

The $db1 array will now look like this:

Array
(
    [abc.com] => Array
        (
            [domain_name] => abc.com
            [renewal_date] => 2015-06-06
            [f3] => aaa
            [f4] => bbb
        )

    [xyz.com] => Array
        (
            [domain_name] => xyz.com
            [renewal_date] => 2015-06-07
            [f3] => ccc
            [f4] => ddd
        )

)

So the domain name is still the KEY to each occurance of the array, but you have another array associated with the key rather than just a single string.

So to access this array you do this to use a domains specific columns.

echo '<td>' . $db1[ $row['d_name'] ] ['renewal_date'] . "</td>";
echo '<td>' . $db1[ $row['d_name'] ] ['f3'] . "</td>";
echo '<td>' . $db1[ $row['d_name'] ] ['f4'] . "</td>";

I hope that is explained well enough to help you.

in this case how can i display domain_name,renewal_date,d_due_date in one table — user3358839, Jun 24 '15 at 08:53
Is it possible that the `db1.table1.renewal_date` will be different to `db2.table2.d_due_date` — RiggsFolly, Jun 24 '15 at 09:02
it gives '0' results. i was trying to see whot is in $InList as var_dump($InList); no results found — user3358839, Jun 24 '15 at 09:29
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/81381/discussion-between-user3358839-and-riggsfolly). — user3358839, Jun 24 '15 at 09:32
hai, if i want to display more columns from db1.table1 how can i do that. can u explain how the array works there — user3358839, Jun 25 '15 at 04:01
Ok I have added to the answer, how to use other fields from table1 — RiggsFolly, Jun 25 '15 at 08:59

score -1 · Answer 2 · edited May 23 '17 at 12:29

-1

First of all, you can do this in just one database with multiple tables which would be nicer and easier to use than you should be able to do something like:

  SELECT domain_name,renewal_date,d_due_date
  FROM table1 INNER JOIN table2
  ON table2.d_name = table1.domain_name
  WHERE MONTH(table1.renewal_date) = '06'";

Something like this(also see this as referance) using where and inner join in mysql

edited May 23 '17 at 12:29

Community

1
1

answered Jun 24 '15 at 08:36

Roy Stijsiger

199
2
12

Yea of course, but the questioner has 2 databases so where is the relevance of this answer – RiggsFolly Jun 24 '15 at 08:38
yes but i have a permission issue where one user cant access two databases. tables are in different databases – user3358839 Jun 24 '15 at 08:46
I guess you can use this at 2 different databases aswell anyway check out this picture it makes thing really clear. http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_V2.png – Roy Stijsiger Jun 24 '15 at 08:47
I think u can use this sample with two databases as well just pointing out it's not usual to have two databases a part(i think) Sorry user(saw your answer just now). Why not using 1 database then? – Roy Stijsiger Jun 24 '15 at 08:50
Why dont u replace table1 with db1.table1 in my example it should work just fine:) – Roy Stijsiger Jun 24 '15 at 09:08

score -1 · Answer 3 · answered Jun 24 '15 at 08:39

-1

I think that due to use of && operator in while loop. There may be one of the table's result is empty and that's why the result set may be empty.

answered Jun 24 '15 at 08:39

Girish

213
2
13

this works fine. but i hav hardcoded the domain names in $sql. i want it to run without hardcoding – user3358839 Jun 24 '15 at 08:47
He has not got that far, the question asked about dynamically building a list for the `IN ()` – RiggsFolly Jun 24 '15 at 08:48

retrieving data from two databases

3 Answers3

RE: Your comment

RE: Using more fields from table1: