Multiple instances of mysql_connect() in single PHP document.

[Timothy Legg-2]

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( "<P>Unable to connect to the database server at this
time.</P>" );
                exit();
        }
        mysql_select_db("Material_Log", $db_material);


        //Connect to server (labor)
        $db_labor = mysql_connect("localhost", "root", "secret");
        if (!$db_labor)
        {
                echo( "<P>Unable to connect to the database server at this
time.</P>" );
                exit();
        }
        mysql_select_db("Labor_Log", $db_labor);



Tim Legg


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

[chris smith-9]

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

[Nehemias Duarte]

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"?

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

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
Confidentiality Notice:
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

[chris smith-9]

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

[Phpster]

On Tue, Nov 10, 2009 at 4:14 PM, Timothy Legg <php_list@...> 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.
>
>
>        //Connect to server (materials)
>        $db_material = mysql_connect("localhost", "root", "secret");
>        if (!$db_material)
>        {
>                echo( "<P>Unable to connect to the database server at this
> time.</P>" );
>                exit();
>        }
>        mysql_select_db("Material_Log", $db_material);
>
>
>        //Connect to server (labor)
>        $db_labor = mysql_connect("localhost", "root", "secret");
>        if (!$db_labor)
>        {
>                echo( "<P>Unable to connect to the database server at this
> time.</P>" );
>                exit();
>        }
>        mysql_select_db("Labor_Log", $db_labor);
>
>
>
> Tim Legg
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

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]


--

Bastien

Cat, the other other white meat

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

[Richard Quadling]

2009/11/11 Bastien Koert <phpster@...>:

> On Tue, Nov 10, 2009 at 4:14 PM, Timothy Legg <php_list@...> 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.
>>
>>
>>        //Connect to server (materials)
>>        $db_material = mysql_connect("localhost", "root", "secret");
>>        if (!$db_material)
>>        {
>>                echo( "<P>Unable to connect to the database server at this
>> time.</P>" );
>>                exit();
>>        }
>>        mysql_select_db("Material_Log", $db_material);
>>
>>
>>        //Connect to server (labor)
>>        $db_labor = mysql_connect("localhost", "root", "secret");
>>        if (!$db_labor)
>>        {
>>                echo( "<P>Unable to connect to the database server at this
>> time.</P>" );
>>                exit();
>>        }
>>        mysql_select_db("Labor_Log", $db_labor);
>>
>>
>>
>> Tim Legg
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>
> 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]
>
>
> --
>
> Bastien
>
> Cat, the other other white meat
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

As I understand things, if you make 2 or more connections to the same
database server with the same credentials, you end up with 1 shared
connection.

This is assuming you are not using mysql_pconnect() and you are not
using the new_link option on mysql_connect().

See http://svn.php.net/viewvc/php/php-src/trunk/ext/mysql/php_mysql.c?view=markup#l864.

If you then use change DB on one of the connection, you are changing
it for both.

The answer is to use a single connection and a fully qualified name to
the table.

SELECT Alias.Column FROM Database.Table.Column AS Alias

sort of thing.



--
-----
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

[Andy Shellam (Mailing Lists)-2]

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

[chris smith-9]

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

[Richard Quadling]

2009/11/11 Chris <dmagick@...>:

> 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.

<?php
$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.

<?php
$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

[Richard Quadling]

2009/11/11 Chris <dmagick@...>:
> 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

[chris smith-9]

Richard Quadling wrote:

> 2009/11/11 Chris <dmagick@...>:
>> 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