Multiple instances of mysql_connect() in single PHP document.

Hello,

Is it wrong to have mysql_connect() called twice within a single PHP
document, such as this? I have been running into some fairly annoying
query failures that commute when I change the order of these two blocks of
code. I am currently working around this by repeatedly using
mysql_select_db() to change the databases as I am needing to change to a
different database.

To be more specific, I can use the $db_material handle in doing a query,
but it will always try to search in the Labor_Log table and thus fail
because the tables have different and names. Swapping these blocks will
have a vice-versa effect.


//Connect to server (materials)
$db_material = mysql_connect("localhost", "root", "secret");
if (!$db_material)
{
echo( "

Unable to connect to the database server at this
time.

Unable to connect to the database server at this
time.

Re: Multiple instances of mysql_connect() in single PHPdocument.

Timothy Legg wrote:
> Hello,
>
> Is it wrong to have mysql_connect() called twice within a single PHP
> document, such as this? I have been running into some fairly annoying
> query failures that commute when I change the order of these two blocks of
> code. I am currently working around this by repeatedly using
> mysql_select_db() to change the databases as I am needing to change to a
> different database.
>
> To be more specific, I can use the $db_material handle in doing a query,
> but it will always try to search in the Labor_Log table and thus fail
> because the tables have different and names. Swapping these blocks will
> have a vice-versa effect.

I assume you mean "database" and not "table". A database can contain
many tables and there is no issue with querying multiple tables in a
single database.

There is no issue with connecting to multiple databases in the one script.


>
> //Connect to server (materials)
> $db_material = mysql_connect("localhost", "root", "secret");

> //Connect to server (labor)
> $db_labor = mysql_connect("localhost", "root", "secret");

So now when you use mysql_query you should pass in the right link to use:

$result = mysql_query($sql, $db_material);

or

$result = mysql_query($sql, $db_labor);


--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Multiple instances of mysql_connect() in single PHP document.

That is great for me to keep in mind for future reference. However, is
it ever appropriate to connect to the database twice in a single script.
Two ways I can think of doing this would be:
>>CASE 1<<

//Connect to server (materials)
$db_material =3D mysql_connect("localhost", "root", "secret");

Some code....

//Connect to server (materials)
$db_material =3D mysql_connect("localhost", "root", "secret");

>>CASE 2<<

//Connect to server (materials)
$db_material =3D mysql_connect("localhost", "root", "secret");

....Code to close connection here ....

Some code...

//Reconnect to server
//Connect to server (materials)
$db_material =3D mysql_connect("localhost", "root", "secret");

I don't see why you would use the first case, but would it cause issues
if you did?

In the second example, the connection was closed in part of a script and
connect to the database again comes later in the script.

Is this "allowable"?

-----Original Message-----
From: Chris [mailto:dmagick@gmail.com]=20
Sent: Tuesday, November 10, 2009 4:51 PM
To: php_list@timothylegg.com
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Multiple instances of mysql_connect() in single
PHP document.

Timothy Legg wrote:
> Hello,
>=20
> Is it wrong to have mysql_connect() called twice within a single PHP
> document, such as this? I have been running into some fairly annoying
> query failures that commute when I change the order of these two
blocks of
> code. I am currently working around this by repeatedly using
> mysql_select_db() to change the databases as I am needing to change to
a
> different database.
>=20
> To be more specific, I can use the $db_material handle in doing a
query,
> but it will always try to search in the Labor_Log table and thus fail
> because the tables have different and names. Swapping these blocks
will
> have a vice-versa effect.

I assume you mean "database" and not "table". A database can contain=20
many tables and there is no issue with querying multiple tables in a=20
single database.

There is no issue with connecting to multiple databases in the one
script.


>=20
> //Connect to server (materials)
> $db_material =3D mysql_connect("localhost", "root", "secret");

> //Connect to server (labor)
> $db_labor =3D mysql_connect("localhost", "root", "secret");

So now when you use mysql_query you should pass in the right link to
use:

$result =3D mysql_query($sql, $db_material);

or

$result =3D mysql_query($sql, $db_labor);


--=20
Postgresql & php tutorials
http://www.designmagick.com/


--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Confidentiality Notice:=20
This e-mail and any attachments may contain confidential information =
intended solely for the use of the addressee. If the reader of this =
message is not the intended recipient, any distribution, copying, or use =
of this e-mail or its attachments is prohibited. If you received this =
message in error, please notify the sender immediately by e-mail and =
delete this message and any copies. Thank you.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Multiple instances of mysql_connect() in single PHPdocument.

Nehemias Duarte wrote:
> That is great for me to keep in mind for future reference. However, is
> it ever appropriate to connect to the database twice in a single script.
> Two ways I can think of doing this would be:
>>> CASE 1<<
>
> //Connect to server (materials)
> $db_material = mysql_connect("localhost", "root", "secret");
>
> Some code....
>
> //Connect to server (materials)
> $db_material = mysql_connect("localhost", "root", "secret");
>
>>> CASE 2<<
>
> //Connect to server (materials)
> $db_material = mysql_connect("localhost", "root", "secret");
>
> ...Code to close connection here ....
>
> Some code...
>
> //Reconnect to server
> //Connect to server (materials)
> $db_material = mysql_connect("localhost", "root", "secret");
>
> I don't see why you would use the first case, but would it cause issues
> if you did?
>
> In the second example, the connection was closed in part of a script and
> connect to the database again comes later in the script.
>
> Is this "allowable"?

It's allowable - whether it's the right thing or not is another question :)

Ideally you'd only connect to a particular database once (the original
example was connecting to two different databases).

If you don't close the connection explicitly, php will do it at the end
of the script as part of it's internal 'cleanup' processing - so you
don't need to have a mysql_close() at the end of the script. This is
good because in some complicated scripts (or cms'es) it'd be pretty hard
to work out where the script "ends" and make sure there is a db close
call in all the right spots.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Multiple instances of mysql_connect() in single PHP

On Tue, Nov 10, 2009 at 4:14 PM, Timothy Legg wr=
ote:
>
> Hello,
>
> Is it wrong to have mysql_connect() called twice within a single PHP
> document, such as this? =A0I have been running into some fairly annoying
> query failures that commute when I change the order of these two blocks o=
f
> code. =A0I am currently working around this by repeatedly using
> mysql_select_db() to change the databases as I am needing to change to a
> different database.
>
> To be more specific, I can use the $db_material handle in doing a query,
> but it will always try to search in the Labor_Log table and thus fail
> because the tables have different and names. =A0Swapping these blocks wil=
l
> have a vice-versa effect.
>
>
> =A0 =A0 =A0 =A0//Connect to server (materials)
> =A0 =A0 =A0 =A0$db_material =3D mysql_connect("localhost", "root", "secre=
t");
> =A0 =A0 =A0 =A0if (!$db_material)
> =A0 =A0 =A0 =A0{
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0echo( "

Unable to connect to the databas=
e server at this
> time.

Unable to connect to the databas=
e server at this
> time.

Re: Multiple instances of mysql_connect() in single PHP

2009/11/11 Bastien Koert :
> On Tue, Nov 10, 2009 at 4:14 PM, Timothy Legg =
wrote:
>>
>> Hello,
>>
>> Is it wrong to have mysql_connect() called twice within a single PHP
>> document, such as this?  I have been running into some fairly annoy=
ing
>> query failures that commute when I change the order of these two blocks =
of
>> code.  I am currently working around this by repeatedly using
>> mysql_select_db() to change the databases as I am needing to change to a
>> different database.
>>
>> To be more specific, I can use the $db_material handle in doing a query,
>> but it will always try to search in the Labor_Log table and thus fail
>> because the tables have different and names.  Swapping these blocks=
will
>> have a vice-versa effect.
>>
>>
>>        //Connect to server (materials)
>>        $db_material =3D mysql_connect("localhost", "=
root", "secret");
>>        if (!$db_material)
>>        {
>>                echo( "

Unable =
to connect to the database server at this
>> time.

Unable =
to connect to the database server at this
>> time.

Re: Multiple instances of mysql_connect() in single PHP document.

Hi,

>
> If the databases are in the same mysql server, then you could qualify
> the table select with the database name and simply re-use the
> connection
>
> select db_name.table_name.field from db_name.table_name [where]

No offence, but if I saw this in an application's source code, I'd run
a mile.

The negligible overhead of simply making two connections is far better
in my opinion than having to rewrite each query if the database name
changed, or to point an application at a copy of the original database
with a different name for testing. At least with the dual-connection
approach, you'd only have to change it once.

Of course in a full application, you'd want the two database
connection details in a config file, so the databases could even be
located on separate servers, and you wouldn't need to make a source
code change to point the application to another database.

Regards,
Andy

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Multiple instances of mysql_connect() in single PHP document.

Andy Shellam (Mailing Lists) wrote:
> Hi,
>
>>
>> If the databases are in the same mysql server, then you could qualify
>> the table select with the database name and simply re-use the
>> connection
>>
>> select db_name.table_name.field from db_name.table_name [where]
>
> No offence, but if I saw this in an application's source code, I'd run a
> mile.

Plus the assumption that they are on the same server and that the user
you're connecting with has access to both databases..

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Multiple instances of mysql_connect() in single PHP

2009/11/11 Chris :
> Andy Shellam (Mailing Lists) wrote:
>>
>> Hi,
>>
>>>
>>> If the databases are in the same mysql server, then you could qualify
>>> the table select with the database name and simply re-use the
>>> connection
>>>
>>> select db_name.table_name.field from db_name.table_name [where]
>>
>> No offence, but if I saw this in an application's source code, I'd run a
>> mile.
>
> Plus the assumption that they are on the same server and that the user
> you're connecting with has access to both databases..
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

If it comes to that, if you are writing SQL code in PHP rather than
abstracting it or using stored procedures/views/etc. ...

Essentially the issue is 2 identical connections are not actually 2
identical connections.

They are 2 references to a single connection.

So, changing the DB via 1 resource changes the db for both resources.

NOTE: IDENTICAL. If the server or username is different, then that
results in a separate connection.

$conn1 = mysql_connect($server, $username, $password);
$conn2 = mysql_connect($server, $username, $password); // Is the same
connection as $conn1
mysql_select_db($db1, $conn1); // Both connection resources are now
looking at db1.
mysql_select_db($db2, $conn2); // Both connection resources are now
looing at db2.

_ONE_ solution is to use fully qualified names (you can use constants
if you don't like hard-coding the db name in the query).

Another option is to set the new_link flag on the mysql_connect() call.

That way you will have 2 separate connections.

$conn1 = mysql_connect($server, $username, $password, true);
$conn2 = mysql_connect($server, $username, $password, true); // Is NOT
the same connection as $conn1
mysql_select_db($db1, $conn1); // Points to db1 for connection1
mysql_select_db($db2, $conn2); // Points to db2 for connection2




--
-----
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Multiple instances of mysql_connect() in single PHP

2009/11/11 Chris :
> Plus the assumption that they are on the same server and that the user
> you're connecting with has access to both databases..

See the initial post.

$db_material = mysql_connect("localhost", "root", "secret");
$db_labor = mysql_connect("localhost", "root", "secret");

2 connections using the same server and credentials - results in 1
real connection.

$db_material = mysql_connect("localhost", "root", "secret", true);
$db_labor = mysql_connect("localhost", "root", "secret", true);

should fix it.
--
-----
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Multiple instances of mysql_connect() in single PHP document.

Richard Quadling wrote:
> 2009/11/11 Chris :
>> Plus the assumption that they are on the same server and that the user
>> you're connecting with has access to both databases..
>
> See the initial post.
>
> $db_material = mysql_connect("localhost", "root", "secret");
> $db_labor = mysql_connect("localhost", "root", "secret");
>
> 2 connections using the same server and credentials - results in 1
> real connection.
>
> $db_material = mysql_connect("localhost", "root", "secret", true);
> $db_labor = mysql_connect("localhost", "root", "secret", true);
>
> should fix it.

Good pickup - the rest of us missed it :)


--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php