Connecting to 2 Mysql database in the same script??

Ciarán Mc Cann · #1 (**permalink**) 23-07-2007, 02:20 PM

Basicly what I am trying to do here is tranfer infromation from one Mysql database 2 another via a PHP script. The only thing about it is, that both Mysql database are on different servers, Here is the script I have got so far, but atm the secound connection isnt kicking in and its pulling info from the frist connect, I have tryed mysql_close() but still nothing. All the database selections and pass word and user name are correct also.Any idea?

PHP Code:

  

<?php
// Make a MySQL Connection
$con = mysql_connect("localhost", "user", "pass") or die(mysql_error());
$con = mysql_select_db("database") or die(mysql_error());

$result = mysql_query("SELECT * FROM oakstown WHERE emailed = 'no'")
or die(mysql_error());

$total_to_be_emailed = mysql_num_rows($result);

While($row = mysql_fetch_array( $result ))
{
  array    ($doc[] = "$row[id]");


array ($date2[]                     = "$row[date]");                         
array ($invoice_name2[]             = "$row[invoice_name]" );
array ($invoice_address2[]            = "$row[invoice_address]" );
array ($telephone12[]                 = "$row[telephone1]" );
array ($telephone22[]                 = "$row[telephone2]" );
array ($fax2[]                         = "$row[fax]" );
array ($delivery_name2[]            = "$row[delivery_name]" );
array ($delivery_address2[]         = "$row[delivery_address]" );
array ($telephone32[]               = "$row[telephone3]" );
array ($digger_driver2[]            = "$row[digger_drivers]" );
array ($price2[]                    = "$row[price]" );
array ($payment2[]                  = "$row[payment]" );
array ($system2[]                   = "$row[system]" );
array ($distribution_box22[]        = "$row[distibution_box]" );
array ($cable2[]                    = "$row[cable]" );
array ($commission2[]               = "$row[comission]" );
array ($size2[]                     = "$row[size]" );
array ($type_of_lids2[]             = "$row[type_of_lid]" );
array ($wall_size2[]                = "$row[wall_sizes]" );
array ($wall_quantity2[]            = "$row[wall_quantity]" );
array ($facility_to_off_load2[]     = "$row[facility_to_off_load]" );
array ($facility_for_artic2[]       = "$row[facility_for_delivery]" );
array ($date_of_delivery2[]         = "$row[date_of_delivery]" );
array ($am_or_pm2[]                 = "$row[am_or_pm]" );
array ($directions2[]               = "$row[direction]" );
        
    
        };
        
    
        
    
mysql_connect("frih.net/", "user", "pass") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());


echo"seound connection<br>";

$i = "0";

While($i < $total_to_be_emailed)
{

$i = $i+1;    
    
mysql_query("INSERT INTO oakstown 
( date, invoice_name, invoice_address, telephone1, telephone2, fax, delivery_name, delivery_address, telephone3, digger_driver, price, payment, system, distribution_box, cable, commission, size, distribution_box2, lid_types, l_wall_size, l_wall_quantity, facility_to_off_load, facility_for_artic, date_of_delivery, time_am_or_pm, directions, emailed, sold ) VALUES
( '$date2[$i]', '$invoice_name2[$i] ', '$invoice_address2[$i] ', '$telephone12[$i]', '$telephone22[$i]', '$fax2[$i]', '$delivery_name2[$i]', '$delivery_address2[$i]', '$telephone32[$i]' , '$digger_driver2[$i]', '$price2[$i]', '$payment2[$i]', '$system2[$i]', '$distribution_box2[$i]', '$cable2[$i]', '$commission2[$i]', '$size2[$i]', '$distribution_box22[$i]', '$type_of_lids2[$i]', '$wall_size2[$i]', '$wall_quantity2[$i]', '$facility_to_off_load2[$i]', '$facility_for_artic2[$i]', '$date_of_delivery2[$i]', '$am_or_pm2[$i]', '$directions2[$i]', '$no[$i]', '$no[$i]')")
or die(mysql_error());

};

?>

daviddoran · #2 (**permalink**) 23-07-2007, 02:32 PM

Basically, you need to create a second mysql connection and explicitly tell each mysql function which one to use. (I always supply the connection object anyway because I'm not sure which the functions will use if you don't specify one)

E.g:

Code:

$con1 = mysql_connect( 'host','user','pass' );
$con2 = mysql_connect( 'host','user','pass' );

//Use first connection
mysql_select_db( "db1", $con1 );
//Use second connection
mysql_select_db( "db2", $con2 );

//Query first connection (db1)
mysql_query( "SELECT etc....", $con1 );

//Query second connection (db2)
mysql_query( "SELECT etc....", $con2 );

As you can see all you do is create two mysql connection objects and they are completely separate.

PS: Your array code really doesn't make any sense in the middle there. Array() creates, and returns an array. You don't wrap array assignments in Array().

Ciarán Mc Cann · #3 (**permalink**) 23-07-2007, 02:44 PM

Ok well I did what you suggected but it didnt work. I got this error and here is the code, I also change a small thing in one of the connections.

Quote:

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in C:\wamp\www\flatline database\doc_email.php on line 7
No database selected

PHP Code:

  <?php
// Make a MySQL Connection
$con = mysql_connect("localhost", "user", "pass") or die(mysql_error());
$con2 =  mysql_connect("frih.net/", "user", "pass") or die(mysql_error());

mysql_select_db( "flatlinedatabase", $con );
mysql_select_db( "flatline_database", $con1 );


$result = mysql_query("SELECT * FROM oakstown WHERE emailed = 'no'", $con)
or die(mysql_error());

$total_to_be_emailed = mysql_num_rows($result);

While($row = mysql_fetch_array( $result ))
{
  array    ($doc[] = "$row[id]");


array ($date2[]                     = "$row[date]");                         
array ($invoice_name2[]             = "$row[invoice_name]" );
array ($invoice_address2[]            = "$row[invoice_address]" );
array ($telephone12[]                 = "$row[telephone1]" );
array ($telephone22[]                 = "$row[telephone2]" );
array ($fax2[]                         = "$row[fax]" );
array ($delivery_name2[]            = "$row[delivery_name]" );
array ($delivery_address2[]         = "$row[delivery_address]" );
array ($telephone32[]               = "$row[telephone3]" );
array ($digger_driver2[]            = "$row[digger_drivers]" );
array ($price2[]                    = "$row[price]" );
array ($payment2[]                  = "$row[payment]" );
array ($system2[]                   = "$row[system]" );
array ($distribution_box22[]        = "$row[distibution_box]" );
array ($cable2[]                    = "$row[cable]" );
array ($commission2[]               = "$row[comission]" );
array ($size2[]                     = "$row[size]" );
array ($type_of_lids2[]             = "$row[type_of_lid]" );
array ($wall_size2[]                = "$row[wall_sizes]" );
array ($wall_quantity2[]            = "$row[wall_quantity]" );
array ($facility_to_off_load2[]     = "$row[facility_to_off_load]" );
array ($facility_for_artic2[]       = "$row[facility_for_delivery]" );
array ($date_of_delivery2[]         = "$row[date_of_delivery]" );
array ($am_or_pm2[]                 = "$row[am_or_pm]" );
array ($directions2[]               = "$row[direction]" );
        
    
        };
        
        


$i = "0";

While($i < $total_to_be_emailed)
{

$i = $i+1;    
    
mysql_query("INSERT INTO oakstown 
( date, invoice_name, invoice_address, telephone1, telephone2, fax, delivery_name, delivery_address, telephone3, digger_driver, price, payment, system, distribution_box, cable, commission, size, distribution_box2, lid_types, l_wall_size, l_wall_quantity, facility_to_off_load, facility_for_artic, date_of_delivery, time_am_or_pm, directions, emailed, sold ) VALUES
( '$date2[$i]', '$invoice_name2[$i] ', '$invoice_address2[$i] ', '$telephone12[$i]', '$telephone22[$i]', '$fax2[$i]', '$delivery_name2[$i]', '$delivery_address2[$i]', '$telephone32[$i]' , '$digger_driver2[$i]', '$price2[$i]', '$payment2[$i]', '$system2[$i]', '$distribution_box2[$i]', '$cable2[$i]', '$commission2[$i]', '$size2[$i]', '$distribution_box22[$i]', '$type_of_lids2[$i]', '$wall_size2[$i]', '$wall_quantity2[$i]', '$facility_to_off_load2[$i]', '$facility_for_artic2[$i]', '$date_of_delivery2[$i]', '$am_or_pm2[$i]', '$directions2[$i]', '$no[$i]', '$no[$i]'), $con2")
or die(mysql_error());

daviddoran · #4 (**permalink**) 23-07-2007, 02:48 PM

You've probably already seen it but you created the connections as "con" and "con2" whereas you are using "con" and "con1" in mysql_select_db.

Ciarán Mc Cann · #5 (**permalink**) 23-07-2007, 02:51 PM

oh **** yea ha thanks, yea that seem to have work alright, although I dont think I am out of the woods yet. Thanks for your help

Ciarán Mc Cann · #6 (**permalink**) 23-07-2007, 03:34 PM

Now I got another problem I am now getting this erorr

Quote:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user: 'user@ip' (Using password: YES) in C:\wamp\www\flatline database\doc_email.php on line 4

daviddoran · #7 (**permalink**) 23-07-2007, 03:54 PM

Have you been able to connect to frih.net remotely before?
Most MySQL servers do not allow non-localhost access.

Ciarán Mc Cann · #8 (**permalink**) 23-07-2007, 03:57 PM

No I havent, but I have been talking to admins of the system and they gave me a ip and told me to put % and the ip number and it should work. Something about a wildcard or somthing I anit sure what its all about. You probly cant help me anymore, thanks for the help anyway.

Kind Regards
Ciaran Mc Cann

Frodo · #9 (**permalink**) 23-07-2007, 05:30 PM

Do you not have access to phpMyAdmin or any other MySql access to a dump/export of the table and then import it on the other side.

Would really be the quickest way.

ziycon · #10 (**permalink**) 24-07-2007, 01:57 AM

Quote:

Originally Posted by Frodo

Do you not have access to phpMyAdmin or any other MySql access to a dump/export of the table and then import it on the other side.

Would really be the quickest way.

Agree, just export the DB and import in into the new hosting!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode