Page 1 of 1

How to pull data from one database to another? Please help

Posted: Sun Aug 16, 2009 11:40 am
by tomex1
Hi all,
I was wondering if anyone could tell me how to go about this - I am trying to select data from one database A and insert it into another database B. Database A is a DB2 database while B is Derby (if that helps). At the moment, I can get the data from A and display it on php page. The problem I have is how to go about inserting the data from A into B.

Here is the code that displays the returned resullts

Code: Select all

 
 if (is_array($sql)) {
                     foreach ($sql as $row) {
                     print "\n<tr>";
                         foreach ($row as $columnName => $value) {
                               populate($row[$columnName]);
                             if ("cust_no" === $columnName)
                             {
                                  
                                  print "\n\t<td nowrap><a href=\"lookup_link.php?$columnName=$row[$columnName]\">$row[$columnName]</a></td>";      
                                 print "\n";
                              }
                              else
                              {
                                      print "\n\t<td nowrap>$row[$columnName]</td>";      
                                     print "\n";
                               }        
                        }
                        print "\n</tr>";
                    }
                        print "\n</table>\n";
                }
                else if (is_null($sql)) {
                // Error
                echo "No";
            }
 

My main objective for this is, I will be comparing B from A in future to pass a visual message to the user whenever a field changes in A. Any information you can give me would be greatly appreciated.

Kind regards.

Re: How to pull data from one database to another? Please help

Posted: Sun Aug 16, 2009 8:01 pm
by AlanG
The easiest way to accomplish this is to connect to database A, retrieve the data, close the db connection, connect to database B and then insert it.

I'm going to assume you are using MySQL, if not i'm sure there's an equivalent feature on the database you are using. Have a look at MySQL federated tables. They allow one table to access another table on a different host. In my opinion it is best to do the transfers from db to db, without having to rely on a outside language for processing.

Re: How to pull data from one database to another? Please help

Posted: Mon Aug 17, 2009 3:09 am
by tomex1
Hi Alan,
Many thanks for your reply. I have managed to pull the data from database A into database B. I also agree with what your said about the two databases communicating with each other rather than having a script to do that and I will be looking into that considering the fact that they are two different databases (Db2 and Derby respectively).

For now, do you know how I can compare the records in the two DBs from my php. I can't seem to get my head round this and I'll be glad if you can point me to the right direction with this.

Kind regards.

Re: How to pull data from one database to another? Please help

Posted: Mon Aug 17, 2009 8:47 am
by AlanG
Well again, you can pull the data from database A, then pull the data from database B and then compare them to each other. You should create a function to switch databases, would make things alot handier.

eg.

Code: Select all

 
<?php
    function switch_db() {
        // Check which database your connected to.
        // if A then switch to B,
        // if B then switch to A,
        // otherwise switch to the default db (if not connected to any database)
    }
 
    // So you could easily do this:
 
    $dataA = get_data();
    switch_db();
    $dataB = get_data();
 
    if(!compare_data($dataA,$dataB)) {
        // Data doesn't match, do an action
    }
?>
 
Note: none of the above functions actually exist, it is only pseudo code.

Re: How to pull data from one database to another? Please help

Posted: Mon Aug 17, 2009 4:19 pm
by tomex1
Thanks a lot, I amanaged to get that working. One problem I had is with a field that had apostrophe in it. I used the addslashes() method but I am still getting error on this
field despite the fact that the addslashes() method actually added the escape slash.
Any thoughts as to why this isn't working?

Thanks

Re: How to pull data from one database to another? Please help

Posted: Mon Aug 17, 2009 10:38 pm
by AlanG
Yeah, dont addslashes, use a mysql_real_escape function. That will solve your problem. :)

e.g.

Code: Select all

 
$database = new MySQLi(HOST,USER,PASS,NAME);
 
$fname = 'john';
$lname = 'smith';
 
// Escape the data
$fname = $database->real_escape_string($fname);
$lname = $database->real_escape_string($lname);
 
$query = "Select id,fname,lname From customers Where fname = '$fname' And lname = '$fname'";
 
if($result = $database->query($query)) {
    // Handle the result
}
else
    die("Error with query");
 
You can use the mysql_real_escape_string() or mysqli_real_escape_string() also depending on your current setup.