Why Doesn't This Work (Multiple Databases)

[jonno946]

I have searched absolutely everywhere and i cannot get this script to work 🙁

i am trying to connect to 2 databases at the same time. I have searched the forums and come up with the following, which i've played around with for so long and it still does not work!!

I am running PHP Version 4.3.7, MySQL version 4.0.20.

Heres my current code:

<?php

$database_connection1 = mysql_connect("localhost", "****", "****") or die ("Could not connect");

$database_connection2 = mysql_connect("localhost", "****", "****") or die ("Could not connect");

mysql_select_db("site_layout", $database_connection1) or die ("Could not select db");

mysql_select_db("vbulletin", $database_connection2) or die ("Could not select db");

// query db1

$get_total_hits = mysql_query("SELECT SUM(hits) FROM page", $database_connection1) or die(mysql_error());

$fetch_row1 = mysql_fetch_row($get_total_hits); 

echo "Total Hits:" . $fetch_row1[0] . "";

mysql_free_result($get_total_hits);

// query db2

$get_total_users = mysql_query("SELECT COUNT(userid) from user", $database_connection2) or die(mysql_error());

$fetch_row2 = mysql_fetch_row($get_total_users); 

echo "Total Hits:" . $fetch_row2[0] . "";

mysql_free_result($get_total_hits);

mysql_close($database_connection1);

mysql_close($database_connection2);

?>

every time the code is executed, it says :

Table 'vbulletin.page' doesn't exist

as if it is completely ignoring the first database connection, and defaulting to the first one.

According to many posts this seems to have worked for other people, why not me?

Thanks

Jonathan

[tsinka]

Hi,

this is a special case because you're connecting to the same server. This will not work since PHP will use one connection internally. Only do one connect and either use mysql_db_query or specify the database in the queries like

SELECT * FROM db.table WHERE ...

Thomas

[Sxooter]

IIRC this is because the mysql_ functions in PHP are pretty brain damaged, and if you use the exact same credentials to crreate a second connection it just reuses the old one, not opening a new one.

In fact, this odd, dangerous, and non-intuitive behavior is documented in the php documentation for the mysql_ extensions:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

[Sxooter]

Note that PostgreSQL suffers the same problem, but, since you choose a database at connect time, not with a seperate selectdb function, this situation would not be a problem there.

The answer for you is to either selectdb as you go back and forth or to create a second username to use to access the database via the other link.

[jonno946]

Originally posted by tsinka
Hi,

this is a special case because you're connecting to the same server. This will not work since PHP will use one connection internally. Only do one connect and either use mysql_db_query or specify the database in the queries like

SELECT * FROM db.table WHERE ...

Thomas

I have already tried selecting the database in my query which failed, but i have just given it another go and got...

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near site_layout.page' at line 1

Here is my code now:

<?php

$database_connection = mysql_connect("localhost", "*****", "*****") or die ("Could not connect");

// query db1

$get_total_hits = mysql_query("SELECT SUM(hits) FROM layout.page", $database_connection) or die(mysql_error());

$fetch_row1 = mysql_fetch_row($get_total_hits); 

echo "Total Hits:" . $fetch_row1[0] . "";

mysql_free_result($get_total_hits);

// query db2

$get_total_users = mysql_query("SELECT COUNT(userid) from vbulletin.user", $database_connection) or die(mysql_error());

$fetch_row2 = mysql_fetch_row($get_total_users); 

echo "Total Hits:" . $fetch_row2[0] . "";

mysql_free_result($get_total_hits);

mysql_close($database_connection);

?>

Any ideas?

Also, mysql_db_query sounds intresting ... i'll look into that now...

Thanks for your help,

Jonno

[jonno946]

Sxooter, sounds a good idea but would useing selectdb as i go along use up server resources?

My main script has roughly 50 functions and an average page load contains roughly 25 queries.

on another note (to save me reposting)

At the moment i am stupidly disconnecting usin mysql_close() and reconnecting for each function. Is there an easier and more resource friendly way of doing this?

Would making my db connection a global variable work then just use selectdb for each function?

Thanks

Jonno

[tsinka]

I can't see site_layout.page in your code (the error message contains site_layout and not layout) ...

But you should get another error if the database doesn't exist.

Thomas

[jonno946]

yeah sorry that should read site_layout, i have changed it for easy readin as it has my web address before it 🙂

Jonno

[tsinka]

Ok,

The syntax in the code looks ok, please post the code attached as file.

Thomas

[Sxooter]

The mysql_connect is what costs the most, so don't do mysql_close then reconnect all over the place. It's far cheaper to just mysql_selectdb than to close / open over and over.

[jonno946]

Here is the file attached as a txt file, tsinka.

Sxooter > thanks for that tip 🙂

Really appreciate all your help 🙂

Jonno

[tasistro]

I believe the solution is simple.

From the php manual

resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]])

So you should change the code to:

$database_connection1 = mysql_connect("localhost", "*", "*",1) or die ("Could not connect");

$database_connection2 = mysql_connect("localhost", "*", "*",1) or die ("Could not connect");

Notice the 1 after the password. This tells php to use a new connection and keep the old. Rather than reuse the old.

Note: The new_link parameter became available in PHP 4.2.0

Another point to remember is that you don't need to mysql_select_db as long as you specify the dbase IN the query string.

For example if I have

dbase1
tableA
tableB

dbase2
tableC
tableB

I can query with

"SELECT count(ID) FROM dbase1.tableA WHERE ..."

then without reselecting dbase

"SELECT count(ID) FROM dbase2.tableC WHERE ..."

Notice how I specify the dbase I'm using inside the query.

[jonno946]

Ahhh this sounds intresting... however i've just given it a go how you suggested and it just brings up a totally blank page?

I am running PHP V 4.3.7 so it should work right?

I would post my current code but i'm at work at the moment, i'll post it tonight when i get home.

Any idea what could be causing this blank screen? My code is exactly how you suggested above?

Thanks

Jonathan

[sanam]

i had the same problem, connecting to 2 databases at same time. I found solution in Adodb. You can use it to connect to two databases using PConnect..

$db_os= NewADOConnection('mysql');
$db_os->PConnect("localhost", "os", "os", "oscommerce");

$db_fr= NewADOConnection('mysql');
$db_os->PConnect("localhost", "fr", "fr", "forums");

[jonno946]

thanks for that tip i may well have a look into that🙂

tasistro, any idea why this isn't working as you suggested? Just brings up a plain blank page for some reason?

<?php

// test.php

$database_connection1 = mysql_connect("localhost", "*****", "*****",1) or die(mysql_error());

$database_connection2 = mysql_connect("localhost", "*****", "*****",1) or die(mysql_error());

// query db1

$get_total_hits = mysql_query("SELECT SUM(hits) FROM pite_layout.page", $database_connection1) or die(mysql_error());

$fetch_row1 = mysql_fetch_row($get_total_hits); 

echo "Total Hits:" . $fetch_row1[0] . "";

mysql_free_result($get_total_hits);

// query db2

$get_total_users = mysql_query("SELECT COUNT(userid) from vbulletin.user", $database_connection2) or die(mysql_error());

$fetch_row2 = mysql_fetch_row($get_total_users); 

echo "Total Users:" . $fetch_row2[0] . "";

mysql_free_result($get_total_hits);

mysql_close($database_connection1);

mysql_close($database_connection2);

?>

Thanks again,

Jonno.

[Sxooter]

You've likely got error reporting turned off, try turning it on and see if you get an error message that helps:

error reporting

[jonno946]

just gave that ago - still no errors, just a blank page 😕

Jonno

[Sxooter]

Did you turn on error reporting in php.ini? If so, you have to restart your web server to get the change to take effect. Check phpinfo() output to see if it really is turned on.

[jonno946]

I am not running PHP locally. only my web host is able to do this.

I tried putting the following at the top of my script:

error_reporting(E_ALL);

That should works shouldnt it?

Jonno

[Sxooter]

I'm not sure. I think that if you're in safe mode you can't change certain things. Read through that error reporting page and see what it says.