Is there a way I can make php insert or control 2 database at the same time? I want all action(add, edit, delete e.t.c) to control 2 different DB on different servers at the same time. Is this possible?
@phpshift2Jan 02.2007 — #With PHP? Not at the same time, but it's possible to do so on the same page if you mysql_close() the first connection and mysql_connect() to the second. ?
@chazzyJan 02.2007 — #mysql has built in replication support. you can enable this during configuration. basically, you have a master system, where all of your queries happen and then slave systems that can either receive an instruction from a master or get their own instructions. either way, all of the data gets copied through out to all of the systems.
@itbeingsauthorJan 02.2007 — #How can I be in control of the msater system via a configuration if I don't have access to the server? Or does .htaccess can also overright MySQL .ini config? lol
Please let me know.
I really do need this, I want to control a large input daily and I won't like to do that manually. I was actually thinkk of using stored procedure, probably it might work.
@NightShift58Jan 02.2007 — #With PHP? Not at the same time, but it's possible to do so on the same page if you mysql_close() the first connection and mysql_connect() to the second. ?[/QUOTE]You could actually open two separate connections to two different servers, without having to close the first connection before opening the next one. In fact, this could be one of the criteria applied by your script, i.e. "only attempt an insert/update if I am able to connect to BOTH SQL servers".
An issue that must be cleared up is whether you'll be able to remote-connect to your SQL servers. If yes, then you can use PHP to perform various inserts/updates as described above.
If your SQL servers don't allow remote access (meaning they only allow access from "localhost"), then you'll have to perform a local insert/update the "normal" way on the server on which the script is running and use a "tunnel" solution to insert/update on the second server. This would entail writing a script on the second server whose only mission in life would be to receive SQL instructions from the script on the first server. It sound more complicated than it really is (see this thread for more details: http://www.webdeveloper.com/forum/showthread.php?t=132508)
How can I be in control of the msater system via a configuration if I don't have access to the server? Or does .htaccess can also overright MySQL .ini config? lol
Please let me know.[/quote]If you don't have access to the server, you can still try talking to the server admin. They could help you if company policy allows. As you seem to suggest, .htaccess won't get you there - not even close...
Aside from the two issues above, you'll also have to ask yourself if inserts/update will only be initiated from one server or from more than one server. If more than one, your SQL servers will be more like peers than master/slaves. This will determine how you will have to propagate/replicate the changes from one peer to the other(s).
@NightShift58Jan 02.2007 — #If you've been granted he necessary privileges, you'll be able to. However, transactions will have to be set independently for each server, which may or may not be be what you're looking for.
"Declaring" two connections won't be a problem.[code=php]<?php $localLINK = mysql_connect('localhost', 'mysql_user', 'mysql_password'); if (!$localLINK) { die('Could not connect: ' . mysql_error()); } echo 'Connected successfully to local host';
$remoteLINK = mysql_connect('remotehost', 'mysql_user', 'mysql_password'); if (!$remoteLINK) { die('Could not connect: ' . mysql_error()); } echo 'Connected successfully to remote host';
@itbeingsauthorJan 02.2007 — #Oh am sorry for using the word "declare" I actaully mean implementing the connection with mysql_select_db(). Basically, the 2 connection will be using different db with the same name say "MyDatas". If they both have the same name as in MyDatas will all queries work on both?
@NightShift58Jan 02.2007 — #What I meant was this (extending code previously posted):[code=php]<?php $localLINK = mysql_connect('localhost', 'mysql_user', 'mysql_password'); if (!$localLINK) { die('Could not connect: ' . mysql_error()); } echo 'Connected successfully to local host';
$localDB = mysql_select_db ('MyDatas', $localLINK); if (!$localDB) { die('Could not connect to local database: ' . mysql_error()); } echo 'Successfully selected local database';
$remoteLINK = mysql_connect('remotehost', 'mysql_user', 'mysql_password'); if (!$remoteLINK) { die('Could not connect: ' . mysql_error()); } echo 'Connected successfully to remote host';
$remoteDB = mysql_select_db ('MyDatas', $remoteLINK); if (!$remoteDB) { die('Could not connect to remote database: ' . mysql_error()); } echo 'Successfully selected remote database';
mysql_close($localLINK); mysql_close($remoteLINK); ?>[/code]What this means is that the name of the database is only relevant within the SQL server connection. They can have the same name or different names.