Multiple db connections in a single script


#1

Does this sound like a dreamhost problem?

We did a rewrite of our site. we are trying to run a script called import.php. Basically populates the new table and pulls in the data from the old table. So we need 2 connections to pull this off.

I did some testing and it looks like this has something to do with accessing multiple databases from the same script. I’m guessing this has something to do with your hosting provider. Even though the script saves each connection to a different identifier ($oldDb and $newDb) it is only querying the $newDb because that’s the last one connected to. If you go down to the first section to pull data over you should see a query that looks like “select * from countries”. If you output a mysql_num_rows() after that query you’ll get 0. There is a countries table in the new database but it is initially empty. So to confirm my theory I picked a table in the new database that was not initially empty. Change the table in that query from countries to bats and now mysql_num_rows() will show 3. We can also tell this is the case because there is no bats table in the old database. So does DreamHost limit you to 1 database connection per script?


#2

No, we don’t do anything weird like that. Chances are that your script just isn’t switching between databases correctly — is there any chance you can post it (or some excerpts) here?


#3

Here are the first 50 lines of the import.php for the rewrite.

<? $start = time(); $oldDb = mysql_connect("host", "user", "pw"); mysql_select_db("olddb", $oldDb); $newDb = mysql_connect("host", "user", "pw"); mysql_select_db("newdb", $newDb); ?>

<!doctype html>

<? $tables = "database/tables" ?> <? if ($files = scandir($tables)) { ?> <? foreach ($files as $file) { ?> <? if (($file != ".") && ($file != "..")) { ?> <? $queries = explode(";", file_get_contents("$tables/$file")) ?> <? foreach ($queries as $query) { ?> <?= $sql = $query ?>
<? mysql_query($sql, $newDb) ?> <? } ?> <? } ?> <? } ?> <? } ?>
<? $views = "database/views" ?> <? if ($files = scandir($views)) { ?> <? foreach ($files as $file) { ?> <? if (($file != ".") && ($file != "..")) { ?> <? $queries = explode(";", file_get_contents("$views/$file")) ?> <? foreach ($queries as $query) { ?> <?= $sql = $query ?>
<? mysql_query($sql, $newDb) ?> <? } ?> <? } ?> <? } ?> <? } ?> <? if (! array_key_exists("flush", $_REQUEST)) { ?>
<?= $sql = "select * from countries" ?>
<? if ($countries = mysql_query($sql, $oldDb)) { ?> <? $oldCountries = array() ?> <? while ($country = mysql_fetch_array($countries)) { ?> <?= $sql = "insert into countries (name, description) values ('" . mysql_real_escape_string($country["nickname"]) . "', '" . mysql_real_escape_string($country["name"]) . "')" ?>
<? if (mysql_query($sql, $newDb)) { ?> <? $oldCountries[$country["id"]] = mysql_insert_id($newDb) ?> <? } ?> <? } ?> <? } ?>

#4

Hmm, as far as I can tell that all looks correct. I’m not sure what might be causing the problem.