Multiple DB Server Connection

[Yardboy]

Just a question about how others would handle this issue:

function accesses LOCAL database for records in cart table.

Runs a while loop for each LOCAL record in cart table

WITHIN that loop, a new REMOTE db connection is established and writes a record to a REMOTE DB.

So, basically, I have a function that is bouncing back and forth between database servers, local and remote.

How, in a perfect world, would one handle this?

Heres how i'm doing it now, but i feel like its kludgy:

$dbh = connect_to_db_local();
where loop
write_order record($data_to_write); - this function starts with $dbh = connect_to_db_remote(); and ends with mysql_close();

is this the only way to handle this, or is there a more graceful way? if you guys need the whole code, i'll post it.

[digitalv]

What type of database are you using? If it's MS SQL or Oracle (not mySQL), you can link two servers together and write a stored procedure that does this for you or you could use replication to move copies of the data to the remote server in real-time or at regular intervals. You would only need to execute the procedure. That's the most efficient way of doing it, but assuming those resources aren't available the way you're doing it now is the only way. Just make sure your script doesn't time out.

[jollymcfats]

If you're fairly certain you'll have to update the remote db, you can speed things up considerably by opening and closing the remote connection outside of the local database loop.

If you want to get fancy, you could delay your remote update requests until you've finished the while loop, then submit them all at once. If they're INSERTs, you might use a MySQL bulk insert statement. Something like:

$ordersToWrite = array();
open_local_db();
while (localResults...) {
  $ordersToWrite[] = array(orderid => '...', customerid => '...', ...);
}
close_local_db();
if (! empty($ordersToWrite)) {
  $sql = 'INSERT INTO ORDERS (orderid, customerid) VALUES ';
  $bulk = array();
  foreach ($ordersToWrite as $row) {
    $bulk[] = " ($row['orderid'], $row['customerid']) "; 
  }
  $sql .= implode(',', $bulk);
  open_remote_db()..
  //etc.
}

[Yardboy]

its a mySQL db, so, unfortunately, i can't create a procedure. however, jollys idea may work for this application. there will always be an order to write.

i'll give it a shot and let you know. thank you for the input!

[coopster]

Stored procedures and functions [dev.mysql.com] are a new feature in MySQL version 5.0.

>>Just make sure your script doesn't time out.

This would be my biggest concern as well. Maybe make your local script a transactional operation and if the remote write returns successfully, COMMIT the local transaction.