Two mysql connections

[elyob]

Not really tried going two ways at once, but I have an include_once
connection to a mysql_database, now I need to retrieve info from a second
mysql_database ..

My mysql_connects are getting confused. So, (I've had a couple of beers), am
I opening up too many at once?

I'll look again in the morning, but am thinking that it is a bad idea to
leave two connections open all the time ...

Anyone able to advise?

Thanks

[Gordon Burditt]

Re: Two mysql connections

>Not really tried going two ways at once, but I have an include_once[color=blue]
>connection to a mysql_database, now I need to retrieve info from a second
>mysql_databa se ..[/color]

Connections aren't include_once. You may be including the code to make
the connection with include_once, but that doesn't affect the connection.
[color=blue]
>My mysql_connects are getting confused. So, (I've had a couple of beers), am
>I opening up too many at once?[/color]

No. I had a page that opened up 3 connections (in this case, on 3
different servers) to compare the data on the 3 servers. Essentially,
it was a test for a data migration script (which also involved a
lot of reorganization) to check if anything got missed. It proved
very useful. Performance and simultaneous use weren't issues, and
only I used it.

You should pass the correct connection handle (er, "resource") to
all of the mysql_* functions to indicate which one you wish to use,
including mysql_query, mysql_select_db , and functions to fetch
results. If you don't pass the connection handle, PHP will pick
one (I forget whether it's the first or the latest), and Murphy's
law says it gets the wrong one more than 50% of the time.
[color=blue]
>I'll look again in the morning, but am thinking that it is a bad idea to
>leave two connections open all the time ...[/color]

Leaving two connections open during processing a PHP page is not
"all the time" unless you've got a heck of a lot of traffic.

Persistent connections tend to accumulate a large number of idle
connections, perhaps more than the server can handle. You also
tend to end up with the MAXIMUM number of connections (as set in
Apache by child_max) rather than the average number of simultaneous
connections actually being used).

Assuming, for the moment, that generating the PHP page takes less
than, say, 1 minute, you should hold the connections for as long
as you need them rather than disconnecting and re-connecting. That's
probably the whole time unless you are doing complicated manipulation
of the data after you fetch it before presenting it. If it takes
longer than that, how do you put up with that poor response?

Gordon L. Burditt

[elyob]

Re: Two mysql connections


"Gordon Burditt" <gordonb.gyj55@ burditt.org> wrote in message
news:128ul74nrk va8a8@corp.supe rnews.com...[color=blue][color=green]
> >Not really tried going two ways at once, but I have an include_once
>>connection to a mysql_database, now I need to retrieve info from a second
>>mysql_databas e ..[/color]
>
> Connections aren't include_once. You may be including the code to make
> the connection with include_once, but that doesn't affect the connection.
>[color=green]
>>My mysql_connects are getting confused. So, (I've had a couple of beers),
>>am
>>I opening up too many at once?[/color]
>
> No. I had a page that opened up 3 connections (in this case, on 3
> different servers) to compare the data on the 3 servers. Essentially,
> it was a test for a data migration script (which also involved a
> lot of reorganization) to check if anything got missed. It proved
> very useful. Performance and simultaneous use weren't issues, and
> only I used it.
>
> You should pass the correct connection handle (er, "resource") to
> all of the mysql_* functions to indicate which one you wish to use,
> including mysql_query, mysql_select_db , and functions to fetch
> results. If you don't pass the connection handle, PHP will pick
> one (I forget whether it's the first or the latest), and Murphy's
> law says it gets the wrong one more than 50% of the time.[/color]

Firstly, thanks for the really prompt reply!

Okay, this is where I am making a mistake.

I have two files that use the same code ... i.e.

<?php
$dbhost = "server1.co m";
$dbname = "server1_dbname ";
$dbuser = "server1_dbuser ";
$dbpass = "123";

$link=mysql_con nect ("$dbhost", "$dbuser", "$dbpass") ;
@mysql_select_d b ("$dbname");
?>

I now have a second file called connecttodb.php which has same info, but
different server (i.e. all db stuff is different).

How should I differentiate between the two files when doing calling the
database? i.e. should I call SELECT * FROM file1.tablename etc?

Thanks

Nick

[Gordon Burditt]

Re: Two mysql connections

><?php[color=blue]
>$dbhost = "server1.co m";
>$dbname = "server1_dbname ";
>$dbuser = "server1_dbuser ";
>$dbpass = "123";
>
>$link=mysql_co nnect ("$dbhost", "$dbuser", "$dbpass") ;[/color]

You see that value $link returned by mysql_connect?
It's a connection handle. Or, if you prefer, a resource link_identifier .
[color=blue]
>@mysql_select_ db ("$dbname");[/color]

You pass the connection handle to functions that use the connection, like:

mysql_select_db ("$dbname", $link);
[color=blue]
>?>
>
>I now have a second file called connecttodb.php which has same info, but
>different server (i.e. all db stuff is different).[/color]

Hopefully it assigns to something different from $link (like $link2).
[color=blue]
>How should I differentiate between the two files when doing calling the
>database? i.e. should I call SELECT * FROM file1.tablename etc?[/color]

$resultset = mysql_query("SE LECT * FROM tablename", $link2);

Those optional link_identifier arguments aren't optional in your situation.

Gordon L. Burditt

[elyob]

Re: Two mysql connections


"Gordon Burditt" <gordonb.8tdzy@ burditt.org> wrote in message
news:128unv4mi4 66v94@corp.supe rnews.com...
[color=blue]
> Hopefully it assigns to something different from $link (like $link2).[/color]

Thanks, I've decided to use more clear names :)
[color=blue]
>[color=green]
>>How should I differentiate between the two files when doing calling the
>>database? i.e. should I call SELECT * FROM file1.tablename etc?[/color]
>
> $resultset = mysql_query("SE LECT * FROM tablename", $link2);[/color]

Absolutely spot on. Not something I've needed to have do before now, as all
my databases have been stored in the same place. Now I am needing to keep a
central database for different projects ...

!! Your advice has been very useful! !!
[color=blue]
> Those optional link_identifier arguments aren't optional in your
> situation.[/color]

:)

Thanks again ....

Nick