php - disconnecting and connecting to multiple databases

Question

I want to be able to switch from the current db to multiple dbs though a loop:

$query = mysql_query("SELECT * FROM `linkedin` ORDER BY id", $CON ) or die( mysql_error() );
if( mysql_num_rows( $query ) != 0 ) {
    $last_update = time() / 60;
    while( $rows = mysql_fetch_array( $query ) ) {
        $contacts_db = "NNJN_" . $rows['email'];
        // switch to the contacts db
        mysql_select_db( $contacts_db, $CON );
        $query = mysql_query("SELECT * FROM `linkedin` WHERE token = '" . TOKEN . "'", $CON ) or die( mysql_error() );
        if( mysql_num_rows( $query ) != 0 ) {
            mysql_query("UPDATE `linkedin` SET last_update = '{$last_update}' WHERE token = '" . TOKEN . "'", $CON ) or die( mysql_error() );   
        }else{
            mysql_query("INSERT INTO `linkedin` (email, token, username, online, away, last_update) VALUES ('" . EMAIL . "', '" . TOKEN . "', '" . USERNAME . "', 'true', 'false', '$last_update')", $CON ) or die( mysql_error() );
        }
    }
    mysql_free_result( $query );
}
// switch back to your own
mysql_select_db( USER_DB, $CON );

It does insert and update details from the other databases but it also inserts and edits data from the current users database which I dont want. Any ideas?

Wow, I can only imagine how much bandwidth this is wasting... — animuson, Mar 25 '10 at 06:03
Please re-title your question. You are not "disconnecting and connecting" to multiple databases. You are merely changing the schema. — Felix, Mar 25 '10 at 07:07
Also, you have separate DATABASES for each email? Wow. That's really poor database modelling. Also, if it helps, the `mysql_fetch_array()` call in the `while()` statement will have some kind of undefined behavior starting from cycle 2, because it's in a different schema than it was before. You should do two loops: in the first one you fetch everything from your main DB and store it in an array and in the second one you process it. — Felix, Mar 25 '10 at 07:13

score 2 · Accepted Answer · answered Mar 25 '10 at 13:58

2

Never use the php mysql_select_db() fundtion - as you've discovered the code (and the coder) gets very confused very quickly.

Explicitly state the DB in the queries:

SELECT * FROM main_database.a_table....

UPDATE alternate_db.a_table SET...

REPLACE INTO third_db.a_table...

C.

answered Mar 25 '10 at 13:58

symcbean

47,736
6
59
94

It's not the answer due to I restarted :-) BUT this seems to be useful information on what I was after so thank you. – Phil Jackson Mar 26 '10 at 04:32
BTW - looking at the code, I think this could all be done with a couple of SQL statements on the DBMS (using the database_name.table_name construct) without having to pull the data into PHP / using a loop. – symcbean Mar 26 '10 at 12:12

score 1 · Answer 2 · answered Mar 25 '10 at 06:04

1

You're probably have wrong database design.

answered Mar 25 '10 at 06:04

Your Common Sense

156,878
40
214
345

score 1 · Answer 3 · answered Mar 25 '10 at 07:00

1

one improve that i see is that you can use one query to duplicate or update

the syntax is like :

INSERT INTO mytable (field_list.....) VALUES (values_list...) 
    ON DUPLICATE KEY 
UPDATE field1 = val1 ...

answered Mar 25 '10 at 07:00

Haim Evgi

123,187
45
217
223

score 0 · Answer 4 · answered Mar 25 '10 at 14:07

0

you are reassigning $query during your while loop. this will give strange results. use $query2 for the query inside the loop

answered Mar 25 '10 at 14:07

SteelBytes

6,905
1
26
28

php - disconnecting and connecting to multiple databases

4 Answers4