Php + mysql: 2 database connections open

[rennis]

I currently am able to open one connection to a mysql database. However, I have been unsuccessful in my attempts to get 2 connections open at the same time. Reason for this is that i need to connect to a second database on a second host, select data from a table in database 1 and insert it into database 2.

Any suggestions?

 

[vacunita]

Do it one by one, open a connection to DB one save the data in an array or something, close the db connection, and open connection to DB 2 and insert data from array, to DB 2.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[sleipnir214]

I strongly recommend against opening and closing the connections.


How, exactly, are you invoking mysql_connect()?

If you want to open two MySQL connections at a time, you must explicitly assign the connection handle to a variable, then use that connection handle explicitly in all function calls. I've done this numerous times.

<?php
[red]$dbh1[/red] = mysql_connect ('localhost', 'a', 'apass');
[blue]$dbh2[/blue] = mysql_connect ('localhost', 'b', 'bpass');

mysql_select_db('foo', [red]$dbh1[/red]);
mysql_select_db('bar', [blue]$dbh2[/blue]);

$result1 = mysql_query ("SELECT * FROM the_table WHERE some_column = 'some_value'", [red]$dbh1[/red]);
while ($data = mysql_fetch_assoc($result1))
{
	$result2 = mysql_query ("INSERT INTO some_table VALUES (....)", [blue]$dbh2[/blue]);
}

Want the best answers? Ask the best questions! TANSTAAFL!

 

[rennis]

Thanks guys for your help. Its greatly appreciated.

sleipnir214 is it possible to do this:
$result1 = mysql_query ("Insert into the_table (select * from thetable1,$dbh1)", $dbh2);

Basically, this will insert all data from thetable1 table in the first database, into the second database in a field called the_table.

Ennis

 

[rennis]

Of course I would need the record number and add one to it, in a for loop to insert all data from the first database into the second

 

[feherke]

Hi

$result1 = mysql_query ("Insert into the_table (select * from thetable1,$dbh1)", $dbh2);

Basically, this will insert all data from thetable1 table in the first database, into the second database in a field called the_table.

But practically will do nothing, just returning an error code. $dbh1 is a PHP variable holding database connection data meaningful only for the PHP interpreter which established the connection. Is useless to send it to MySQL or anything else.

Feherke.

http://rootshell.be/~feherke/

 

[sleipnir214]

rennis:
No, that will not work. You get raw data from one handle which must be processed into an SQL query before being inserted into the other.

You could, I suppose, create a function which fetches the data from the input handle and writes the necessary SQL. Then you could do something like:

$result2 = mysql_query( MyFetchAndProcessSQLFunction($dbh1), $dbh2);

but I don't know what you'd gain from something like that.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[rennis]

Thanks again everyone for your help. Im currently at the point now where i am able to generate the insert statement by using the following:

$result = mysql_query ("SELECT * FROM table1", $dbh1);

$num=mysql_numrows($result);

mysql_close($dbh1);

while($row = mysql_fetch_assoc($result)){
$array[] = $row; }

printArray($array);

function PrintArray($arr)
{
$count = count($arr);
if($count > 0){
reset($arr);
$num = count(current($arr));

//This returns the column names from the table
foreach(current($arr) as $key => $value){
$theKey = $key;
$test .= $theKey . ",";

}

//Trim the last ","
$test = substr($test,0,-1);
//Add the Bracket
$test = $test. ')';
while ($curr_row = current($arr)) {
$col = 1;
//This section appears to return the actual value in the field
while (false !== ($curr_field = current($curr_row))) {

$test2 .= $curr_field. ",";
next($curr_row);
$col++;
}
//Trim the last ","
$test2 = substr($test2,0,-1);
//Add the bracket
$test2 = $test2. ')';
$result3 = mysql_query ("Insert into Patientlist ($test values ($test2", $dbh2);

Problem: When printing/echoing the code in a browser, it prints out correctly, however, result3 doesn't get executed, placing the information into the database. I believe my issue is that values in the table that are varchar need to be enclosed with single quotes (' ')...Is there a way to determine the datatype and if its equal to varchar then wrap it in the single quotes? Any other suggestions

 

[feherke]

Hi

Is there a way to determine the datatype and if its equal to varchar [gray](...)[/gray]

Sort of. Use [tt]mysql_field_type()[/tt].

The returned field type will be one of "int", "real", "string", "blob", and others as detailed in the » MySQL documentation.

Feherke.

http://rootshell.be/~feherke/

 

[sleipnir214]

My primary suggestion is that you regularize your indentation style. It's very hard to tell what code is supposed to be in what loop.


If you aren't sure why your MySQL query isn't working, ask MySQL. At the bare minimum, something like:

[tt]$result3 = mysql_query ("Insert into Patientlist ($test values ($test2", $dbh2) or die(mysql_error());

is give you debugging information.


You have at least two ways to determine whether the datatype of a column requires it be in singlequotes. One is to use the PHP function is_numeric() against the value and if it is not numeric, put quotes around it. Another is to do a MySQL query like [tt]DESCRIBE tablename[/tt] against your source table then parse the return.




Want the best answers? Ask the best questions! TANSTAAFL!

 

[feherke]

Hi

if it is not numeric, put quotes around it

In MySQL just like in PostgreSQL and Oracle you can quote numeric values. The one you can not quote is the boolean.

[blue]mysql>[/blue] [b]describe[/b] quoteornot;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| i     | int(11)     | YES  |     | NULL    |       |
| f     | float       | YES  |     | NULL    |       |
| v     | varchar(10) | YES  |     | NULL    |       |
| c     | char(10)    | YES  |     | NULL    |       |
| b     | tinyint(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
[gray]5 rows in set (0.01 sec)[/gray]

[blue]mysql>[/blue] [b]insert into[/b] quoteornot [b]values[/b] ([i]'2007'[/i],[i]'3.1415'[/i],[i]'PHP'[/i],[i]'MySQL'[/i],[i]'true'[/i]);
[gray]Query OK, 1 row affected, 2 warnings (0.02 sec)[/gray]

[blue]mysql>[/blue] [b]select[/b] * [b]from[/b] quoteornot;
+------+--------+------+-------+------+
| i    | f      | v    | c     | b    |
+------+--------+------+-------+------+
| 2007 | 3.1415 | PHP  | MySQL |    0 |
+------+--------+------+-------+------+
[gray]3 rows in set (0.00 sec)[/gray]

Feherke.

http://rootshell.be/~feherke/