LAMP settings – PHP and multiple DB connections with mysql_connect()

Question

I was assigned to maintain prehistoric PHP application, which uses some 'phpDB' class written by Joe Thong, last updated in 1999. This application connects to two different databases on the same server, taking some data from one and another from the second one.

Now, the limit of mysql_connect is, that it uses the same connection resource for new connections. Therefore, if I use following code:

$db1 = new phpDB()->connect(/* db1data, database 'one' */);
$db2 = new phpDB()->connect(/* db2data, database 'one' */);
$data = $db1->query($somequery);

EDIT NOTE: new phbDB()->connect just sets some internal values and does standard mysql_connect without $new_link parameter.

Now, the problem is, that the $query is run over the database two, because it has rewritten previous connection.

This can be solved by using true as fourth parameter in mysql_connect. The thing is, I would rather not rewrite something in 12 years old library (because of pure fear of how it will react on live server), and also it works without that fourth parameter on live server. However, me not being server guru, I was unable to locate the proper directive in the server configuration to switch on my local MAMP configuration, to be closer to emulate live enviroment.

Can anyone help me? Thank you.

EDIT: wrapper itself:
phpDB.php – db wrapper – http://scrp.at/wd
phpDB-mysql.php – mysql specific code – http://scrp.at/we

What's your goal? Adding new queries to the app? Since you said your two databases are on the same server, perhaps it's enough to use one call to `mysql_connect()` and thereafter use `mysql_select_db()`? — chelmertz, Feb 15 '11 at 14:53
I think you have this wrong somehow. You should not be able to do `$db1->query`. `mysql_connect()` returns an ID to a database connection. From there you can run `mysql_query("SELECT...", $db1);`, and the second parameter specifies the database. Or am I missing something here? — Brad, Feb 15 '11 at 14:55
My goal is to recreate live enviroment this app runs in as close as possible without rewriting every db using file OR that prehistoric db wrapper. — Adam Kiss, Feb 15 '11 at 14:57
@Brad: I have used standard PHP code although I stated that app is using very old DB wrapper. I edited the code to closer resemble what it does. — Adam Kiss, Feb 15 '11 at 15:01
@Adam, that makes much more sense now... those details were important. Thank you for correcting it. — Brad, Feb 15 '11 at 15:08
@Adam, looking at this source code, it is using `_connectionID` appropriately in the execute function. I don't however see your `query` function. — Brad, Feb 15 '11 at 15:19
@Brad: well, the execute does the standard mysql_query on line 189. Whatever, I'll set `$new_link` to `'true'` and hope it'll work on live server. Thanks Brad — Adam Kiss, Feb 15 '11 at 15:23
`$new_link` won't be quite what you are looking for. Where is the `query` method itself? Yes, I understand that `execute` calls `mysql_query`, but that one is fine. I suspect wherever `query` is defined in this class, they left out `_connectionID`. — Brad, Feb 15 '11 at 15:30

score 2 · Accepted Answer · answered Feb 15 '11 at 15:12

The way to fix this is by modifying that query method.

Somewhere inside it is calling mysql_query(). All you have to do is add a second parameter which includes a reference to the internal variable containing the MySQL connection reference.

This is speculation because you haven't posted any of the code, but I think you will see something like this:

Old

function query($thequery) {
    mysql_query($thequery);
    ....
}

Change it to this

function query($thequery) {
    mysql_query($thequery, $this->dbreference);
    ....
}

Whatever "dbreference" is called in your class, I have no idea. Look for whatever variable is set when mysql_connect is called.

it's called $this->_referenceID and it's reused multiple times. — Adam Kiss, Feb 15 '11 at 15:18

Michiel Pater · Answer 2 · 2011-02-15T14:58:18.120

1

Have a look at the following pages:

http://nl.php.net/manual/en/function.mysql-connect.php

http://nl.php.net/manual/en/function.mysql-query.php

By filling the new_link parameter you can add a new connection.

For example:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password', 'link1');
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password', 'link2');

mysql_query($query, 'link1'); //query on database 1
mysql_query($query, 'link2'); //query on database 2

edited Feb 15 '11 at 14:58

answered Feb 15 '11 at 14:52

Michiel Pater

22,377
5
43
57

I know about this possibility, the problem is, that `mysql_connect` is wrapped in 12 years old open source db class, and I'm just scared I'll break something. – Adam Kiss Feb 15 '11 at 14:56
I think you will be fine. I use this myself. It has not been deprecated yet :-) – Michiel Pater Feb 15 '11 at 15:00
I'm not scared of the parameter itself, rather what it does with that old wrapper and server configuration that allows something, that shouldn't be possible (i.e. connecting to multiple DBs without $new_link data). – Adam Kiss Feb 15 '11 at 15:03
"scared" .. its just a little code, make a buckup of the file and edit, if you mess up just overwrite it again and all is well. stop being a baby :) – RobertPitt Feb 15 '11 at 15:24

LAMP settings – PHP and multiple DB connections with mysql_connect()

2 Answers2