Queries from two databases don"t work

Hello,

I need query tables that are in two different databases so I use
mysql_query() with link_identifier, as I've read in the manual, in order
to select in wich DB must be made the query.
The problem I have is that the link identifier has no effect.

This example doesn't work
$conn_1=mysql_pconnect("localhost","usermysql","password");
mysql_select_db("DB1", $conn_1);

$conn_2=mysql_pconnect("localhost","usermysql","password");
mysql_select_db("DB2", $conn_2); // <------ (1)

$result_t1 = mysql_query("SELECT * FROM `Table1inDB1`",$conn_1);
$row_t1 = mysql_fetch_assoc($result_t1); // <------ (2)

$result_t2 = mysql_query("SELECT * FROM `Table2inDB2`",$conn_2);
$row_t2 = mysql_fetch_assoc($result_t2);
?>

It is as if (1) activates DB2 for all the script an in (2) the query is
not made in DB1 but in DB2, even using $conn_1.

I have looking in php.ini but I haven't found anything useful.

My environment is php 4.4.2, mysql 4.0.18, IIS, W2000.

Thanks in advance.

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

Re: Queries from two databases don"t work

Joaquín wrote:
> Hello,
>
> I need query tables that are in two different databases so I use
> mysql_query() with link_identifier, as I've read in the manual, in order
> to select in wich DB must be made the query.
> The problem I have is that the link identifier has no effect.
>
> This example doesn't work
> > $conn_1=mysql_pconnect("localhost","usermysql","password");
> mysql_select_db("DB1", $conn_1);
>
> $conn_2=mysql_pconnect("localhost","usermysql","password");
> mysql_select_db("DB2", $conn_2); // <------ (1)

RTFM:

http://php.net/mysql_pconnect

To Quote:
"First, when connecting, the function would first try to find a
(persistent) link that's already open with the same host, username and
password. If one is found, an identifier for it will be returned instead
of opening a new connection."

You cannot open two database connections using _pconnect with exactly
the same information.

You need to use _connect and make sure you specify the $new_link parameter.


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

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

[Fixed] Re: [PHP-DB] Queries from two databases don"t work

> You cannot open two database connections using _pconnect with exactly
> the same information.
>
> You need to use _connect and make sure you specify the $new_link parameter.

In fact I used mysql_connect() at first and I didn't work either, so I
tried a lot of different tests and one of them was using mysql_pconnect().

Anyway, yes it was the $new_link parameter. That "optional" parameters
drive me mad.

Thank you very much.

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

RE: Best Practices? [PHP-DB] Queries from two databases

>From: Joaquín
>I need query tables that are in two different databases
>mysql_select_db("DB1", $conn_1);
>
>mysql_select_db("DB2", $conn_2);
>
>$result_t1 = mysql_query("SELECT * FROM `Table1inDB1`",$conn_1);
>$row_t1 = mysql_fetch_assoc($result_t1);
>
>$result_t2 = mysql_query("SELECT * FROM `Table2inDB2`",$conn_2);
>$row_t2 = mysql_fetch_assoc($result_t2);

Although Joaquín said his issue is [Fixed], I write these queries as:
SELECT * FROM DB1.TableInDB1
SELECT * FROM DB2.TableInDB2
(with and without link_identifiers I believe), so I was wondering about best
practices.

I can see if you already have code written without the DB in the FROM
clause, it could be easier to have multiple links.

I haven't really used multiple links, but I ran into issues with multiple
databases and consistently setting the correct DB, so now if I know I'll be
using multiple DB's, I just write the query with the DB in the FROM clause.
(A prior fix was to select the DB before each query. But if it was already
selected, it seemed like a waste {according to my "by inspection" profiler}.
So I started using the DB in the FROM clause.)

Are there any recommendations regarding best practices between:
1) using multiple links and table.field (or table) notation
versus
2) database.table.field (or database.table) notation?

____________________________________________________________ _____
Local listings, incredible imagery, and driving directions - all in one
place! http://maps.live.com/?wip=69&FORM=MGAC01

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

Re: RE: Best Practices? [PHP-DB] Queries from two databases

Instruct ICC wrote:
>> From: Joaquín
>> I need query tables that are in two different databases
>> mysql_select_db("DB1", $conn_1);
>>
>> mysql_select_db("DB2", $conn_2);
>>
>> $result_t1 = mysql_query("SELECT * FROM `Table1inDB1`",$conn_1);
>> $row_t1 = mysql_fetch_assoc($result_t1);
>>
>> $result_t2 = mysql_query("SELECT * FROM `Table2inDB2`",$conn_2);
>> $row_t2 = mysql_fetch_assoc($result_t2);
>
> Although Joaquín said his issue is [Fixed], I write these queries as:
> SELECT * FROM DB1.TableInDB1
> SELECT * FROM DB2.TableInDB2
> (with and without link_identifiers I believe), so I was wondering about
> best practices.

If you only ever deal with mysql then you'll be fine. Other databases
may or may not support this feature.

The other problem could be user 'A' has access to DB1 but no access to DB2.

So you have to connect as user 'B' to be able to read from DB2.

So - as usual - "it depends" is the answer.

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

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