MySQL connection within a PHP function overwrites existing connection (other database) - how can I prevent this?

Question

We open a MySQL connection to host_1 at the beginning of our script. Somewhere in between, we call a function that opens & closes a connection to host_2 (function retrieve_information_from_host_2).

Unfortunately, the code after the function call still uses the connection of the function (host_2).

We need it though to continue with the preceding connection host_1.

<?php

function retrieve_information_from_host_2($host_2, $username_2, $password_2, $database_2) {
    $connection = mysql_connect($host_2, $username_2, $password_2);
    if($connection) {
        if(mysql_select_db($database_2)) {
            $sql = "SELECT * FROM table WHERE id = 1;";
            $erg = mysql_query($sql);
            $row = mysql_fetch_assoc($erg);

            return $row;
        }
        mysql_close($connection);
    }
}

if(mysql_connect($host_1, $username_1, $password_1)) {
    if(mysql_select_db($database_1)) {
        $sql = "SELECT * FROM table WHERE id = 1;";
        $erg = mysql_query($sql);
        $row = mysql_fetch_assoc($erg); # CORRECT - This returns data from host 1

        $row_host_2 = retrieve_information_from_host_2(); # CORRECT - This returns data from host 2

        $sql = "SELECT * FROM table WHERE id = 2;";
        $erg = mysql_query($sql);
        $row = mysql_fetch_assoc($erg); # WRONG - This returns data from host 2 instead of host 1
    }
    mysql_close();
}
?>

We have tried almost every combination by giving each connection a name

$connection_1 = mysql_connect($host_1, $username_1, $password_1);
...
mysql_close($connection_1);

and closing them explicitly, etc.

Nothing helps. Can someone give us a solution to this problem?

PS: We are bound to this approach (functions) and can't use classes.

Thank you very much!

Inside of the interrupting functions, have you tried closing the connection you don't want to use, and then reopening it at the end of the function? — davepmiller, Oct 19 '12 at 17:00

score 4 · Answer 1 · answered Oct 19 '12 at 17:06

4

See manual for mysql_query for second parameter link_identifier (The MySQL connection)

If the link identifier is not specified, the last link opened by mysql_connect() is assumed.

You also should use mysqli instead of the old mysql.

answered Oct 19 '12 at 17:06

slaakso

8,331
2
16
27

score 0 · Accepted Answer · answered Oct 19 '12 at 17:00

Try to pass your connection link identifier as second argument.

For example:

$connection1 = mysql_connect($host_1, $username_1, $password_1);
$connection2 = mysql_connect($host_2, $username_2, $password_2);
mysql_query('Query',$connection1);
mysql_query('Query',$connection2);

MySQL connection within a PHP function overwrites existing connection (other database) - how can I prevent this?

2 Answers2