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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tomex1
Forum Commoner
Posts: 26
Joined: Sun Aug 16, 2009 11:34 am

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

Post 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.
AlanG
Forum Contributor
Posts: 136
Joined: Wed Jun 10, 2009 1:03 am

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

Post 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.
tomex1
Forum Commoner
Posts: 26
Joined: Sun Aug 16, 2009 11:34 am

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

Post 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.
AlanG
Forum Contributor
Posts: 136
Joined: Wed Jun 10, 2009 1:03 am

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

Post 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.
tomex1
Forum Commoner
Posts: 26
Joined: Sun Aug 16, 2009 11:34 am

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

Post 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
AlanG
Forum Contributor
Posts: 136
Joined: Wed Jun 10, 2009 1:03 am

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

Post 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.
Post Reply