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.

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.

Answer 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

Answer 2

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.

Answer 3

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.